Calculate Hours Across Different Dates & Times

[JasonTruman]

Hey all

I can easily calculate the duration between 2 dates and a time for the SAME day, e.g. 13/09/23 08:30 until 13/09/23 17:00 = 8.5 hrs. I'm struggling how to approach it when the days are different e.g. 12/09/23 13:25 until 14/09/23 12:17 = 46 hours and 52 mins.

Meaning I can work out the amount of time in hours and minutes between these 2 dates but this result would be based on a 24 hour day. I'd like to base it on an 8.5 hour day

Using the above example, I started at 13:25 and finished at 17:00 on 12/09, worked 8:30 until 17:00 on 13/09 and worked 8:30 until 12:17 on 14/09

Any suggestions would be greatly appreciated?

 

[Proulxs]

Hi,

My solution would be to make an if first to check if the hour of one date/time is bigger than the second. If ending hour is bigger than starting time you can simply Calculate the time difference then add 8.5h per full day inbetween.

If the ending time is less than the starting time (ie you started at 10am but ended at 9am) then I would calculate the time from start hour to 17h and from 8h30 to end time. And again add 8.5h per full day.

The formula would be like this (I'm translating my formula from french to english so might need slight fixing):
(Start time in A1, End time in A2):

=if(MOD(A1;1)>MOD(A2;1);(rounddown(A2;1)-rounddown(A1;1)-1)*8,5+(17/24-MOD(A1;1)+(MOD(A2;1)-8,5/24));(rounddown(A2;1)-rounddown(A1;1)-1)*8,5+MOD(A2;1)-MOD(A1;1))

Dates are stored as full numbers and hours/minutes/seconds as a fraction of a day. So Mod with parameter 1 will remove the date and keep only the time.
Rounddown take day+time numbers and round it to a full day count only.

 

[JasonTruman]

Hi,

My solution would be to make an if first to check if the hour of one date/time is bigger than the second. If ending hour is bigger than starting time you can simply Calculate the time difference then add 8.5h per full day inbetween.

If the ending time is less than the starting time (ie you started at 10am but ended at 9am) then I would calculate the time from start hour to 17h and from 8h30 to end time. And again add 8.5h per full day.

The formula would be like this (I'm translating my formula from french to english so might need slight fixing):
(Start time in A1, End time in A2):

=if(MOD(A1;1)>MOD(A2;1);(rounddown(A2;1)-rounddown(A1;1)-1)*8,5+(17/24-MOD(A1;1)+(MOD(A2;1)-8,5/24));(rounddown(A2;1)-rounddown(A1;1)-1)*8,5+MOD(A2;1)-MOD(A1;1))

Dates are stored as full numbers and hours/minutes/seconds as a fraction of a day. So Mod with parameter 1 will remove the date and keep only the time.
Rounddown take day+time numbers and round it to a full day count only.

Hi there

Didn't work unfortunately, ended up with weird results and negative results - also, why is there a 17/24 in the formula?

 

[Proulxs]

Hi, the 17/24 refer to 17h (as in 17/24th of a day). I see I did 8.5h without dividing it by 24 and rounddown to 1 digit instead of 0;
Note that *8.5/24 in formula refer 8.5h per day while -8.5/24 refer to 8:30am.

Here's an updated formula

=if(MOD(A1;1)>MOD(A2;1);(rounddown(A2;0)-rounddown(A1;0)-1)*8,5/24+(17/24-MOD(A1;1)+(MOD(A2;1)-8,5/24));(rounddown(A2;0)-rounddown(A1;0)-1)*8,5/24+MOD(A2;1)-MOD(A1;1))

P.S. I suggest formating the result as [hh]:mm so the hour count can go above 24h.

 

[JasonTruman]

Thank you again for replying. Some results are still returning negatives. Some return the correct number of hours between times but not the days e.g. 05-09-2023 10:22 and 06-09-2023 15:27 returns 05:05 (just the times' hours and minutes, not the additional 8 hours and 30 mins, others return numbers lower than expected

Again, I still do not understand why 17/24ths is being used - for what purpose? What relevance has 17 hours?

Thanks in advance

 

[JasonTruman]

some sample data. Those with hashes are negative results. The top record is the hours between the times not days. The bottom example, I would expect at least 25.5 as there are 3 days difference plus the time difference

 

[Proulxs]

Hi,

17h is relevant as it is your end of day. If you started working at 3:30pm and kept going till 9am the next morning you would have a total of 2h : 1h30mins on first day (5pm minus 3:30pm). So I add the end time (17h aka 5pm) then remove the start time (3:30pm in example so 15.5). And as stated before I write them as fraction of a day so divised by 24 because that's how many hours in a day. If you were working with a result in minute I would divise by 1440 to end with a fraction that represent minutes when showing a number with correct format.

For the morning I add the end time say 9am but then I remove 8.5 since that's when you started to get the day. so 9/24 - 8.5/24 (the 9/24 was found by mod(end;1)

In your example the last line should be 31h07 because you worked 8.5h on the 2nd and 3rd; only 2 full days. 8h14 on the 1st and 5h43 on the 4th.

.... now as for negative I just tried the formula again with 2 identical times and got minus 8h30. I was removing 1 day to the day difference in both scenario and that was wrong. You remove one day only when the end time is before start time because the day isn't finished.

Here's the corrected formula;

=if(MOD(A1;1)>MOD(B1;1);(rounddown(B1;0)-rounddown(A1;0)-1)*8,5/24+(17/24-MOD(A1;1)+(MOD(B1;1)-8,5/24));(rounddown(B1;0)-rounddown(A1;0))*8,5/24+MOD(B1;1)-MOD(A1;1))

Here's my results with your examples numbers;

2023-09-05 10:22​	2023-09-06 15:27​	13:35​
2023-09-04 09:23​	2023-09-04 15:17​	05:54​
2023-09-07 08:30​	2023-09-07 09:52​	01:22​
2023-09-04 12:52​	2023-09-04 16:55​	04:03​
2023-09-06 08:42​	2023-09-06 14:18​	05:36​
2023-09-06 11:15​	2023-09-06 12:31​	01:16​
2023-09-01 08:46​	2023-09-04 14:23​	31:07​