Function Problems

How to Compare Dates Using IF Statements and VBA in Excel

Huma Ashraf Ch Oct 22, 2026 868 views

Question details

The user needs to compare whether a date falls in a specific month relative to another date using Excel formulas and properly insert this formula into an Excel cell using a VBA macro.

How to Compare Dates Using IF Statements and VBA in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating year and month date comparisons using the IF, EDATE, and TEXT functions within a VBA macro script.
Observed behavior: When inserting the formula via VBA, the user encounters a "Compile Error: Expected End of Statement" because the quotation marks inside the formula prematurely terminate the VBA string.

Before you start

Ensure your Excel file is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA code to run, and verify that the target cells contain valid date values rather than plain text.

Solution 1

Use EDATE and TEXT Functions for Direct Excel Date Comparison

Use this method to compare the year and month of two dates directly within an Excel worksheet before translating it to VBA.

Before automating with VBA, it is best practice to ensure your formula works directly in the spreadsheet. The TEXT function combined with EDATE extracts the year and month, bypassing daily variance.

Select the target cell

Click on the cell where you want the comparison result to be displayed.

Enter the formula

Type the formula using IF, TEXT, and EDATE. For example: =IF(TEXT(EDATE(E1,0),"yymm")<TEXT(A1,"yymm"), B1, "").

Apply and test

Press Enter to execute the formula. If it correctly identifies the date relationship, it is ready to be adapted for VBA.

Solution 2Recommended

Properly Escape Quotation Marks in VBA Strings

Use this method to resolve the "Compile Error: Expected End of Statement" when inserting complex Excel formulas containing text format codes via VBA.

Advanced Spreadsheets with WPS Office

Use WPS Spreadsheet for Powerful Formulas and VBA Macros

WPS Spreadsheet offers comprehensive support for advanced Excel functions like IF, EDATE, and TEXT, as well as full VBA/macro compatibility. You can seamlessly write, debug, and run your existing date comparison macros with ease.

1. Download and open WPS Office: Install WPS Office and launch the WPS Spreadsheet application.
2. Open your Macro-Enabled Workbook: Open your existing .xlsm file containing the date data and macros.
3. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on 'VBA Editor' to manage your macros.
4. Run your macros seamlessly: Execute your date comparison macros or input the EDATE/TEXT formulas directly into cells just as you would in Microsoft Excel.

Fully compatible with Microsoft Excel functions and VBA macros (.xlsm formats)Free, lightweight, and fast-loading office suiteBuilt-in developer tools for writing and debugging VBA codeFamiliar user interface ensuring a zero-learning-curve transition

microsoft office alternative - wps office

Frequently Asked Questions

Why does VBA give an "Expected End of Statement" error for my formula?

This happens because VBA uses double quotes to define the beginning and end of strings. If your Excel formula contains double quotes (like "yymm"), VBA interprets the first internal quote as the end of the string. You must type them twice (e.g., ""yymm"") so VBA recognizes them as literal quotation marks.

What does the EDATE function do in Excel?

The EDATE function returns the serial number of a date that is a specified number of months before or after a starting date. Using EDATE(start_date, 0) essentially captures the exact date for accurate month-specific calculations.

How does the TEXT function help in date comparisons?

The TEXT function converts numeric values, including dates, into text strings using specific format codes. By formatting a date with TEXT(date, "yymm"), you extract only the year and month. This enables a precise month-level comparison regardless of what exact day the dates fall on.

Excel & Data Problems