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 () func­tion adds the spec­i­fied time inter­val to the date and returns a Vari­ant (Date)

Format:

DateAdd(interval,number,date)

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

Set­ting Descrip­tion
Yyyy Year
Q Quar­ter
M Month
Y Day of year
D Day
W Week­day
Ww Week
H Hour
N Minute
S Sec­ond
  •  number
    • Manda­tory
    • Type: Numeric
    • The num­ber of inter­vals you want to add
  • date
    • Manda­tory
    • Type : Date
    • Date in which the inter­val will be added

Exam­ple:

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 Week­day inter­val (“W”) to work as adver­tised. It does not appear to behave any dif­fer­ently than Day (“D”).

    Have you expe­ri­enced dif­fer­ent results?

  2. June says:

    Hello,

    I used a slightly mod­i­fied ver­sion of your code:
    Func­tion FnDateAdd()

    Dim str­Date

    str­Date = CDate(“January 8, 2014″)

    strNew­Date = DateAdd(“w”, –5, strDate)

    Msg­Box strNewDate

    End Func­tion

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

    My end goal is to set sev­eral dif­fer­ent vari­ables as dif­fer­ent date val­ues, rel­a­tive to the cur­rent date. The chal­lenge I’m fac­ing is account­ing for week­ends and hol­i­days. 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 work­ing in same man­ner.
      but still we can do some­thing about the objec­tive you want to achieve..

      Here is my under­stand­ing abt ur prob­lem
      – from the cur­rent date, you want to do a task on each day, till the spec­i­fied period (exclud­ing week­ends) ..
      i just came up with the small piece of code for this…

      Sub FnDateAdd()

      Dim str­Date
      Dim main­Work­Book As Work­book
      Dim intCounter

      int­Counter = 1
      Set main­Work­Book = ActiveWorkbook

      For i = Date To DateAdd(“m”, 1, Date) ’ your spe­cific period
      str­Day = Format(i, “dddd”)
      If str­Day “Sat­ur­day” And str­Day “Sun­day” Then
      mainWorkBook.Sheets(“Sheet1”).Range(“A” & int­Counter) = i
      mainWorkBook.Sheets(“Sheet1”).Range(“B” & int­Counter) = str­Day
      int­Counter = int­Counter + 1
      End If
      Next

      End Sub

      Result :
      08-01-2014 Wednes­day
      09-01-2014 Thurs­day
      10-01-2014 Fri­day
      13-01-2014 Mon­day
      14-01-2014 Tues­day
      15-01-2014 Wednes­day
      16-01-2014 Thurs­day
      17-01-2014 Fri­day
      20-01-2014 Mon­day
      21-01-2014 Tues­day
      22-01-2014 Wednes­day
      23-01-2014 Thurs­day
      24-01-2014 Fri­day
      27-01-2014 Mon­day
      28-01-2014 Tues­day
      and so on„„,

      I am not say­ing this a best solu­tion but still can be used as a work around.
      Please cor­rect me if my under­stand­ing is correct

      Sumit Jain

  3. June says:

    Here’s my issue:

    Each day I’m try­ing to copy data from an exist­ing spread­sheet (2014-Jan-06_Outages.xlsx) into a new ver­sion of the exist­ing spread­sheet (2014-Jan-07_Outages.xlsx). There are sev­eral tabs within the spread­sheet — I copy the most recent, delete the prior day’s tab (2014-Jan-03), and cre­ate a new tab with all of the for­mu­las I need to process today’s work (today is really yes­ter­day, as we’re rec­on­cil­ing data posted yesterday).

    Here’s what I’ve been try­ing:
    Ver­sion 1 (does not account for week­ends):
    Dim Cur­rDate, Last­Date, Pre­v­Date, Pri­or­Date
    Dim FilePath, Extract­File As String
    Cur­rDate = Format(Date, “dd-mmm-yyyy”)
    Last­Date = Format(DateAdd(“w”, –1, Cur­rDate), “yyyy-mmm-dd”)
    Pre­v­Date = Format(DateAdd(“w”, –2, Cur­rDate), “yyyy-mmm-dd”)
    Pri­or­Date = Format(DateAdd(“w”, –3, Cur­rDate), “yyyy-mmm-dd”)
    FilePath = “Q:\Transfer\June\VBA\“
    Extract­File = “unrecon.csv”

    ’ Open spread­sheet
    Workbooks.Open Filename:=FilePath & Pre­v­Date & “_Outages.xlsx“
    ’ Save as new file name
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=FilePath & Last­Date & “_Outages.xlsx“
    Application.DisplayAlerts = True

    Ver­sion 2 (accounts for week­ends, but won’t open the spread­sheet — I get the fol­low­ing error mes­sage: “Sorry, we couldn’t find ‘Q:\Transfer\June\VBA\2014–01-06_Outages.xlsx”. Is it pos­si­ble it was moved, renamed or deleted?” — even though the file does exist, and ver­sion 1 can suc­cess­fully open the file):
    Dim Cur­rDate, Last­Date, Pre­v­Date, Pri­or­Date 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”) = “Hol­i­days“
    Range(“A9”) = “New Years Day“
    Range(“B9”) = “1/1/2014″
    Range(“A10”) = “MLK Day“
    Range(“B10”) = “1/20/2014″
    Range(“A11”) = “Washington’s Birth­day“
    Range(“B11”) = “2/17/2014″
    Range(“A12”) = “Good Fri­day“
    Range(“B12”) = “4/18/2014″
    Range(“A13”) = “Memo­r­ial Day“
    Range(“B13”) = “5/26/2014″
    Range(“A14”) = “Inde­pen­dence Day“
    Range(“B14”) = “7/4/2014″
    Range(“A15”) = “Labor Day“
    Range(“B15”) = “9/1/2014″
    Range(“A16”) = “Thanks­giv­ing Day“
    Range(“B16”) = “11/27/2014″
    Range(“A17”) = “Christ­mas“
    Range(“B17”) = “12/25/2014″
    Columns(“A:A”).Font.Bold = True
    Columns(“B:B”).NumberFormat = “yyyy-mmm-dd”

    Iden­tify source for dates
    Sheets(“Dates”).Select
    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”)
    FilePath = “Q:\Transfer\June\VBA\“
    Extract­File = “unrecon.csv”

    ’ Open spread­sheet
    Workbooks.Open Filename:=FilePath & Pre­v­Date & “_Outages.xlsx“
    ’ Save as new file name
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=FilePath & Last­Date & “_Outages.xlsx“
    Application.DisplayAlerts = True

  4. June says:

    My good­ness. A con­fer­ence call with some col­leagues helped me iden­tify my error.

    My date for­mat was not con­sis­tent 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 require­ment 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

    Exam­ple:

    Todays date: 2014-03-28
    Out­put: 2014-03-31

    Its crit­i­cal to note that , say if todays date was 30th. then if you add three days it rolls over to next month. how do we han­dle this?

    • sumitjain says:

      Dateadd() Func­tion will han­dle itself. Use Now () func­tion to get the today’s date and time. Change it ur for­mat u need. Then add 3 days. Clear?

  6. Lital says:

    how do i use a vari­able in the num­ber 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: