Date and Time in Excel VBA

Year, Month, Day of a Date | DateAdd | Current Date and Time | Hour, Minute, Second | TimeValue

Learn how to work with dates and times in Excel VBA.

Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.

Year, Month, Day of a Date

The following macro gets the year of a date. To declare a date, use the Dim statement. To initialize a date, use the DateValue function.

Code:

Dim exampleDate As Date

exampleDate = DateValue(“Jun 19, 2010”)

MsgBox Year(exampleDate)

Result:

Note: Use Month and Day to get the month and day of a date.

DateAdd

To add a number of days to a date, use the DateAdd function. The DateAdd function has three arguments. Fill in “d” for the first argument to add days. Fill in 3 for the second argument to add 3 days. The third argument represents the date to which the number of days will be added.

Code:

Dim firstDate As Date, secondDate As Date

firstDate = DateValue(“Jun 19, 2010”)
secondDate = DateAdd(“d”, 3, firstDate)

MsgBox secondDate

Result:

Note: Change “d” to “m” to add a number of months to a date. Place your cursor on DateAdd in the Visual Basic Editor and click F1 for help on the other interval specifiers. Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings.

Current Date and Time

To get the current date and time, use the Now function.

Code:

MsgBox Now

Result:

Hour, Minute, Second

The get the hour of a time, use the Hour function.

Code:

MsgBox Hour(Now)

Result:

Note: Use Minute and Second to get the minute and second of a time.

TimeValue

The TimeValue function converts a string to a time serial number. The time’s serial number is a number between 0 and 1. For example, noon (halfway through the day) is represented as 0.5.

Code:

MsgBox TimeValue(“9:20:01 am”)

Result:

Now, to clearly see that Excel handles times internally as numbers between 0 and 1, add the following code lines:

Dim y As Double
y = TimeValue(“09:20:01”)
MsgBox y

Result:

Compare Dates and Times

This example teaches you how to compare dates and times in Excel VBA. Dates and times are stored as numbers in Excel and count the number of days since January 0, 1900. What you see depends on the number format.

1. Enter some numbers in column A.

2. These numbers are dates. This is a perfect way to enter some dates without worrying about the Date format. Change the format to Date (Right click on the column A header, Format Cells and choose Date).

Result:

Note: Dates are in US Format. Months first, Days Second. This type of format depends on your windows regional settings.

Place a command button on your worksheet and add the following code lines:

3. Declare the variable i of type Integer.

Dim i As Integer

4. Add a For Next loop.

For i = 1 To 5

Next i

5. The Date function returns the current date without the time. Add the following code line to the loop, to highlight all the cells containing the current date (12/22/2013).

If Cells(i, 1).Value = Date Then Cells(i, 1).Font.Color = vbRed

Result:

6. Add the following code line to the loop, to highlight all the dates earlier than 04/19/2011.

If Cells(i, 1).Value < DateValue(“April 19, 2011”) Then Cells(i, 1).Font.Color = vbRed

Result:

7. But what about times, we hear you say. They are the decimals. Switch back to General format and change the numbers to decimal numbers.

8. Now change the format to ‘Date and Time’ format.

Result:

9. If you want to highlight all cells containing the current date, we cannot use the code line at 5 anymore. Why not? Because the numbers in column A are decimal numbers now. Comparing it with Date (a whole number) would not give any match. (It would only give a match with 12/22/2013 at midnight exactly!) The following code line does work:

If Int(Cells(i, 1).Value) = Date Then Cells(i, 1).Font.Color = vbRed

Explanation: we simply use the Int function. The Int function rounds a number down to the nearest integer. This way we can get the dates without the times and compare these dates with Date.

Result:

10. Add the following code line to highlight all the cells containing times in the morning.

If (Cells(i, 1).Value – Int(Cells(i, 1).Value)) < 0.5 Then Cells(i, 1).Font.Color = vbRed

Explanation: we only need the decimals so therefore we subtract the integer part. Noon (halfway through the day) is represented as 0.5. Decimals lower than 0.5 are the times in the morning.

Result:

DateDiff Function

The DateDiff function in Excel VBA can be used to get the number of days between two dates.

Place a command button on your worksheet and add the following code lines:

Dim firstDate As Date, secondDate As Date, n As Integer

firstDate = DateValue(“Jun 19, 2010”)
secondDate = DateValue(“Jul 25, 2010”)

n = DateDiff(“d”, firstDate, secondDate)

MsgBox n

Explanation: first, we declare two dates. Next, we initialize the two dates using the DateValue function. The DateDiff function has three arguments. Fill in “d” for the first argument since we want the number of days between two dates. Finally, we use a MsgBox to display the number of days between the two dates.

Result when you click the command button on the sheet:

Change “d” to “ww” to get the number of weeks between two dates. Place your cursor on DateDiff in the Visual Basic Editor and click F1 for help on the other interval specifiers.

Weekdays

Below we will look at a program in Excel VBA that calculates the number of weekdays between two dates.

Weekdays are: Monday, Tuesday, Wednesday, Thursday and Friday.

Situation:

Note: Dates are in US Format. Months first, Days Second. This type of format depends on your windows regional settings.

