How to work with Dates in VBA. As with most programming languages Dates can be a bit tricky sometimes.
Below we go through most of your Date needs *not too be confused with dating needs*
Dim MyDate MyDate = Date 'MyDate contains the current system date. Debug.Print MyDate
Date additions or subtractions
DateAdd(interval, number, date) interval = interval of time you want to add. Years, months, days number = number of years, months, days date = date you wish to add to.
In the below example my number to add is 3 of something. Keep in mind that I can also go back 3 of something with -3 instead.
MyNewDate = DateAdd("yyyy", 3, MyDate) Debug.Print MyNewDate
MyNewDate = DateAdd("m", 3, MyDate) Debug.Print MyNewDate
MyNewDate = DateAdd("d", 3, MyDate) Debug.Print MyNewDate
MyNewDate = DateAdd("h", 3, MyDate) Debug.Print MyNewDate
MyNewDate = DateAdd("n", 3, MyDate) Debug.Print MyNewDate
MyNewDate = DateAdd("s", 3, MyDate) Debug.Print MyNewDate
DateSerial(year, month, day) Great function for configuring dates without using the system languages.
MyDate contains the date for October 24, 1929. Known for the great stock market crash.
MyDate = DateSerial(1969, 10, 24) Debug.Print MyDate 'returns the date in dateformat.
Another option to set a date is to convert it to dateformat from text with the use of DateValue function.
I will show it below, however I tend to use DateSerial function mostly as I find it more clear.
Dim MyDate MyDate = DateValue("October 24, 1969") Debug.Print MyDate 'Returns my date in dateformat.
DateDiff(interval, date1, date2) interval = same as before (i.e "yyyy", "m", "d", "h", "n", "s)
MyDate = DateSerial(1969, 10, 24) MyMyNewDate = DateSerial(1971, 10, 24) 'diff in years MyDiff = DateDiff("yyyy", MyDate, MyMyNewDate) Debug.Print MyDiff 'returns 2, which is my difference in years.
Take apart and put together a date again
'Now this exercise is good to get a sense of how to work with dates, and modify them. 'In order to modify something it is sometimes easier to take the date apart, modify that or several parts (years, months, days), and then put it together again. MyDate = DateSerial(1969, 10, 24) 'our date MyYear = Year(MyDate) '1969 MyMonth = Month(MyDate) '10, or october. MyDay = Day(MyDate) '24th 'Now let's say I only know the pieces or needed to modify a year, month or day. 'Let's just add it again with the dateserial function. MyCompiledDate = DateSerial(MyYear, MyMonth, MyDay) Debug.Print MyCompiledDate 'returns my date as a dateformat again.
'FormatDateTime(Date, [ NamedFormat ]) MsgBox (FormatDateTime(MyDate, vbGeneralDate)) 'general MsgBox (FormatDateTime(MyDate, vbLongDate)) 'writes in text MsgBox (FormatDateTime(MyDate, vbLongTime)) 'returns time MsgBox (FormatDateTime(MyDate, vbShortDate)) 'probably the same as generaldate MsgBox (FormatDateTime(MyDate, vbShortTime)) 'returns time
...Or we can go completely custom formatting with the Format function that can format most things.
'I can for example first set my date to format by using the Now() function which will return the current date. MyDate = Now() 'returns current date 'yy = is the year in 2 digits. 'mmm = is the month in short text. 'Oct' 'd = is the single digit day excluding 0 in front if possible. 'For example: Debug.Print Format(MyDate, "yy-mmm-d")