Calculating Working Hours /w Date+time stamps and a variable

[Jaevwyn]

Hi Guys,

I am trying to write a formulae that will calculate the working hours between two date + time stamps formatted "dd/mm/yyyy hh:mm:ss", but I also need it to factor in the schedule type of the entree, of which I have three located further along the row:
- 24/7 (where all hours are counted)
- 0700-1900 Business Days (as the label states, monday - friday 0700-1900)
- 0900-1700 Business Days (as the label states, monday - friday 0900-1700)

I have tried a few things out based on other forum posts I found and havent managed to get things calulating correctly, could anyone lend me their brain?
(I promise to give it back)

 

[Gerald Higgins]

Hi, welcome to the board.

I think I know what you're trying to do, but just to be completely clear, can you post maybe 2 or 3 different examples of the date and time stamp pairs, and describe what exactly the results should be in each case ?

 

[Jaevwyn]

Thanks for the welcome!

Examples
Start Times Column O
End Time Column P
Result Column S (dd:hh:mm:ss)
Schedule Coloumn Z

O43 = 17/10/2017 16:50:28 | P43 = 18/10/2017 12:48:33 | S43 = 00:07:52:05 | Z43 = 0700-1900 Business Days
O222 = 18/10/2017 11:58:35 | P222 = 20/10/2017 03:57:43 | S222 = 01:15:59:57 | Z222 = 24X7
O926 = 18/10/2017 13:35:15 | P926 = 19/10/2017 13:34:33 | S926 = 00:23:59:58 | Z926 = 0900-1700 Business Days

Hope that is enough information for you!
I will be heading home for the day shortly, but I will check back in tomorrow... many thanks!

 

[Gerald Higgins]

I can't match your answers.

I think they should be as follows
S43 00:07:58:05
S222 01:15:59:08
S926 00:07:59:18

Am I missing something ?

 

[Jaevwyn]

No you are correct, I had extracted the data from my copy of the sheet but before sending it out someone had messed with the formulae that makes those timestamps, its been corrected and they match what you have there!

 

[Jaevwyn]

Just in case it helps anyone in the future, I solved it with this:
=IF(Z926="24X7",(NETWORKDAYS(O926,P926)-1)+(MOD(P926,1)-MOD(O926,1))+(IF(R926<="",R926,0)),IF(Z926="0700-1900 BUSINESS DAYS",((NETWORKDAYS(O926,P926)-1)/2)+(MOD(P926,1)-MOD(O926,1))+(IF(R926<="",R926,0)),IF(Z926="0900-1700 BUSINESS DAYS",((NETWORKDAYS(O926,P926)-1)/3)+(MOD(P926,1)-MOD(O926,1))+(IF(R926<="",R926,0)),0)))

Column O Start Date / Time
Column P End Date / Time
Column Z Schedule Type

Its not perfect by any means, but it works for what I need it to