1. First, we declare five variables. date1 of type Date, date2 of type Date, dateToCheck of type Date, daysBetween of type Integer, weekdays of type Integer and i of type Integer.

Dim date1 As Date, date2 As Date, dateToCheck As Date
Dim daysBetween As Integer, weekdays As Integer, i As Integer

2. We initialize four variables. We use the DateDiff function to initialize the variable daysBetween. This function has three arguments. We fill in “d” for the first argument since we want the number of days between date1 and date2.

weekdays = 0
date1 = Range(“B2”)
date2 = Range(“B3”)
daysBetween = DateDiff(“d”, date1, date2)

3. We need to check for each date between date1 and date2 (including date1 and date2) whether the date is a weekday or not. If yes, we increment weekdays by 1. We will use a For Next loop.

For i = 0 To daysBetween

4. We use the DateAdd function to get each date we need to check. This function has three arguments. We fill in “d” for the first argument since we want to add days, i for the second argument, and date1 for the third argument since we want to add i days to date1. This way Excel VBA can check each date between date1 and date2 starting with date1. Add the following code line:

dateToCheck = DateAdd(“d”, i, date1)

Example: for i = 3, Excel VBA checks date1 + 3 days.

5. Next, we use the Weekday function (build in function) to check whether dateToCheck is a weekday or not. The Weekday function returns 1 for a Sunday and 7 for a Saturday. Therefore, we only increment the variable weekdays if Weekday(dateToCheck) is not equal to 1 and not equal to 7 (<> means not equal to). The following code lines get the job done.

If (Weekday(dateToCheck) <> 1 And Weekday(dateToCheck) <> 7) Then
weekdays = weekdays + 1
End If

6. Don’t forget to close the loop.

Next i

7. Finally, we display the number of weekdays using a MsgBox. We use the & operator to concatenate (join) two strings. Although weekdays is not a string it works here.

MsgBox weekdays & ” weekdays between these two dates”

8. Place your macro in a command button and test it.

Result:

Delay a Macro

To execute a sub in Excel VBA after a specific time, use onTime, Now and the TimeValue function. First, place a sub into a module.

1. Open the Visual Basic Editor and click Insert, Module.

2. Add the following sub with name reminder:

Sub reminder()

MsgBox “Don’t forget your meeting at 14.30”

End Sub

We want Excel VBA to execute this sub 5 seconds after a command button is clicked.

2. Place a command button on your worksheet and add the following code line:

Application.OnTime Now() + TimeValue(“00:00:05”), “reminder”

3. Click the command button on the worksheet.

Result 5 seconds later:

4. If you want to execute this sub at a specific time, simply use the following code line:

Application.OnTime TimeValue(“14:00:00 am”), “reminder”
5. Click the command button on the worksheet and wait until 14:00 AM to see your sub being executed.

Note: you will probably not use Excel VBA to remind yourself of an important meeting, but if you do want, it’s best to put a macro like this in a Workbook Open Event. By doing this, you will automatically receive a reminder at 14:00 AM (you don’t have to click a command button to activate the macro). Of course, you will only receive the reminder when you leave your workbook open.

Year Occurrences

Below we will look at a program in Excel VBA that counts the number of year occurrences.

Situation:

Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings.

1. First, we declare three variables of type Integer. One variable we call yearCount, one variable we call yearAsk, and one variable we call i.

Dim yearCount As Integer, yearAsk As Integer, i As Integer

2. We initialize yearCount with the value 0 and yearAsk with the value of cell C4.

yearCount = 0
yearAsk = Range(“C4”).Value

3. We start a For Next loop.

For i = 1 To 16

4. We now check each date and only if the year of the date equals the entered year in cell C4, we increment yearCount by 1. We use the Year function to get the year of a date.

If year(Cells(i, 1).Value) = yearAsk Then
yearCount = yearCount + 1
End If

5. Don’t forget to close the loop.

Next i

6. Finally, we display the total year occurrences. We use the & operator to concatenate (join) two strings.

MsgBox yearCount & ” occurrences in year ” & yearAsk

7. Place your macro in a command button and test it.

Result:

Note: because we made yearAsk variable, you can simply count the number of year occurrences of another year by entering another year in cell C4, and clicking on the command button again.

Tasks on Schedule

Below we will look at a program in Excel VBA that sets the background color of tasks that are on schedule to green, and sets the background color of tasks that are behind schedule to red.

Situation:

On Sheet1 we have three tasks (X, Y, Z). A “1” indicates that a task has been completed. A task is on schedule if a “1” exist in every column up to and including today’s date. Today it’s 6-jun. Tasks X and Y are on schedule. Task Z is behind schedule (no “1” in cell E8).

Create a Worksheet Change Event. Code added to the Worksheet Change Event will be executed by Excel VBA when you change a cell on a worksheet.

1. Open the Visual Basic Editor.

2. Double click on Sheet1 (Sheet1) in the Project Explorer.

3. Choose Worksheet from the left drop-down list. Choose Change from the right drop-down list.

Add the following code lines to the Worksheet Change Event:

4. Declare two variables of type Integer.

Dim i As Integer, j As Integer

