How to work with and modify dates in excel VBA?

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*

​

Current Date

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.

Add years

MyNewDate = DateAdd("yyyy", 3, MyDate)
Debug.Print MyNewDate

Add Months

MyNewDate = DateAdd("m", 3, MyDate)
Debug.Print MyNewDate

Add Days

MyNewDate = DateAdd("d", 3, MyDate)
Debug.Print MyNewDate

Add Hours

MyNewDate = DateAdd("h", 3, MyDate)
Debug.Print MyNewDate

Add Minutes

MyNewDate = DateAdd("n", 3, MyDate)
Debug.Print MyNewDate

Add Seconds

MyNewDate = DateAdd("s", 3, MyDate)
Debug.Print MyNewDate

DateSerial function

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.

DateValue function

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 function

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.

Custom Dateformats

'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")