0

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

2
  • 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?
    – BruceWayne
    Jul 24 '17 at 22:13
  • Never knew that concept. Someone provided a solution below, but I'll keep this in mind in the future, thanks.
    – etudes
    Jul 24 '17 at 23:50
2

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.

enter image description here

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.

2
  • 1
    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!)
    – etudes
    Jul 24 '17 at 23:52
  • ...uhh yeah, what you said! (Nicely done, wow).
    – BruceWayne
    Jul 25 '17 at 1:11
0

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.

enter image description here

5
  • 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?
    – etudes
    Jul 24 '17 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?
    – BruceWayne
    Jul 24 '17 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?
    – etudes
    Jul 24 '17 at 21:19
  • @etudes - You can have a custom formula, which would work like =get_time_difference(A1,A2)...or whatever
    – BruceWayne
    Jul 24 '17 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.
    – etudes
    Jul 24 '17 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.