5. Add a Do While Loop.

Do While Cells(6 + i, 1).Value <> “”

i = i + 1
Loop

Explanation: For i = 0, Excel VBA checks task X, for i = 1, task Y, etc. Excel VBA leaves the Do While loop when Cells(6 + i, 1).Value is empty (no more tasks to check).

Add the following code lines (at 6, 7 and 8) to the Do While Loop.

6. Initialize the variable j with value 0.

j = 0

7. Set the background color of a task to green assuming that a task is on schedule (this is not necessarily true of course).

Cells(6 + i, 1).Interior.ColorIndex = 4

8. Add another Do While Loop.

Do While Cells(4, 2 + j).Value <= Date
If Cells(6 + i, 2 + j).Value = 0 Then Cells(6 + i, 1).Interior.ColorIndex = 3
j = j + 1
Loop

Note: the Date function returns today’s date.

Explanation: For i = 0, j = 0, Excel VBA checks cell B6. For i = 0, j = 1, Excel VBA checks cell C6, etc. A task is on schedule if a “1” exists in every column up to and including today’s date. If Excel VBA finds a “0”, it sets the background color of the task to red.

9. Test the program. For example, indicate that task Z on 6-jun has been completed. As a result, task Z will automatically turn green.

Sort Birthdays

Below we will look at a program in Excel VBA that sorts birthdays to months first and days second (so we ignore years). We want the birthday with the lowest month number at the first position. If there are birthdays with equal month numbers, we want the birthday with the lowest day number first. Are you ready?

Situation:

Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings.

1. First, we declare eight variables. One Date variable we call tempDate, one String variable we call tempName. The other six variables are Integer variables with names monthToCheck, dayToCheck, monthNext, dayNext, i and j.

Dim tempDate As Date, tempName As String
Dim monthToCheck As Integer, dayToCheck As Integer, monthNext As Integer, dayNext As Integer, i As Integer, j As Integer

2. We start two For Next loops.

For i = 2 To 13
For j = i + 1 To 13

Example: for i = 2, j = 3, 4, … , 12 and 13 are checked.

3. We initialize four Integer variables. We use the Month function to get the month of a date and the Day function to get the day of a date.

monthToCheck = month(Cells(i, 2).Value)
dayToCheck = day(Cells(i, 2).Value)

monthNext = month(Cells(j, 2).Value)
dayNext = day(Cells(j, 2).Value)

For example: at the start, for i = 2; the date of Bregje, and j = i + 1 = 2 + 1 = 3; the date of Niels will be chosen.

4. To sort the dates properly, we compare the first date (monthToCheck and dayToCheck) with the next date (monthNext and dayNext). If the next date is ‘lower’, we swap the dates and names. Add the following If Then statement.

If (monthNext < monthToCheck) Or (monthNext = monthToCheck And dayNext < dayToCheck) Then

End If

If the above statement is true, we swap the dates and names.

For example: for i = 2 and j = 3, the date of Bregje and Niels are checked. MonthNext = 6, monthToCheck = 2. The above statement is not true since monthNext is higher than monthToCheck. Excel VBA increments j by 1 and repeats the code lines for i = 2 and j = 4. You can easily see that Joost (j = 4) has a higher month number than Bregje, so we go to the next one. We get the same result for j = 5 and j = 6. When we arrive at j = 7, we have the following variables: monthNext = 2 and dayNext = 9. MonthToCheck = 2 and dayToCheck = 12. Now the above statement is true since monthNext = monthToCheck and dayNext (9) is lower than dayToCheck (12).

5. We swap the dates. We temporarily store one date to tempDate, so that Excel VBA can swap the dates properly. Add the following code lines in the If statement.

‘swap dates
tempDate = Cells(i, 2).Value
Cells(i, 2).Value = Cells(j, 2).Value
Cells(j, 2).Value = tempDate

6. We do the same with the names. Add the following code lines in the If statement.

‘swap names
tempName = Cells(i, 1).Value
Cells(i, 1).Value = Cells(j, 1).Value
Cells(j, 1).Value = tempName

7. We close the second For Next loop (Outside the If statement).

Next j

For i = 2 and j = 7, Excel VBA swapped the dates and names. That means we get Richard at the first position and Bregje at position 7. That also means we get a new monthToCheck and dayToCheck at the start of our next iteration (for i = 2 and j = 8). We will now compare Richard with Dineke (j = 8). You can easily see that there is no need to replace those dates and names because Richard has a ‘lower’ date. As a matter of fact, there is no need to replace Richard (i = 2) with Jan (j = 9), Wendy (j = 10), Jeroen (j = 11), John ( j= 12) and Debby (j = 13). That is because Richard has the ‘lowest’ date. This way Excel VBA will get (for i = 2) the ‘lowest’ date at the first position. To get the second ‘lowest’ date at the second position, Excel VBA repeats the exact same steps for i = 3. To get the third ‘lowest’ date at the third position, Excel VBA repeats the exact same steps for i = 4, etc.

8. Close the first For Next loop (Outside the If statement).

Next i

9. Test your program.

Result:

Leave a Reply

Your email address will not be published.