subtract 2 24 hour times as string and return the number of minutes

**jacol** · 11-21-2012, 11:16 PM

If anyone can help that would be appreciated.

How do i subtract two times that are entered in 24 hour time format but are strings and return the difference in minutes

For example

StopTime = 21:15
RestartTime = 22:00

Therefore MachineDownTime in minutes = RestartTime - StopTime

Any help would be appreciated

**protonLeah** · 11-21-2012, 11:58 PM

What exactly is in the cells? Is it the entire string "StopTime = 21:15"?
If you have:
A1: StopTime = 21:15
A2: RestartTime = 22:00
Try:
B1: =Right(A2,5)-Right(A1,5), format B1 as Time.

**6StringJazzer** · 11-22-2012, 12:08 AM

If they are entered in 24-hour time format, are you sure they are strings in Excel? Are the cells formatted as "Text"?

This formula works, even though the times are formatted as text strings. Excel will convert for you. I'm assuming that the stop time is in A1 and the restart time is in A2. This also handles the case where the interval crosses midnight:

Please Login or Register  to view this content.

**6StringJazzer** · 11-22-2012, 12:13 AM

On review of protonLeah's response, that's good point--I assumed that only the time was in the cell but if you have the whole string then you will have to use RIGHT as shown.

Also, I didn't explain this in my response but the result for time subtraction is in units of days, so you have to multiply by 24*60 (or 1440) to get minutes. If you want to display the result in 00:00 format then you don't need to multiply but if you just need the number of minutes you do.

**mikerickson** · 11-22-2012, 12:29 AM

=(A2+0)-(A1+0) will return the time difference if A1 and A2 are either strings or excel times.

Since this will be in Excel serial time, you could use either

=TEXT((A2+0)-(A1+0), "[m]")+0
or
=((A2+0)-(A1+0))*24*60

to return the number of minutes in that interval.