Excel Elapsed Time as Plain Numbers Subtraction Formula

Question

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"?

Answer 1

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.
 3. Add 1 and 2.

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.

Answer 2

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.