DATEADD returns a Date String containing a date to which a specified time interval has been added.



Interval: The interval of the time you want to add in quotes.  The following table displays the valid Intervals.

  Interval    Description 
"yyyy" Year
"y" Day of Year
"q" Quarter
"m"  Month
"d" Day
"w" Weekday
"ww" Week
"h" Hour
"n" Minute
"s" Second

Number: The number of intervals you want to add.  It can be positive (to get dates in the future) or negative (to get days in the past).

Date: A string representing a valid date, i.e. "1/1/2004", a cell reference to a cell containing a string, or a function that returns a date string (such as DSORT)

Format: Date Format (i.e. mm/dd/yyyy...) for the date returned by the function. 



DateAdd("M",1,"1/31/95","mm/dd/yyyy") Returns 2/28/95, not 2/31/95.

DateAdd("D",3,A1,"mm/dd/yyyy") Returns 1/14/2004. Cell A1 = 1/11/2004


Download Example Template: Help_DateAdd_Examples.ss3

Example 2: Using DateAdd to specify a one year date range.


To set up a date range looking ahead, first type "Start Date" in the cell next to where you want the date. In the next cell, same row, Go to, Locate, Date, select Start Date and the format you want the date to appear as.

Go to the location that you want the end date and type "End Date" in the cell next to where you want the date. In the next cell, same row, use the DATEADD function: =DATEADD("M",12,D6,"mm/dd/yy").

The End date shows the same month but a year ahead. If I want the End date to show the previous month, I would need to copy the DATEADD function and paste into a seperate cell.

In the this cell change the syntax to reflect "D", -1 to go back one from the start date, and refer to the cell with the start date in it.


Now go back to the End Date cell and change the syntax to show  the cell referance where the DATEADD "Day" is.

Now to hide cell C5 use the paint brush and make the cell White on White.

Download Example 2 Template: Help_DateAdd_Example2.ss3

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?


Tell us why you rated the content this way. (optional)
Approved Comments...
No user comments available for this article.
Created on 1/22/2008 8:30 PM.
Last Modified on 9/25/2008 11:32 AM.
Last Modified by No Author Name Available!.
Article has been viewed 3820 times.
Rated 6 out of 10 based on 4 votes.
Print Article
Email Article