Date and time functions in Excel VBA

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
Date and time functions in Excel VBA
Date and time functions in Excel VBA

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:

FUNCTIONDESCRIPTION
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.

IntervalImportance
yyyyYear
qQuarter
mMonth
sDay of the year
dDay
wDay of the week
wwWeek
hHour
nMinute
sSecond

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
An examples of using the date and time functions.
An examples of using the date and time functions.

Examples in Excel file are attached below:

Tasks (You can enter the solution in a comment)

  1. Calculate on today’s date day, month, year, quarter.
  2. Using the current time, insert hour, minutes and seconds into the cells of the worksheet
  3. Check how many days have remained until the end of the year.
  4. Check the day of the week for your birthday.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: