Time Difference Calculation Between Two Dates ...

[uday]

Hi experts,

I have figured out one formula to calcute the time (hh:mm) difference between two dates. However it is not giving me the correct answer if the time span is beyond 2 days, it is only working within date or same date,.


Formula correction required:

=IF(B2 <= A2, "Out of Scope", (MIN(TIME(20,0,0), MOD(B2,1)) - MAX(TIME(8,0,0), MOD(A2,1))) + (INT(B2) - INT(A2) - 1) * (TIME(20,0,0) - TIME(8,0,0)) + IF(INT(B2) > INT(A2), MAX(0, TIME(20,0,0) - MAX(TIME(8,0,0), MOD(A2,1))) + MAX(0, MIN(TIME(20,0,0), MOD(B2,1)) - TIME(8,0,0)), 0 ))

A2	B2	Result

09/08/2024 08:00 AM

11/08/2024 08:00 PM

36:00:00

09/08/2024 07:00 AM

11/08/2024 09:00 AM

37:00:00

09/08/2024 07:00 PM

11/08/2024 09:00 PM

37:00:00

09/08/2024 10:00 AM

11/08/2024 06:00 PM

22:00:00

09/08/2024 08:00 PM

12/08/2024 08:00 PM

48:00:00

Result should be like above mentioned table, however it is not working, beyond 1 day or 2 days span. Sometime the result is not changing.
Also,if required then please suggest what could be the correct format to keep the result.

 

[vletm]

uday​

=b2-a2
use customformat [h]:mm with result

 

[ExcelGoogler]

Formatting may help but...

So... Calculating date and time differences in Excel is both aided and hindered by the fundamental concept that when trying to look at hours, minutes & seconds only 24 hours are part of the logic. That's not to say that you can't increase the number by adding in more the way you have in your formula. However, it becomes increasingly simple if you handle each time segment independently.

I'm sure there are about 40 other ways to reach the outcome you're looking for but here is one approach that seems to fit the need:

=
IF(AND(INT(B2-A2)<=0, HOUR(B2-A2)<=0, MINUTE(B2-A2)<=0, SECOND(B2-A2)<=0), "Out of scope",
  IF((24*INT(B2-A2))  + HOUR(B2-A2)=0,"00", TEXT((24*INT(B2-A2))  + HOUR(B2-A2),"00"))
& ":"  &
  IF(MINUTE(B2-A2)=0,"00",TEXT(MINUTE(B2-A2),"00") )
& ":" &
  IF(SECOND(B2-A2)=0,"00",TEXT(SECOND(B2-A2),"00"))
)

Cheers!

---

If you'd like to say thank you.​

 

[p45cal]

Formatting may help but...

A lot it would seem:

and @vletm 's is a value you can do arithmetic with while @ExcelGoogler 's is text (although Excel's implicit data type conversion would probably come in if needed)! I've assumed UK style dates.



Edit post posting: Looking more closely at @uday 's formula I'm guessing it's trying to calculate the count of hours between 8am and 8pm for the date and times concerned? @uday , is that right and are weekends counted?

 

[ExcelGoogler]

WOW! Nice, I like this much better but I would add to the condition just-in-case the difference is within the same day.

=IF(AND(INT(B2-A2)<=0, HOUR(B2-A2)<=0, MINUTE(B2-A2)<=0, SECOND(B2-A2)<=0),"Out of scope", B2-A2)

I haven't been very successful with selecting the correct formats over time so text is my go-to.

Also to @p45cal 's observation regarding 8am and 8pm, if that is the goal; are there any concerns for holidays?

 

[vletm]

Negative times are not challenge(, if someone would like to use it).
Excel can handle time if you turn on the 1904 date system,
but that will cause all dates to shift by 4 years and 1 day, so you'd have to correct that.
If you want that:

• Select File > Options > Advanced.
• Scroll down to the section 'When calculating this workbook'.
• Tick the check box 'Use 1904 date system'.
• Click OK.

Only uday can know, what uday tries to figure.
Last seen 14 Aug, 2024 (26 Aug, 2024)

 

[ExcelGoogler]

Skip changing the condition! Why mess with perfection?

I just confirmed it works fine with exactly what p45cal provided.

 

[vletm]

ExcelGoogler​

Why do You want to play with those hour, minute & second?
Below: B2 & B2 are same as well as C2 & C3.
This works always, if values are valid date & time.
If C2 < B2 then it works same way with C3 < B2 ... negative.

... or there always can get absolute value and add "-" in front of result.

 

[ExcelGoogler]

vletm,
LOL Fair enough, I guess it all depends on your perspective... Microsoft Support Link

I haven't been very successful with selecting the correct formats over time so text is my go-to.

Why mess with perfection?

I'm sure there are about 40 other ways to reach the outcome you're looking for but here is one approach that seems to fit the need:

It's always been easier "for me" to prove to self-identified 'perfectionists' that needed their information to the second. I get what you're saying but from the user perspective, there are hidden arguments (format selections) that absolutely do change the information and in this situation, it changed the data for me.

I love that I learned something new that works! I may still use the separate "text" segments if I am trying to help others because it makes each part easier to see and understand.

Thank YOU so much for teaching me something new.

Cheers!

 

[pecoflyer]

@uday Does all this help? You did not react to or thanked your helpers?