Tuesday, July 01, 2008
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
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
=DATE(YEAR(A1) ,MONTH(A1) +6, DAY(A1)+10)
Generally, you cannot add two dates that are in serial format. For example, adding
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
=DATE(YEAR(A1) , MONTH(A1)+1, DAY(A1))
you will get the date
=DATE(YEAR(A1) ,MONTH(A1) +1,MIN(DAY( A1),DAY(DATE( YEAR(A1), MONTH(A1) +2,0))))
For example, if A1 contains the date
|Let's say you have a bill due every 6 months. To calculate the next due date from the current date in cell A1 you would use this formula.
=DATE(YEAR(A1) ,MONTH(A1) +7,0
Calculate the number of days in a month
How can I calculate future dates in Excel?
|If you want to correctly display a time greater than 24 hours, you can use the 37:30:55 built-in format. If you want to use a custom format instead, you must enclose the hours parameter of the format in brackets, for example:
How to Calculate Elapsed Time
How to Convert a Date to Its Decimal Number Equivalent
How to Find the Number of Days Between Today and a Future Date
How to Find the Number of Days, Months, and Years Between Two Dates
How to Find the Number of Weekdays Between Two Dates
How to Increase Dates Incrementally