Subtracting TIME UNITS (not time of day)

Question

How do I subtract TIME UNIT (not time of day)? Last year I ran a marathon in 48 minutes and 20 seconds. This year I ran a marathon in 47 minutes and 50 seconds. When I do this in Excel, I tried every imaginable formatting for time (including [h]mm:ss) and nothing works. I'm expecting a result of -00:00:30 (or negative 30 seconds).

Answer 1

Excel uses fractions of a day to store time. So, 3 hours are stored as 0.125 (3/24). Time is stored as a date/time value. Excel starts counting the dates from january 1st, 1900 12:00 am (1/2/1904 12:00 a.m. on Excel for Mac). Entering a time of 0:48:20 is actually Jan. 1st, 1900, 0:48:20 hours. Excel does not work with negative values for date/time, so subtracting to a negative value results in an error (cell with "#######").

Another challenge is to get Excel to understand that by 48:20 you mean 48 minutes and 20 seconds and not 48 hours and 20 minutes. There are two ways to do this:

• Use the hour notation. 48 minutes and 20 seconds become: 0:48:20
• Use the TIME function: =TIME(0;48;20)

A third thing to consider is the formatting of the cells. In the custom format, you can use [h]:mm:ss or [m]:ss. The brackets tell Excel that it should count the number of hours ([h]) or minutes ([m]) and not revert them to zero after 23 hours or after 59 minutes. So in your case, make sure the formatting of the cells are: [m]:ss

Long story short: there is no "out-of-the-box" way to do what you want because Excel doesn't know how to handle negative time values.

If you really want to calculate what you're asking, you could use the following (a bit more complex) formula, which subtracts A1 from A2: =IF( A2-A1 < 0; "-"; "") & TEXT( ABS(A2-A1); "[m]:ss" ). What that actually does is substract the times, convert it to a positive number (ABS), formats it as a string (TEXT) and puts a "-" in front of it when it's negative. Note that the result is a text and not a number (value).

Microsoft has two articles; one about storing date and time values (scroll down to about halfway the article) and one about adding and subtracting time.

Answer 2

Excel can handle negative times......but only if you use "1904 date system", rather than Excel's default 1900 date system

To set that for a specific workbook do the following:

File > Options > Advanced > Scroll down to the "When calculating this workbook" section (towards the bottom) and tick the box for "1904 date system".

Sometimes you may get undesirable effects. Don't switch to 1904 date system in a workbook where you already have dates - because the base date is 1/1/1904 rather than 1/0/1900 the dates will alter by 4 years and a day (1462 days).

See screenshot:

Answer 3

This works, the time function cannot accept 24 hours, but just fool it with 23 hours, then correct later....

=IF(A1<B1,TIME(23,0,0)-A1+B1+TIME(1,0,0),B1-A1)