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.

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 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.

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).

Other Date And Time Related Procedures are described on the following pages.

Adding Months And Years

The DATEDIF Function

Date Intervals

Dates And Times

Date And Time Entry

Date And Time Arithmetic

Distributing Dates Over Intervals