Add decimal minutes to time

In this example, the goal is to add minutes in decimal format (i.e., 1, 5, 10, etc.) to an existing Excel time. The complication is that Excel stores time as fractional values. The number 0.0104167 makes sense when you consider that 15 minutes is 1/96th of a day, and a day in Excel equals 1. But it isn't the way most people think about time. To add decimal minutes to a time, we need to first convert the minutes to an equivalent fractional value.

How Excel tracks time

In Excel, dates are serial numbers, and a single day has a numeric value of 1. Since there are 24 hours in a day, one hour = 1/24 = 0.041666667, and 1 minute = 1/(24*60) = 1/1440 = 0.000694444. This means that 15 minutes is 1/96th of a day (0.0104167), 30 minutes is 1/48th of a day (0.0208333), and 60 minutes (1 hour) is 1/24th of a day (0.0416667). The table below summarizes this relationship:

* In Excel, midnight (12:00 AM) has a dual nature: it has a value of 0 when it represents the start of a day, but it can be 1 inside a calculation that completes a full 24-hour cycle. In other words, as we approach midnight, the value of time approaches 1. But as we cross from one day to another, the 1 is added to the date, and time begins again at zero.

Adding decimal minutes to a time

Because Excel stores time as fractional values, we need to convert decimal minutes to a valid time before addition. To do this, we simply divide the minutes by 1440. For example, with an Excel time in cell A1, we can add 15, 30, 60, and 120 minutes like this:

=A1+(15/1440) // add 15 minutes
=A1+(30/1440) // add 30 minutes
=A1+(60/1440) // add 60 minutes
=A1+(120/1440) // add 120 minutes

In the example shown, the formula in cell E5 uses the same idea above like this:

=B5+(C5/1440)

As the formula is copied down, it adds the decimal minutes in column C to the times in column B. The results in column E are formatted with the custom number format "h:mm AM/PM".

With the TIME function

You can also add time values with the TIME function. To add 90 minutes to a time in A1, use:

=A1+TIME(0,90,0)

The TIME function saves you from having to remember the formula for converting decimal minutes to an Excel time. However, note that the TIME function only supports time spans up to 24 hours. Every 24 hours, the time will reset to zero like a clock. If you try to add more than 1440 minutes to a time with the TIME function, only the remainder of the minutes divided by 1440 will be added. For example, 25 hours of time is 1500 minutes. Note how the results below differ:

=TIME(0,1500,0) = 0.041667 = 1 hour
=1500/1440 = 1.041667 = 25 hours

Also, TIME does not support negative values for minutes as seen in rows 14-16 of the example shown. If you try to use TIME with a negative number of minutes, it will return a #NUM! error.

Subtracting minutes from time

The easiest way to subtract minutes from time in Excel is to provide a negative number for minutes, as seen in the range C14:C16. This requires no adjustment in the formula explained above. However, you can also use a generic formula like this:

=A1-minutes/1440

Note that you may get an error if you try to subtract minutes from a time when the result is negative because Excel doesn't support negative time values. This happens when the number of minutes being subtracted is larger than the original time value. One way to avoid this problem is to use a formula like this:

=MOD(time-(minutes/1440),1)

Here, the MOD function solves the negative problem by "flipping" negative values to the required positive value. Here, MOD works like a clock, wrapping the time around when it goes past 24 hours. When you subtract minutes and the result is negative, the MOD function essentially keeps subtracting from 24 hours until it reaches the correct positive time. This is how we naturally think about time - if we start at 1:00 AM and we go back 2 hours, we end up at 11:00 PM the previous day, not at -1:00 AM.

Note: This is a good example of "modular arithmetic", sometimes called "clock arithmetic". In modular arithmetic, numbers "wrap around" when they reach a certain value - just like hours on a clock wrap around from 12 back to 1. In Excel's time system, the wrap-around point occurs at 1 (representing a full day), which is why the MOD function with a divisor of 1 works well for time calculations and appears often in other formulas.

Working with dates + times

Another way to avoid the problem of ending up with a negative time value is to start with a time that includes a date, sometimes called a "datetime". This lets you subtract very large numbers of minutes without any danger of getting a negative result. You can see how this works in the worksheet below, where the values in column B contain a date and a time. The formula in cell E5 is the same as the original above:

=B5+(C5/1440)

Because values in column B contain a date, the numbers are very large. For example, the numeric value in cell B5 is 45474.3333. As a result, we are able to add or subtract any number of minutes, even when the operation causes the date to change. All values in columns B and E are formatted with the same custom number format:

d-mmm hh:mm

You can customize this format in any way you like.