Working with Date and Time Functions in Excel VBA – Complete Tutorial

Date and Time Functions in Excel VBA

Learn Excel VBA to handle dates and times for automation, calculations, formatting, and task efficiency.

Insert a command button on the worksheet, then type the following code:

Year, Month, Day of a Date

Extracts the year value from a given or specified date. The Dim statement is used to declare a variable for holding a date. If you have a date in text format, use the DateValue function to convert it to an actual date.

Code:

Dim exampleDate As Date

exampleDate = DateValue("Jan 19, 2020")

MsgBox Year(exampleDate)

Result when the Command Button is clicked:

Date and Time Functions in Excel

Note: Use MONTH to extract the month and DAY to get the day from a date value.

DateAdd

The DateAdd function adds a specific number of days, months, or years to a given date and returns the updated date. The DateAdd function has three arguments. Use “d” as the first input to represent days. Use 3 as the second argument to add 3 days to the original date value. The third value shows the starting date from which the specified number of days will be added to calculate the result.

Code:

Dim firstDate As Date, secondDate As Date

firstDate = DateValue("Jan 19, 2020")
secondDate = DateAdd("d", 3, firstDate)

MsgBox secondDate

Result:

Note: Change “d” to “m” if you want to add months instead of days to a date. Place the cursor on DateAdd in the editor, press F1 to view help on interval specifiers. Dates are in US Format. Months first, Days second. Windows shows formats like date and time based on the regional settings set in your system.

Current Date and Time

The NOW function can be used to insert the current date and time automatically into your spreadsheet.

Code:

MsgBox Now

Result:

Hour, Minute, Second

The HOUR function helps you retrieve the hour from a complete time value in Excel.

Code:

MsgBox Hour(Now)

Result:

Note: Use the MINUTE and SECOND functions to get the minute and second from a given time value.

TimeValue

The TimeValue function helps convert time written as text into a number format that Excel uses for time calculations. Time in Excel is stored as a decimal value ranging from 0 to 1. For example, 0.5 equals 12:00 PM, indicating the midpoint of a 24-hour day.

Code:

MsgBox TimeValue("9:20:01 AM")

Result:

To verify that Excel handles time values as decimal numbers between 0 and 1, kindly add the code given below:

Dim y As Double
y = TimeValue("09:20:01 AM")
MsgBox y

Result:

1/9 Completed! Know more about dates and times ➝
Next Chapter: Events