Calculating hh:mm between two times that span midnight

**Ymir20000000** · 11-07-2010, 02:34 PM

I have searched the forum, but admit to having given up when all the similar-sounding problems/solutions I looked at did not seem to apply to my issue.

I have two times I want to calculate the difference from: A1 is the time a person went to sleep (ex. 10:43 PM); A2 is when the person woke up (ex. 6:00 AM). Since these times span from one day to the next, I am finding myself unable to create a formula that properly returns the result (A3) of 7:15.

I have formatted A1 and A2 as "Time, 1:30 PM."
I have formatted A3 as "Custom, hh:mm."

What is the correct formula I need to obtain the result I desire? Can I do this in a way that assumes the hours slept does *not* exceed 24 hours [i.e.: without having to specify dates in addition to the times]?

Thanks for your help in this.

--John

**zbor** · 11-07-2010, 02:37 PM

try =MOD(A2-A1, 1)

**Ymir20000000** · 11-07-2010, 03:19 PM

Wow. That's just so freakin' simple.

Thank you!

--John

**tony h** · 11-07-2010, 03:21 PM

Please Login or Register  to view this content.

where A5 is the earlier time

**arthurbr** · 11-07-2010, 03:36 PM

Originally Posted by tony h

Please Login or Register  to view this content.

where A5 is the earlier time

Or =B5-A5+(B5<A5)

**Data Cruncher** · 11-10-2010, 05:27 AM

or simply format the time cells as Custom, [h]:mm
(This is the 24 hour clock format but recognising that something can run for longer than 24 hours or past the midnight date change)
then add 24:00 to the later time
then subtract the earlier time

e.g.
[A1]22:43
[B1]30:00
[C1]=B1-A1
[Answer] 7:17

Easy peasy, lemon squeezy

**DonkeyOte** · 11-10-2010, 05:30 AM

@DataCruncher, thanks, welcome to the board.

FWIW, adding the additional 24 hours is illustrated in both tonyh/arthurbr's examples - neither of which necessitate source values be altered from their original values (the adjustment forms part of the calculation itself).

**Nana D** · 05-27-2011, 09:39 AM

the code of formula doesnt quite work with my example; im getting 13:33:00 for my answer which is totally incorrect. please help

11:37 01:10

thanks

**zbor** · 05-27-2011, 09:44 AM

From the rules:

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

**arthurbr** · 05-27-2011, 09:55 AM

Nana D you have already received a warning for hijacking threads less than 30 min ago. Please abide by forum rules. Thread closed

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc