DATEADD returns a Date String containing a date to which a specified time interval has been added.
SYNTAX:
DATEADD(Interval,Number,Date,Format)
Interval: The interval of the time you want to add in quotes. The following table displays the valid Intervals.
| "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.
EXAMPLES:
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