How to Compare Dates to Today with Excel VBA (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Typical Excel datasets contain hundreds of rows and tens of columns. As a result, users often use Excel VBA to compare dates to today to highlight or filter out data. Versions of the VBA FOR loop entwined with VBA IF and VBA custom function can be used to compare dates against the current date.

Let’s say we have a sales dataset containing the amounts of sales of each date. We want to compare the dates to the current date using Excel VBA.

Excel VBA Compare Dates to Today-Dataset

In this article, we demonstrate multiple macro variants using VBA FOR and VBA IF functions to compare dates to today.


How to Compare Dates to Today with Excel VBA: 3 Easy Ways

Excel macros require Module insertion prior to their execution. Follow the below instructions to be able to insert a Module:

➤ Press ALT+F11 simultaneously to open the Microsoft Visual Basic window.

➤ Go to Insert > Module. Afterward, Excel opens a VBA Module where users can insert their macros.

Inserting Module

Go through the latter section to find out the appropriate macro variants suitable for your case.


Method 1: Highlighting Cells to Compare Dates to Today Using Excel VBA

From the hundred rows, users can highlight the current date and also the days before or after the current date using Excel VBA. Excel’s VBA FOR and VBA IF functions enable users to do so.

Step 1: Type the following macro in the inserted Module.

Sub Hightlight_Dates_Compare_Today()
Dim m As Integer
For m = 4 To 15
If Cells(m, 2).Value = Date Then Cells(m, 2).Font.Color = vbRed
Next m
End Sub

Highlighting Cells to Compare Dates to Today Using Excel VBA

Macro Explanation

  • Firstly, the macro calls the Sub using Sub Highlight_Dates_Compare_Today.
Sub Hightlight_Dates_Compare_Today()
  • Secondly, the macro defines the variable types as Integer.
Dim m As Integer
  • Thirdly, macro executes a VBA FOR
  • Then the VBA IF function iterates the cells to check the imposed condition using the VBA Date. The VBA DATE function returns the current date.
  • .Font.Color command colors the cells that match the current date.
  • Users can modify the macro to highlight the days before or after the current date.

Before Days

If Cells(m, 2).Value < Date Then Cells(m, 2).Font.Color = vbRed

After Days

If Cells(m, 2).Value > Date Then Cells(m, 2).Font.Color = vbRed
			

Step 2: After inserting the macro into the module, use the F5 key or go to Run > Run Macro F5 to execute it. Then return to the worksheet to check whether the macro highlights the matched cell or not, as shown in the latter image.


Method 2: Coloring Rows to Compare Dates with Excel VBA

As an alternative to Method 1, Excel Macros can color the entire rows upon comparing them to a specific column value.

Step 1: Type the below macro in the module.

Sub Coloring_Dates_Against_Today()
Dim mLastRow As Long, mRow As Long
Dim mColumn As String
mColumn = "B"
 With ActiveSheet
 mLastRow = .Cells(.Rows.Count, mColumn).End(xlUp).Row
  For mRow = 4 To mLastRow
    If IsDate(.Cells(mRow, mColumn).Value) And .Cells(mRow, mColumn).Value = Date Then
     .Rows(mRow).Interior.ColorIndex = 6
    End If
  Next mRow
 End With
End Sub

Coloring Rows to Compare Dates with Excel VBA

Macro Explanation

  • At first, the macro calls the Sub using Sub Coloring_Dates_Against_Today.   
Sub Coloring_Dates_Against_Today()
  • Then the macro declares the variables as Long and String.
Dim mLastRow As Long, mRow As Long
Dim mColumn As String
  • Also, set the mColumn variable to B Column.
mColumn = "B"
  • VBA WITH statement performs VBA FOR and VBA IF functions on a single object.
  • VBA FOR assigns the row numbers and VBA IF executes the imposed conditions.
  • .Rows.Interior.ColoeIndex colors the matched rows with the assigned color index (i.e., 6 (Yellow)).
  • Users can modify the macro to highlight the days before or after the current date.

Before Days

If IsDate(.Cells(mRow, mColumn).Value) And .Cells(mRow, mColumn).Value < Date

After Days

If IsDate(.Cells(mRow, mColumn).Value) And .Cells(mRow, mColumn).Value > Date
			

Step 2: Run the macro, then go back to the worksheet. You see, Excel colors the entire rows.


Method 3: Using VBA Custom Function to Compare Dates to Today

What if users want to know the dates’ status that fall within the current month and year? A custom function can display TRUE or FALSE to indicate the dates match the current month and year’s status in the adjacent cells.

The TODAY() function returns today’s date in the C3 cell.

the TODAY Function

Step 1: Type the following macro in the module.

Function MnthYr(mDate As Date) As Boolean
MnthYr = False
If Not IsDate(Cells(3, 3)) Then Exit Function
If Month(mDate) = Month(Cells(3, 3)) And Year(mDate) = Year(Cells(3, 3)) Then
MnthYr = True
End If
End Function

Using VBA Custom Function

Macro Explanation

  • At first, the macro named the function as MnthYr. And assign cell references to Date.      
Function MnthYr(mDate As Date) As Boolean
  • Initially, the function gets assigned to False.
MnthYr = False
  • The VBA IF function fixes the C3 cell as the current date and extracts the month and year using the VBA Month and Year functions respectively.
  • The VBA AND function combines both conditions into one.
If Month(mDate) = Month(Cells(3, 3)) And Year(mDate) = Year(Cells(3, 3))
  • The macro returns True upon matching the month and year with the current date.
  • Users can modify the macro to highlight the previous or following month and year of the current date.

Previous Months and Years

If Month(mDate) < Month(Cells(3, 3)) And Year(mDate) < Year(Cells(3, 3))

Following Months and Years

If Month(mDate) > Month(Cells(3, 3)) And Year(mDate) > Year(Cells(3, 3))
			

Step 2: After inserting the macro into the module, go to the worksheet. Type the following formula into cell D6.

=MnthYr(B6)

Inserting Formula

Step 3: Drag the Fill Handle to display True or False in the adjacent cells.

🔺 Users can use Conditional Formatting (Home > Conditional Formatting > Highlight Cells Rules > Equal To) to color format function outcomes as depicted below.


Download Excel Workbook


Conclusion

This article demonstrates multiple variants of Macros to Excel VBA comparing dates to today. Go through the Macro Explanations for a better understanding. Use any of the methods to accomplish your goal. Comment if you have further inquiries or have anything to add.


<< Go Back to Dates | Compare | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo