# Excel Elapsed Time as Plain Numbers Subtraction Formula

For sake of entry simplicity and to prevent Excel from inserting unnecessary time of day hours and AM/PM information, I am using this custom format to enter time in minutes and seconds: `"00\:00"`

I am trying to calculate time difference with this type of data, but Excel doesn't recognize it as base 60 numbering. Is there a way I can keep this format and still calculate a result?

e.g. If A1 reads "00:30" and B1 reads "01:21", how do I get C1 to calculate and result in "00:51"?

• I think this may be an XY Problem. I know you're trying to avoid Excel from inserting unnecessary info, but what info is it inputting that can't be fixed via Formatting? Commented Jul 24, 2017 at 22:13
• Never knew that concept. Someone provided a solution below, but I'll keep this in mind in the future, thanks. Commented Jul 24, 2017 at 23:50

Here's a way to do it with a formula, although there are so many parentheses in this equation I'm about to go cross-eyed.

Since the rightmost two digits in your custom format represent the seconds, we can separate them using RIGHT(). Then subtracting that from the original number gives the "minutes" in your custom format.

I'll start out with the pieces, and then build the formula from its parts.

``````Convert B to Seconds  =  (60*(B2-RIGHT(B2,2))/100)+RIGHT(B2,2)
Convert A to Seconds  =  (60*(A2-RIGHT(A2,2))/100)+RIGHT(A2,2)
Difference in Seconds  =  ((60*(B2-RIGHT(B2,2))/100)+RIGHT(B2,2))-((60*(A2-RIGHT(A2,2))/100)+RIGHT(A2,2))

Now to convert back to the original format:
1. Divide the difference by 60, truncate it, and multiply by 100 to get the minutes part.
2. Divide the difference by 60, and take the modulus to get the seconds part.

Minutes Part  =  100*(TRUNC((((60*(B2-RIGHT(B2,2))/100)+RIGHT(B2,2))-((60*(A2-RIGHT(A2,2))/100)
Seconds Part  =  MOD((((60*(B2-RIGHT(B2,2))/100)+RIGHT(B2,2))-((60*(A2-RIGHT(A2,2))/100)+RIGHT(A2,2))),60)

And here's the entire formula:

=100*(TRUNC((((60*(B2-RIGHT(B2,2))/100)+RIGHT(B2,2))-((60*(A2-RIGHT(A2,2))/100)+RIGHT(A2,2)))/60))+MOD((((60*(B2-RIGHT(B2,2))/100)+RIGHT(B2,2))-((60*(A2-RIGHT(A2,2))/100)+RIGHT(A2,2))),60)
``````

Whew! Hope this helps.

• You're like a dang mad scientist. Thank you for not only working that all out but more importantly for the explanation. (I'm aware of the no "thanks" comments so I will delete this later, but this seriously deserves a thanks!) Commented Jul 24, 2017 at 23:52
• ...uhh yeah, what you said! (Nicely done, wow). Commented Jul 25, 2017 at 1:11

If you're using `121` and formatting that to be `01:21` (meaning 1 minute and 21 seconds), you can use a helper column to do the math:

In column B, put this and drag down:

``````=IF(LEN(A1)=4,TIME(0,LEFT(A1,2),RIGHT(A1,2)),IF(LEN(A1)=3,TIME(0,LEFT(A1,1),RIGHT(A1,2)),TIME(0,0,A1)))
``````

Then in `B3`, you can do `B2-B1`.

This should work for times from `00:01` to `59:59` I believe.

• Thanks! With the way I have things laid out, however, this would be troublesome. It would require two helper columns -- I should have specified more accurately, A1 is 00:30, B1 is 01:21, and I want C1 to read 00:51 (I am updating this in my original post). I'm using an excel template given to me, so if I can help it, I don't want to add any columns. Is it possible to do it in an equation that takes the first two characters and converts it to base 60, then the last two characters and converts that? Commented Jul 24, 2017 at 20:59
• @etudes - Would that work (converting the first two characters)? Because if you have just `30`, it'd convert that to base 60. But if you have `121` it takes `12` in base 60. What if you have `4030`, it'd take `40`? ...also, is VBA (a macro) an option? Commented Jul 24, 2017 at 21:09
• Oh, I guess you're right... A formula would be ideal because sometimes I have to go back and make adjustments to the times, but I guess a macro would be the next best thing. Would that be something I run at the end and it would perform the calculations? Commented Jul 24, 2017 at 21:19
• @etudes - You can have a custom formula, which would work like `=get_time_difference(A1,A2)`...or whatever Commented Jul 24, 2017 at 21:23
• I see, ok. So like you were doing before, I would have to have the formula convert the numbers to actual time data, then do the subtraction, yes? That's where I am stuck, I don't know Excel's formula system enough to understand how to make that. Commented Jul 24, 2017 at 21:50