Date Arithmetic

Adding Dates

You can add some number of days to a date by simply using the =SUM function. Since Excel stores dates as
a number of days, no further work is required. For example, to add 5 days to 1/1/98, in A1, use =A1+5,
which gives 1/6/98.

To add a number of months or years to a date, you first need to decompose the initial date into its year, month,
and day components, add in the desired offset, and then have Excel put the components back together.
For example, say you have a date in A1, to which you want to add 3 months and 4 days. You'd use the following formula:

=DATE(YEAR(A1), MONTH(A1)+3, DAY(A1)+4)

Excel will automatically handle the situation which arises when you pass a number greater than 12 to the MONTH function, or a number greater than 31 to the DAY function.

For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

gives 3/7/98.

Generally, you cannot add two dates that are in serial format. For example, adding 1/15/1998 and 6/15/1998 gives 6/30/2096, which is essentially meaningless. If you want to add some number of days to a date, but exclude weekends and holidays, you can use the WORKDAY function, which is part of the Analysis Tool Pack.

Note, however, that adding a month to a date may give you a result that you do not expect. For example, suppose A1 contains the date 31-Jan-2002. If you use the formula

=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))

you will get the date 3-March-2002, because the "31st" day of February, 2002, is 3-March. The formula below will work around this issue, returning the last day of the next month if the date in A1 is a day that does not exist in the next month.

=DATE(YEAR(A1),MONTH(A1)+2,0)

For example, if A1 contains the date 31-Jan-2002, this formula will return 28-Feb-2002. It is important that you and your users understand what "one month later" means in the context of your workbook.

Subtracting Dates

You can subtract some number of days to a date by simply using the SUM function. Since Excel stores dates
as a number of days, not further work is required. For example, to subtract 5 days to 6-Jan-98, in A1, use =A1-5,
which gives 1-Jan-98.

To subtract a number of months or years to a date, you first need to decompose the initial date into
its year, month, and day components, subtract the desired offset, and then let Excel put the
components back together. For example, say you have a date in A1, to which you want to
subtract 3 months and 4 days. You'd use the following formula:

=DATE(YEAR(A1), MONTH(A1)-3, DAY(A1)-4)

Excel will automatically handle the situation which arises when you pass a number less than 0 to the MONTH
function, or a number less than 0 to the DAY function.

For example, subtracting 6 months and 10 days to 4/5/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)-6, DAY(A1)-10)

gives 9/25/96.

Generally, you cannot subtract two dates that are in serial format. If you want to subtract some number of
days from a date, but exclude weekends and holidays, you can use the WORKDAYfunction, which is part
of the Analysis Tool Pack.

Adding Times

You can add times using the =SUM worksheet function. Just enter all of your times as HH:MM:SS, and then
use SUM to add them up. You may leave off the :SS if you prefer. By default, Excel will display the sum of
times in "time-of-day" format, meaning that adding 12:30 + 12:45 will yield 01:15. You can prevent Excel
from "rolling over" at 24 hours by formatting the result cell as [h]:mm which will cause it to display 25:15
rather than 01:15.

If you want to add up minutes and seconds, you must include a leading "0:" in your data. For example,
enter "0:10:20" to indicate 10 minutes, 20 seconds. When you sum these times, Excel will display the
sum in "time-of-day" format, meaning that adding 0:40:10 and 0:30:20 will yield 1:10:30. You can prevent
Excel form "rolling over" at the hour by formatting the result cell as [m]:ss which will cause it to
display 70:30 rather than 1:10:30.

Another method of adding times is to use the TIME function. To add 1 hour, 35 minutes, 10 seconds to a time in A1, use the function

=A1 + TIME(1,35,10)

Subtracting Times

Subtracting one time from another is a little more difficult, since Excel does not handle negative numbers as times. When you enter a time without a date, Excel assumes this is that time of day on January 1, 1900, since it puts a 0 in for the date component of the serial number. For example, you cannot subtract 18 hours from 4:00 PM, since this would result in a negative number (0.67 - 0.75 = -0.83).

You can get around this by entering a full date and then subtracting a time from this, and the formatting the result as time-only. For example to subtract 18 hours from 4:00 PM, enter the 4:00 PM as "1/1/98 4:00 PM" and subtract 18:00 from this. Formatting the result as hh:mm will result in "10:00 PM" which is what we would expect.

Another method is to use the TIME function. To subtract 1 hour, 35 minutes, 10 seconds from a time in A1, use the function

=A1 - TIME(1,35,10)

Time Intervals

You can determine the number of hours and minutes between two times by subtracting the two times. However, since Excel cannot handle negative times, you must use an =IF statement to adjust the time accordingly. If your times were entered without a date (e.g, 22:30), the following statement will compute the interval between two times in A1 and B1 .

=IF(A1>B1,B1+1-A1,B1-A1)

The "+1" in the formula causes Excel to treat B1 as if it were in the next day, so 02:30-22:00 will result in 4:30, four hours and thirty minutes, which is what we would expect. To covert this to a decimal number, for example, 4.5, indicating how many hours, multiply the result by 24 and format the cell as General or Decimal, as in

=24*(IF(A1>B1,B1+1-A1,B1-A1))

Rounding Times

For many scheduling or payroll applications, it is useful to round times to the nearest hour, half-hour, or quarter-hour. The MROUND function, which is part of the Analysis ToolPack add-in module, is very useful for this. Suppose you have a time in cell A1. In B1 , enter the number of minutes to which you want to round the time -- for example, enter 30 to round to the nearest half-hour. The formula

=TIME(HOUR(A1),MROUND(MINUTE(A1),B1),0)

will return a time rounded to the nearest half-hour, either up or down, depending what is closest. For example, 12:14 is rounded to 12:00, and 12:15 is rounded to 12:30.

To round either up or down to the nearest interval, enter the interval in B1, and use either of the following formulas:

=TIME(HOUR(A1),FLOOR(MINUTE(A1),B1),0)
to round to the previous interval (always going earlier, or staying the same).

=TIME(HOUR(A1),CEILING(MINUTE(A1),B1),0)
to round to the next interval (always going later, or staying the same).