Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Date-Time Functions – DateAdd()

Description:

The DateAdd () function adds the specified time interval to the date and returns a Variant (Date)

Format:

DateAdd(interval,number,date)

Arguments:
    • interval
      • Manda­tory
      • Type: String expression
      • The time interval you want to add

Setting Description
Yyyy Year
Q Quarter
M Month
Y Day of year
D Day
W Weekday
Ww Week
H Hour
N Minute
S Second
  •  number
    • Manda­tory
    • Type: Numeric
    • The number of intervals you want to add
  • date
    • Manda­tory
    • Type : Date
    • Date in which the interval will be added

Example:

Function FnDateAdd()

Dim strDate

strDate = CDate("June 24, 2013")    

strNewDate = DateAdd("m", 2, strDate)

MsgBox strNewDate

strNewDate2 = DateAdd("yyyy", -3, strDate)

MsgBox strNewDate2

strDate2 = CDate("June 24, 2013 12:00:00 PM")    

MsgBox DateAdd("h", 2, DateAdd("n", 23, strDate2))

End Function 
AddDate

AddDate

 


 

You may also like...

10 Responses

  1. June says:

    Using Excel 2013, I have not been able to get the Weekday interval (“W”) to work as advertised. It does not appear to behave any differently than Day (“D”).

    Have you experienced different results?

  2. June says:

    Hello,

    I used a slightly modified version of your code:
    Function FnDateAdd()

    Dim strDate

    strDate = CDate(“January 8, 2014”)

    strNewDate = DateAdd(“w”, -5, strDate)

    MsgBox strNewDate

    End Function

    I expected to see a result of “1/1/2013”, which is 5 weekdays (work days) before today. But I got “1/3/2013” as a result.

    My end goal is to set several different variables as different date values, relative to the current date. The challenge I’m facing is accounting for weekends and holidays. There doesn’t seem to be an easy way to do this. I can send you all of my code, if you like.

    • sumitjain says:

      Yes, i checked both “W” and “D” are working in same manner.
      but still we can do something about the objective you want to achieve..

      Here is my understanding abt ur problem
      – from the current date, you want to do a task on each day, till the specified period (excluding weekends) ..
      i just came up with the small piece of code for this…

      Sub FnDateAdd()

      Dim strDate
      Dim mainWorkBook As Workbook
      Dim intCounter

      intCounter = 1
      Set mainWorkBook = ActiveWorkbook

      For i = Date To DateAdd(“m”, 1, Date) ‘ your specific period
      strDay = Format(i, “dddd”)
      If strDay “Saturday” And strDay “Sunday” Then
      mainWorkBook.Sheets(“Sheet1”).Range(“A” & intCounter) = i
      mainWorkBook.Sheets(“Sheet1”).Range(“B” & intCounter) = strDay
      intCounter = intCounter + 1
      End If
      Next

      End Sub

      Result :
      08-01-2014 Wednesday
      09-01-2014 Thursday
      10-01-2014 Friday
      13-01-2014 Monday
      14-01-2014 Tuesday
      15-01-2014 Wednesday
      16-01-2014 Thursday
      17-01-2014 Friday
      20-01-2014 Monday
      21-01-2014 Tuesday
      22-01-2014 Wednesday
      23-01-2014 Thursday
      24-01-2014 Friday
      27-01-2014 Monday
      28-01-2014 Tuesday
      and so on,,,,,

      I am not saying this a best solution but still can be used as a work around.
      Please correct me if my understanding is correct

      Sumit Jain

  3. June says:

    Here’s my issue:

    Each day I’m trying to copy data from an existing spreadsheet (2014-Jan-06_Outages.xlsx) into a new version of the existing spreadsheet (2014-Jan-07_Outages.xlsx). There are several tabs within the spreadsheet – I copy the most recent, delete the prior day’s tab (2014-Jan-03), and create a new tab with all of the formulas I need to process today’s work (today is really yesterday, as we’re reconciling data posted yesterday).

    Here’s what I’ve been trying:
    Version 1 (does not account for weekends):
    Dim CurrDate, LastDate, PrevDate, PriorDate
    Dim FilePath, ExtractFile As String
    CurrDate = Format(Date, “dd-mmm-yyyy”)
    LastDate = Format(DateAdd(“w”, -1, CurrDate), “yyyy-mmm-dd”)
    PrevDate = Format(DateAdd(“w”, -2, CurrDate), “yyyy-mmm-dd”)
    PriorDate = Format(DateAdd(“w”, -3, CurrDate), “yyyy-mmm-dd”)
    FilePath = “Q:\Transfer\June\VBA\”
    ExtractFile = “unrecon.csv”

    ‘ Open spreadsheet
    Workbooks.Open Filename:=FilePath & PrevDate & “_Outages.xlsx”
    ‘ Save as new file name
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=FilePath & LastDate & “_Outages.xlsx”
    Application.DisplayAlerts = True

    Version 2 (accounts for weekends, but won’t open the spreadsheet – I get the following error message: “Sorry, we couldn’t find ‘Q:\Transfer\June\VBA\2014-01-06_Outages.xlsx”. Is it possible it was moved, renamed or deleted?” – even though the file does exist, and version 1 can successfully open the file):
    Dim CurrDate, LastDate, PrevDate, PriorDate As String
    Dim FilePath As String

    Application.DisplayAlerts = False
    Sheets(“Dates”).Delete
    Application.DisplayAlerts = True
    Sheets.Add After:=Worksheets(“sheet1”)
    Sheets(“Sheet2”).Name = “Dates”
    Range(“A3”) = “TODAY”
    Range(“B3”).FormulaR1C1 = “=today()”
    Range(“A4”) = “{last}”
    Range(“B4”).FormulaR1C1 = “=workday(R[-1]C,-1,R9C2:R17C2)”
    Range(“A5”) = “{prev}”
    Range(“B5”).FormulaR1C1 = “=workday(R[-2]C,-2,R9C2:R17C2)”
    Range(“A6”) = “{prev}-1”
    Range(“B6”).FormulaR1C1 = “=workday(R[-3]C,-3,R9C2:R17C2)”
    Range(“A8”) = “Holidays”
    Range(“A9”) = “New Years Day”
    Range(“B9”) = “1/1/2014”
    Range(“A10”) = “MLK Day”
    Range(“B10”) = “1/20/2014”
    Range(“A11”) = “Washington’s Birthday”
    Range(“B11”) = “2/17/2014”
    Range(“A12”) = “Good Friday”
    Range(“B12”) = “4/18/2014”
    Range(“A13”) = “Memorial Day”
    Range(“B13”) = “5/26/2014”
    Range(“A14”) = “Independence Day”
    Range(“B14”) = “7/4/2014”
    Range(“A15”) = “Labor Day”
    Range(“B15”) = “9/1/2014”
    Range(“A16”) = “Thanksgiving Day”
    Range(“B16”) = “11/27/2014”
    Range(“A17”) = “Christmas”
    Range(“B17”) = “12/25/2014”
    Columns(“A:A”).Font.Bold = True
    Columns(“B:B”).NumberFormat = “yyyy-mmm-dd”

    ‘Identify source for dates
    Sheets(“Dates”).Select
    CurrDate = Format(Range(“B3”), “yyyy-mmm-dd”)
    LastDate = Format(Range(“B4”), “yyyy-mm-dd”)
    PrevDate = Format(Range(“B5”), “yyyy-mm-dd”)
    PriorDate = Format(Range(“B6”), “yyyy-mm-dd”)
    FilePath = “Q:\Transfer\June\VBA\”
    ExtractFile = “unrecon.csv”

    ‘ Open spreadsheet
    Workbooks.Open Filename:=FilePath & PrevDate & “_Outages.xlsx”
    ‘ Save as new file name
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=FilePath & LastDate & “_Outages.xlsx”
    Application.DisplayAlerts = True

  4. June says:

    My goodness. A conference call with some colleagues helped me identify my error.

    My date format was not consistent in the last section:

    Cur­rDate = Format(Range(“B3”), “yyyy-mmm-dd”)
    Last­Date = Format(Range(“B4”), “yyyy-mm-dd”) <—-
    Pre­v­Date = Format(Range(“B5”), “yyyy-mm-dd”) <—-
    Pri­or­Date = Format(Range(“B6”), “yyyy-mm-dd”) <—-

    Should have been

    Cur­rDate = Format(Range(“B3”), “yyyy-mmm-dd”)
    Last­Date = Format(Range(“B4”), “yyyy-mmm-dd”) <—-
    Pre­v­Date = Format(Range(“B5”), “yyyy-mmm-dd”) <—-
    Pri­or­Date = Format(Range(“B6”), “yyyy-mmm-dd”) <—-

  5. Vishal says:

    my requirement is:

    i need to extract todays’ date and add 3 days to it and post it to a VBA form in below format:

    YYYY-MM-DD

    Example:

    Todays date: 2014-03-28
    Output: 2014-03-31

    Its critical to note that , say if todays date was 30th. then if you add three days it rolls over to next month. how do we handle this?

    • sumitjain says:

      Dateadd() Function will handle itself. Use Now () function to get the today’s date and time. Change it ur format u need. Then add 3 days. Clear?

  6. Lital says:

    how do i use a variable in the number part of the function?

    i=0
    dateadd(“m”, i, getdate())

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: