Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
Hopefully this is a nice and simple one, but clearing it's not coming to me today!!
I have a calculation performed in the script to calculate the duration between two days including working time, which works perfect and provides me with an output in hours and minutes. I want to format this to show it in days, hours and minutes, but unfortunately, it shows the number of days (as in 24 hours) for this value rather than a working day-day (as in 8 hours).
So, an example set of data would be:
Date Start: 18/04/2016 - 14:17:42
Date End: 27/041/2016 - 12:18:40
Duration: 54:00:58
.... So far so good!!
In days, this would be 6 Days, 6 Hours 1 Minute....
... However, if I was to do Interval(54:00:58,'DD hh:mm:ss), I get 02 06:00:58 as a 'day' is 24 hours.
Can you advise on what I need to do to format this to get a working day in the result?
Many thanks for your assistance!
Kind Regards,
Dayna
Based on previous replies, where Finish is your Finish date, Start is your Start date:
LOAD * INLINE [
Start, Finish
18/04/2016 14:17:42, 27/04/2016 12:18:40
];
=Interval#(Floor(Interval#(
Interval(
(RangeMin(frac(Finish), MakeTime(18))
- RangeMax(frac(Start), MakeTime(8)))
+ (NetWorkDays(Start, Finish-1) * MakeTime(8)) // Only 8 hours per whole day
)
, 'h:mm:ss')/MakeTime(8)) & ' ' &
Interval(Frac(Interval#(
Interval(
(RangeMin(frac(Finish), MakeTime(18))
- RangeMax(frac(Start), MakeTime(8)))
+ (NetWorkDays(Start, Finish-1) * MakeTime(8)) // Only 8 hours per whole day
)
, 'h:mm:ss')/MakeTime(8)) *(8/24) , 'h:mm:ss'), 'D hh:mm:ss')
Wow, thanks for the many responses, many ways to get to the result... Thanks all, much appreciated!!