DATEADD

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 DATEVALUE function to return the date given a string representation of a date.
* You can use the FORMAT function to return a text string of a date in a particular format.
* 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()                                 '18/02/2024 08:53:34  
Debug.Print DateAdd("yyyy", 1, VBA.Now())             '18/02/2025 08:53:34  
Debug.Print DateAdd("yyyy", 1, "01-01-2024")          '01/01/2025  
Debug.Print DateAdd("q", 1, "01-01-2024")             '01/04/2024  
Debug.Print DateAdd("m", 1, VBA.Now())                '18/03/2024 08:53:34  
Debug.Print DateAdd("m", 1, "01-01-2024")             '01/02/2024  

'2024 is a leap year, so February has 29 days
Debug.Print DateAdd("m", 1, VBA.CDate("31 Jan 2024")) '29/02/2024  

'these 3 lines all add one day
Debug.Print DateAdd("d", 1, "01-01-2024")             '02/01/2024  
Debug.Print DateAdd("y", 1, "01-01-2024")             '02/01/2024  
Debug.Print DateAdd("w", 1, "01-01-2024")             '02/01/2024  

'this adds 5 weekdays
Debug.Print DateAdd("w", 5, "01-01-2024")             '06/01/2024  

'this adds 5 weeks
Debug.Print DateAdd("ww", 5, "01-01-2024")            '05/02/2024  

Debug.Print VBA.Now()                                 '18/02/2024 08:53:34  
Debug.Print DateAdd("w", 2, VBA.Now())                '20/02/2024 08:53:34 (weekdays)  
Debug.Print DateAdd("h", 5, VBA.Now())                '18/02/2024 13:53:34 (hours)  
Debug.Print DateAdd("n", 5, VBA.Now())                '18/02/2024 08:58:34 (minutes)  
Debug.Print DateAdd("s", 10, VBA.Now())               '18/02/2024 08:53:44 (seconds)  

Dim lSerial As Long 
lSerial = DateAdd("d", 1, "01/07/2024") 
Debug.Print lSerial                                   '45475