Adding time across days

[phil_mccomb]

Hi,
I have a timekeeping spreadsheet that adds hours and minutes from start time to finish time.

This works fine if you finish before midnight, but as soon as you go past midnight it counts backwards?

Is there a way to ensure it always counts up? The formula looks like this: =IF(((E6-D6)*1440/60>5.49),(((E6-D6)*1440)/60),(((E6-D6)*1440)/60))

E6=Finish time and D6=Start time (I know the answer for both is the same, but I leave that format so I can subtract a lunch break when required)

The first line is incorrect because i want it to add hours from 09:30 on day 1 until 07:00 on day 2 (which should add up to 21.5 hrs, not -2.5 hrs)

Employee		Start time	Finish time	Total hours
McComb, Phil		9:30	7:00	-2.50
McComb, Phil		9:30	19:00	9.50

<tbody>
</tbody>

Thanks in advance.

 

[JonXL]

I'm assuming total hours is in F and Finish and Start are in E and D, respectively:

=(E6-D6+(E6 < D6))*24

<d6))*24[ code]

Be sure when you enter your time you are specific about AM/PM, even if your formatting won't show the distinction.</d6))*24[>

 

[phil_mccomb]

Correct, hours in F and Finish start in E and C.

When I select either the start time or finish time it states AM/PM (ie: 9:30:00 AM) so I can safely assume it knows whether it is am or pm but I just can't get it to keep counting forward past midnight

 

[JonXL]

Did you try my formula?

 

[phil_mccomb]

Perfect!!! (looks simple too... ) not sure why I had to *1440/60, but it was 5 years ago so I don't remember.

THANKS

 

[JonXL]

I actually took this formula from a thread I was working in a little while ago. The author of that thread was looking for a way to work out breaks. Since you mentioned subtracting out lunch breaks, I'll link to it here in case you find something there useful:

https://www.mrexcel.com/forum/excel...-1-result-option-post5063466.html#post5063466