Last Updated on January 11, 2023 by token
Description of date and time functions.
VBA date functions, as the name suggests, are used to manipulate the values related to dates and time. On their basis, you can catch any parameter such as day, month, year, etc. from the given date. You can also subtract two dates from each other.
If you want to enter a date, time value or a date and time value into your VBA code, you can use the following code:
Sub DateTimeInsertExample1() Dim datVal As Date Dim datTimeVal As Date Dim datDatTimeVal As Date datVal = "2020/09/01" datTimeVal = "23:41:59" datDatTimeVal = "2020/09/01 23:41:59" MsgBox (datVal) MsgBox (datTimeVal) MsgBox (datDatTimeVal) End Sub
List of date and time functions in VBA
I present the VBA date functions in the table. First, you will learn about the functions that indicate the current date. Functions are listed below:
| FUNCTION | DESCRIPTION |
|---|---|
| DATE () | Returns the current date from system dates. The function can be compared to the TODAY () formula from Excel |
| DATEADD (interval, number, date) | Adds the specified date and time to the specified number of units according to the interval argument. By inserting a negative sign before the number, you can reduce the entered date. The number of units for which you want to change your date is given in the parameter number. |
| DATEDIFF (interval, date_1, date_2) | Returns the difference of two dates and gives the result as a number of units that you set in interval. |
| DATEPART (interval, date) | Returns the value of a part of the date or time (year, day, hour, etc.) depending on the interval parameter specified. |
| DATESERIAL (year, month, day) | Returns the date according to the YMD arguments. |
| DATEVALUE (string) | Returns the date from a text value |
| DAY (date) | Returns the day of the month from the given date as number 1-31 |
| HOUR (date) | Returns the hour from time 0-23 |
| MINUTE (date) | Returns the number of minutes from the given date as a number 0-59 |
| MONTH (date) | Returns the month from the given date as a number 1-12 |
| MONTHNAME (date, [abbreviate]) | Returns the name of the month from given date. The function has a second argument. The True value will give the name of the month in short form. |
| NOW () | Returns the current date and time |
| TIMESERIAL (hour, minute, second) | Returns the time value according to the given HMS parameters. |
| TIMEVALUE (String) | Returns the time from the text value |
| WEEKDAY (date) | Returns the day of the week from the given date |
| WEEKDAYNAME (number, [abbreviate], [firstdayofweek]) | Returns the name of the day of the week depending on the given day number of the week. The optional parameter with the value True will give the name of the month in short form. The firstdayofweek parameter set to 2 means that the first day of the week is Monday. |
| YEAR (date) | Returns the year from the given date in the range 1900-9999 |
As you can see, some of VBA date functions use time intervals as arguments. These are Intervals for the DATADD (), DATEDIFF (), DATEPART () functions. Below is a table showing each of intervals. Use this values as a function argument.
| Interval | Importance |
| yyyy | Year |
| q | Quarter |
| m | Month |
| s | Day of the year |
| d | Day |
| w | Day of the week |
| ww | Week |
| h | Hour |
| n | Minute |
| s | Second |
An example of using the date and time function.
Below is listed a short example. If you want to check how the functions you have just learned works. Create a program in a new module. Let’s write the code using VBA variables with the values of date, time, date and time.
Sub DateFunctionsExample2()
Dim datVal As Date
Dim datTimeVal As Date
Dim datDatTimeVal As Date
datVal = "2020/09/01"
datTimeVal = "23:41:59"
datDatTimeVal = "2020/09/01 23:41:59"
MsgBox (datVal)
MsgBox (datTimeVal)
MsgBox (datDatTimeVal)
Range("A1") = Date
Range("A2") = DateAdd("m", -2, datVal)
Range("A3") = DateDiff("d", Date, datVal)
Range("A4") = DatePart("yyyy", datVal)
Range("A5") = DateSerial(2015, 1, 1)
Range("A6") = DateValue(datVal)
Range("A7") = Day(datVal)
Range("A8") = Hour(Now())
Range("A9") = Minute(Now())
Range("A10") = Month(datVal)
Range("A11") = MonthName(9, True)
Range("A12") = Now()
Range("A13") = TimeSerial(13, 23, 59)
Range("A14") = TimeValue("13:23:59")
Range("A15") = Weekday(Date)
Range("A16") = WeekdayName(3, True, 2)
Range("A17") = Year(Date)
End Sub
Examples in Excel file are attached below:
Tasks (You can enter the solution in a comment)
- Calculate on today’s date day, month, year, quarter.
- Using the current time, insert hour, minutes and seconds into the cells of the worksheet
- Check how many days have remained until the end of the year.
- Check the day of the week for your birthday.
