Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have two fields DT END and DT START in format "DD/MM/YYYY hh:mm:ss".
I need to calculate the difference between them and show the format "D hh: mm".
The problem is that I calculate for every 8 working hours.
Anyone can help me?
tks
Hi!
Thank you all for responding me.
Below I leave my version to solve the problem, I hope it helps others.
Interval(If((DT_END - DT_START) >= Num(TimeStamp(TimeStamp#('8', 'hh'))),
If(Frac(((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh'))))) > Num(TimeStamp(TimeStamp#('8', 'hh'))),
(((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh')))) - Frac(((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh')))))) +
(
(DT_END - DT_START) -
(((((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh')))) - Frac(((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh'))))))) * Num(TimeStamp(TimeStamp#('8', 'hh'))))
),
((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh'))))
),
(DT_END - DT_START)
), 'D hh:mm')
Hi,
Try like this
=Interval( [DT END]- [DT START]), 'DD:hh:mm:ss')
If it is not what you expected can you come up with example what exactly you need.
Regards,
Jagan.
can you attach a sample
Hi Marco,
As I understood You need to increment next day after every 8 hours, if it is, just try this thing:
=Timestamp#(interval(interval([timestamp(DT END)]-[timestamp(start)],'hh')/8,'hh')&' '
&(num#(text(interval([timestamp(DT END)]-[timestamp(DT START)],'hh')),'#')-num#(text(interval(interval([timestamp(DT END)]-[timestamp(DT START)],'hh')/8,'hh')),'#')*8)&':'&
(num#(text(interval([timestamp(DT END)]-[timestamp(DT START)],'mm')),'#')-num#(text(interval(interval([timestamp(DT END)]-[timestamp(DT START)],'mm')/60,'mm')),'#')*60),'DD hh:mm')
It doesn't look gracefully, but it works.
Hi!
Thank you all for responding me.
Below I leave my version to solve the problem, I hope it helps others.
Interval(If((DT_END - DT_START) >= Num(TimeStamp(TimeStamp#('8', 'hh'))),
If(Frac(((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh'))))) > Num(TimeStamp(TimeStamp#('8', 'hh'))),
(((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh')))) - Frac(((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh')))))) +
(
(DT_END - DT_START) -
(((((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh')))) - Frac(((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh'))))))) * Num(TimeStamp(TimeStamp#('8', 'hh'))))
),
((DT_END - DT_START) / Num(TimeStamp(TimeStamp#('8', 'hh'))))
),
(DT_END - DT_START)
), 'D hh:mm')