Interval with working days

[Dayna]

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

[sunny_talwar]

Try this with a single D may be

Interval(54:00:58,'D hh:mm:ss)

What is your expectation here? How do you want to see it?

[Dayna]

Hi Sunny,

That gives the same value of 02 06:00:58.

Many thanks,

Dayna

[sunny_talwar]

Ya I figured that you are looking for something else, can you elaborate on how the output should look like?

[Dayna]

Hi Sunny,

It should look like '06 06:00:58', as the 54 hours in the original duration should be 54 hours over an 8 hour working day.

Kind Regards,

Dayna

[sunny_talwar]

May be not the best ways to do it, but seems to work:

=Floor(Interval#('54:00:08', 'h:mm:ss')/MakeTime(8)) & ' ' & Interval(Frac(Interval#('54:00:08', 'h:mm:ss')/MakeTime(8)) *(8/24) , 'h:mm:ss')

[#7]

Hi Dayna,

floor((Num('54:00:58')/(1/3)))&' '&Time(((Num('54:00:58')/(1/3))-floor((Num('54:00:58')/(1/3))))*(1/3),'hh:mm:ss')

try this

-Joyson G

[sunny_talwar · Accepted solution]

Or this to give it an true timefield interpretation:

=Interval#(Floor(Interval#('54:00:08', 'h:mm:ss')/MakeTime(8)) & ' ' & Interval(Frac(Interval#('54:00:08', 'h:mm:ss')/MakeTime(8)) *(8/24) , 'h:mm:ss'), 'D hh:mm:ss')

[crusader_]

Similar idea but a bit simpler math...

=Interval#(floor(interval#('54:00:58', 'hh:mm:ss'))*3&' '&time(frac(interval#('54:00:58', 'hh:mm:ss')),'hh:mm:ss'), 'D hh:mm:ss')

/Andrei

[crusader_]

more details:

*3 means  = /(8/24) = /1/3 = *3

/Andrei