This post is dedicated on handling the dates in VBA by using some in-built functions. I have explained some functions in short, used to work with dates.
Assigning a datevalue to a variable.
MyDate = "10-12-2017" 'Directly passing the date
MyDate = DateSerial(2017, 12, 10) 'DateSerial function to assign a date
MyDate = Date 'Passing today's date using "date" function
Adding an interval to a date like years, months, quarters etc. We have an in-built function DATEADD for it which takes three arguments Interval, Number, Date.
Example:
DateAdd("yyyy", 1, "10-12-2017") 'Adding one year to a date; new date is 10-12-2018
DateAdd("m", 2, "10-12-2017") 'Adding 2 months to a date; new date is 10-02-2018
DateAdd("d", 3, "10-12-2017") 'Adding 3 days to a date; new date is 13-12-2017
DateAdd("ww", 1, "10-12-2017") 'Adding a week to a date; new date is 17-12-2017
DateAdd("q", 1, "10-12-2017") 'Adding a quarter to a date; new date is 10-03-2018
Similarly, we have other intervals to add hours, minutes and seconds to a date. Strings used to specifiy the others intervals:
"h" - hours, "n" - minutes, "s" - seconds
Fetching or extracting a part of the date. We have DATEPART function in VBA to do the job. It takes four arguments Interval, Date, FirstDayofWeek, FirstWeekofYear. Last two arguments are optional.
FirstDayofWeek - Specifies the weekday that should be used as the first day of the week. Default is vbSunday.
FirstWeekofYear - Specifies the week that should be used as the first week of the year. Default is vbFirstJan1.
Example:
DatePart("d", "10-12-2017") 'Returns day of month (1-31) i.e. 10
DatePart("m", "10-12-2017") 'Returns month i.e. 12
DatePart("yyyy", "10-12-2017") 'Returns year i.e. 2017
DatePart("q", "10-12-2017") 'Returns quarter i.e. 4
DatePart("ww", "10-12-2017") 'Returns week of year (1-53) i.e. 50
DatePart("w", "10-12-2017") 'Returns day of week (1-7) i.e. 1
Likewise DATEADD function, we have other intervals in DATEPART function also. Strings used to specify other intevals:
"h" - hours, "n" - minutes, "s" - seconds, "y" - day of year (1-366)
We also have MONTH and MONTHNAME function to return the month number and name of the month respectively. MONTH function takes a date only as an argument. MONTHNAME function takes two arguments Month, Abbreviation. Second argument is optional which takes boolean value TRUE or FALSE. TRUE to abbreviate the month name like Jan, Feb, Mar etc.
Example:
Month("10-12-2017") 'Returns 12
MonthName(Month("10-12-2017"), False) 'Returns "December "
MonthName(Month("10-12-2017"), True) 'Returns "Dec"
Likewise MONTH and MONTHNAME function, we have WEEKDAY and WEEKDAYNAME function to return the day of the week and name of the weekday respectively. WEEKDAY takes two arguments Date and FirstDayofWeek. Second argument is optional which specifies the weekday that should be used as first day of the week. Default is vbSunday if nothing specifies.
WEEKDAYNAME takes three arguments Weekday, Abbreviation and FirstDayofWeek. Last two arguments are optional. Abbreviation takes boolean value TRUE or FALSE. TRUE to abbreviate weekday name like Sun, Mon etc. FirstDayofWeek specifies the weekday that should be used as first day of the week. Default is vbSunday if nothing specifies.
Example:
Weekday("10-12-2017", vbMonday) 'Returns the day of the week i.e. 7
WeekdayName(Weekday("10-12-2017", vbSunday), False, vbSunday) 'Returns "Sunday"
WeekdayName(Weekday("10-12-2017", vbMonday), True, vbSunday) 'Returns "Sat"
Formatting dates. VBA has an in-built function FORMATDATETIME to assign a format to the given date. This function takes two arguments Expression, NameFormat. Second argument is optional.
Example:
FormatDateTime("10-12-2017") 'Returns 10-12-2017
FormatDateTime("10-12-2017", vbLongDate) 'Returns 10 December 2017
FormatDateTime("10-12-2017", vbShortDate) 'Returns 10-12-2017
FormatDateTime("10-12-2017 09:30:00", vbLongTime) 'Returns 09:30:00
FormatDateTime("10-12-2017 09:30:00", vbShortTime) 'Returns 09:30
Finally, wrapping up this post with ISDATE function used to check whether the passed value is a valid date or not. It takes only one arguement i.e. Expression. It returns TRUE if the passed value is a proper date, time or or a text representation of date or time and FALSE for all non-date strings and numbers.
IsDate("10-12-2017") 'Returns TRUE
IsDate(43079) 'Returns FALSE
IsDate("Excel VBA Tips") 'Returns FALSE
IsDate(#9:30:00 AM#) 'Returns TRUE
Is this post helpful?
Kindly post your valuable comments or suggestions.
Thanks!
