DATEADD(interval, number, date) |
Returns the date with a specified time interval added (Date). |
interval | The interval of time you want to add (String): "yyyy","YYYY" = year "q","Q" = quarter "m","M" = month "y","Y" = day of year "d","D" = day "w","W" = weekday "ww","WW" = week "h","H" = hour "n","N" = minute "s","S" = second |
number | The number of intervals to add (Long). |
date | The date you want the interval added to (Date). |
REMARKS |
* The "interval" is confusing, for example: "y","Y" = day of year and not years. * If "interval" = "y" or "Y", this will add Days and not Years. * To add years to a date, you need to use "yyyy" or "YYYY". * The "number" can be any numeric expression. * If "number" is not a Long, then it is rounded to the nearest whole number before getting evaluated. * This function will never return an invalid date. * If you subtract more time than is in the "date", then an error occurs. * The format of the date that is returned is determined by the PC settings from the control panel. * This function can be used to add or subtract a specified time interval from a date. * You can use the CDATE function to return an expression converted to a Date data type. * You can use the DATEDIFF function to return the number of a given time interval between two specified dates. * You can use the DATEPART function to return the specified part of a given date. * You can use the DATESERIAL function to return the date given a year, month and day. * You can use the NOW function to return the current system date and time. * The equivalent .NET function is Microsoft.VisualBasic.DateAndTime.DateAdd * For the Microsoft documentation refer to learn.microsoft.com |
Debug.Print VBA.Now() '09/10/2021 06:41:42
Debug.Print DateAdd("yyyy", 1, VBA.Now()) '09/10/2022 06:41:42
Debug.Print DateAdd("yyyy", 1, "01-01-2020") '01/01/2021
Debug.Print DateAdd("q", 1, "01-01-2020") '01/04/2020
Debug.Print DateAdd("m", 1, VBA.Now()) '09/10/2021 06:41:42
Debug.Print DateAdd("m", 1, "01-01-2020") '01/02/2020
'2020 is a leap year, so February has 29 days
Debug.Print DateAdd("m", 1, VBA.CDate("31 Jan 2000")) '29/02/2000
'these 3 lines all add one day
Debug.Print DateAdd("y", 1, "01-01-2020") '02/01/2020
Debug.Print DateAdd("d", 1, "01-01-2020") '02/01/2020
Debug.Print DateAdd("w", 1, "01-01-2020") '02/01/2020
'this adds 5 weekdays
Debug.Print DateAdd("w", 5, "01-01-2020") '06/01/2020
'this adds 5 weeks
Debug.Print DateAdd("ww", 5, "01-01-2020") '05/02/2020
Debug.Print VBA.Now() '09/10/2021 06:41:42
Debug.Print DateAdd("w", 2, VBA.Now()) '11/10/2021 06:41:42 (weekdays)
Debug.Print DateAdd("h", 5, VBA.Now()) '09/10/2021 11:41:42 (hours)
Debug.Print DateAdd("n", 5, VBA.Now()) '09/10/2021 06:46:42 (minutes)
Debug.Print DateAdd("s", 10, VBA.Now()) '09/10/2021 06:41:52 (seconds)
Dim lSerial As Long
lSerial = DateAdd("d",1,"01/07/2012")
Debug.Print lSerial '41092
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited Top