If you're writing an application that works with dates And times in Excel, you'll probably be doing some sort of arithmetic operations with those dates. This page explains several formulas that you may find useful.
You can add some number of days to a date by simply using the =SUM
function. Since Excel stores dates as
To add a number of months or years to a date, you first need to decompose the initial
date into its year, month,
=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
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 theWORKDAY 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.
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.
You can subtract some number of days to a date by simply using the SUM function. Since Excel stores dates
To subtract a number of months or years to a date, you first need to decompose the
initial date into
=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
For example, subtracting 6 months and 10 days to 4/5/97, in A1 with the formula
Generally, you cannot subtract two dates that are in serial format. If
you want to subtract some number of
You can add times using the =SUM worksheet function.
Just enter all of your times as HH:MM:SS, and then
If you want to add up minutes and seconds, you must include a leading "0:" in your data. For example,
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 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 TIMEfunction. To subtract 1 hour, 35 minutes, 10 seconds from a time in A1, use the function
=A1 - TIME(1,35,10)
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
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
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
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
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:
And Time Related Procedures are described on the following pages.
VBA Procedures For Dates And Times