CODEWITHSUNDEEP
excelexcel-formula
Alan Treanor
Alan Treanor

Reputation: 159

Formula to find difference in time between two values when they pass midnight?

I have a series of times and I am trying to find the difference in time between two values which is fine for a normal day however some times pass midnight e.g.

18:00 - 00:30

The time difference is 6 hours 30 minutes which I would like returned in a numerical value e.g. 6.5 hours. However whenever I pass midnight it is not calculating properly.

Is there anyway to work around this without having to input the dates on each value? I would like it to assume if I am between the hours of 00:00 & 08:00 then I mean the following morning and anything between 08:01 and 23:59 is from the previous day.

Upvotes: 0

Views: 328

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

Use this formula:

=IF(A1>B1,1,0)+B1-A1

enter image description here

To get the 6.5 just multiply by 24, and make the format general:

=(IF(A1>B1,1,0)+B1-A1)*24

enter image description here

Edit #2

We can get rid of the IF as the expression (A1>B1) will return 1/0.

enter image description here

=((A1>B1)+B1-A1)*24

Upvotes: 4

user4039065
user4039065

Reputation:

Try it as,

=ABS(SUM(A2, -(B2+(B2<TIME(8, 0, 0)))))*24

... which leaves 6.5 as 6 (integer) representing hours and .5 (decimal) representing 30 minutes.

enter image description here

Upvotes: 3

Related Questions