MrExcel.com - Your One Stop for Excel Tips & Solutions

time calculations

Posted by Brian on December 04, 2000 7:35 PM

Is there a formula that will calculate the hours between two times when the second time is past 12:00 AM (midnight) and the first time is either AM or PM the previous day.

1) If A1= 10:00 AM and B1= 1:00 AM (the following day) Total Should = 15 Hrs

2) If A1= 4:00 PM and B1= 1:00 AM (the following day) Total Should = 9 Hrs.

I need a formula that will work for either case. I checked the excel functions but didn’t find one that worked. I can get No. 2 to work but can’t get a formula that works for both.

Thanks to anyone who can help.

Posted by Tim Francis-Wright on December 04, 2000 8:29 PM

If the total is in C1, format C1 as h:mm
and set the formula in C1 to:
=IF(B1>A1,1,0)+B1-A1

That works in your cases, and also works if
B1 and A1 are on the same side of midnight.
If anyone pulls a medical-resident-style
shift, then all bets are off.

HTH

Posted by Brian on December 04, 2000 9:31 PM

Thanks, Tim.

I checked the formula and couldn't get it to work. I looked over your formula and tried a variation that did work.

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

I used =IF(B1>A1,1,1)+B1-A1

I changed the >A1,1,0 to >A1,1,1

I don't know why it works but it does. Thanks for the help.

Brian

Posted by Tim Francis-Wright on December 05, 2000 6:50 AM

My apologies: I mistyped my original
answer:

It should be =IF(B1>A1,0,1)+B1-A1
If B1 is an earlier time of day than A1, the
formula adds 24 hours to the (negative)
amount of B1 minus A1.

HTH