How to Date Is Same Month And Year in Excel

Why This Task Matters in Excel

In every organization, dates drive reporting, compliance, and decision-making. Finance teams close their books by calendar month, sales managers compare performance against the same month last year, and project leaders monitor tasks due within the current billing cycle. All these activities require a quick answer to a seemingly simple question: do these two dates belong to the same calendar month and year?

Imagine a retail analyst evaluating promotional campaigns. She receives two date columns: “Order Date” and “Promo Start Date.” To attribute revenue correctly, she must flag orders that occurred in the exact month a promotion started. If she only matches on year, December orders could be paired with a January promotion; if she only checks the day, January thirty-first might incorrectly match February first. Precise month-and-year matching eliminates these costly errors.

In supply-chain planning, vendors often provide expected shipment dates, while manufacturers log actual receipt dates. Auditors want to know whether shipping and receipt occurred in the same accounting period. A single formula that validates “same month and year” can instantly highlight discrepancies without manually filtering by twenty-four different month-year combinations.

Human-resources departments use a similar check when calculating benefits or tenure. Suppose an employee’s “Hire Date” and “Conversion to Full Time Date” must fall in the same month to satisfy policy. Automating the comparison prevents payroll issues that might arise from misaligned dates.

Excel excels (pun intended) at this task because it stores dates as serial numbers—integers for whole days and decimals for fractions of a day—while offering dozens of date functions that slice and dice those serials with surgical precision. By mastering month-and-year comparisons, you reinforce broader spreadsheet skills: extracting date parts, using logical operators, employing array formulas, and scaling calculations to thousands of rows without performance hits. Neglecting this knowledge forces you into fragile manual filters, increases the risk of misclassification, and slows every downstream process that depends on accurate period matching.

Best Excel Approach

The most reliable way to determine whether two dates lie in the same month and year is to compare their month component and their year component simultaneously. While you could concatenate MONTH and YEAR or convert both dates to the first day of their month, the simplest, most transparent method is to turn each date into a text string formatted as “yyyymm” and then test for equality.

Because Excel’s TEXT function respects the underlying date serial but outputs a normalized six-character code, “202401” unequivocally represents January 2024 regardless of the input’s day value or time stamp. Converting both dates to this code and asking whether they match returns TRUE when they do and FALSE when they do not.

Recommended syntax:

=TEXT(A2,"yyyymm") = TEXT(B2,"yyyymm")

If you need a numeric 1 or 0 for further math, wrap the comparison in a double unary operator (--) or multiply by 1:

=--(TEXT(A2,"yyyymm") = TEXT(B2,"yyyymm"))

When to use this approach

• Any time you have standard Excel date values (not text)
• When you prefer a single, easily auditable formula
• When regional settings might display dates differently yet underlying serials remain consistent

Prerequisites

• Columns A and B (or whichever you choose) must contain valid Excel date serials or datetimes.
• No special add-ins are necessary; TEXT is available in all modern Excel versions and in Excel for the web, Mac, and Microsoft 365.

Logic overview

1. TEXT converts each date to a six-digit string.
2. The equality operator (=) checks if the strings match character for character.
3. The result becomes a Boolean TRUE or FALSE (or 1 and 0 when forced numeric).

Parameters and Inputs

Input columns can hold dates, datetimes, or formulas that evaluate to dates. Excel stores all of these as serial numbers such as 45230 for 30-Apr-2024. TEXT only needs the value; it ignores cell formatting.

Required inputs

• Date1 – any cell or expression that resolves to a valid date serial.
• Date2 – same as Date1.

Optional inputs and variations

• Custom month-year format – You might choose \"mm-yyyy\" or \"mmm-yyyy\" for readability in helper columns, but “yyyymm” is ideal for lexical comparisons.
• Return type – Use the Boolean result directly in filtering or wrap with IF to output custom labels such as \"Same Period\" or \"Different Period.\"
• Array ranges – In dynamic array versions of Excel, comparing two whole columns like [A2:A1000] = [B2:B1000] spills down automatically.

Data preparation

• Ensure there are no blank cells unless you intentionally want blank-to-blank comparisons to return TRUE.
• Validate imported CSVs to convert text dates to real date serials via DATEVALUE or Power Query.

Edge cases

• Non-date inputs generate a #VALUE! error. Add a nested IFERROR to handle gracefully.
• Time stamps at midnight versus 11:59 p.m. do not affect the month-year code, so they are safe.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose you have two columns: “Invoice Date” in A2:A7 and “Payment Date” in B2:B7. You want to know if each invoice was paid in the same month it was issued.

Sample data
(A2) 12-Jan-2024
(A3) 28-Jan-2024
(A4) 05-Feb-2024
(A5) 31-Mar-2024
(A6) 15-Apr-2024
(A7) 30-Apr-2024

(B2) 20-Jan-2024
(B3) 03-Feb-2024
(B4) 28-Feb-2024
(B5) 15-Mar-2024
(B6) 02-May-2024
(B7) 30-Apr-2024

Step-by-step

1. Click cell C2 and enter:

=TEXT(A2,"yyyymm") = TEXT(B2,"yyyymm")

2. Press Enter. Excel returns TRUE because both dates fall in January 2024.
3. Autofill down through C7. Your results: TRUE, FALSE, TRUE, TRUE, FALSE, TRUE.
4. Optionally convert to numeric by wrapping with double unary:

=--(TEXT(A2,"yyyymm")=TEXT(B2,"yyyymm"))

Now TRUE becomes 1 and FALSE becomes 0—handy for SUM or AVERAGE.

Why it works
TEXT strips the day from each date while preserving month and year. The equality operator then has a simple string comparison instead of complex date logic.

Variations

• Replace TEXT with YEAR=YEAR and MONTH=MONTH combination if you prefer two conditions.
• Use an IF wrapper: `=IF(`TEXT(A2,\"yyyymm\")`=TEXT(`B2,\"yyyymm\"),\"On time\",\"Delayed\").

Troubleshooting

• If the formula returns #VALUE!, check for text dates. Convert them with DATEVALUE or multiply by 1 to coerce to a serial.
• If you see FALSE where you expect TRUE, confirm that regional settings have not misinterpreted day and month in your source data.

Example 2: Real-World Application

A manufacturing firm stores “Planned Completion Date” and “Actual Completion Date” for thousands of work orders. Management requires a Key Performance Indicator (KPI): percentage of jobs completed in the same accounting period. Accounting periods align with calendar months. You want a single cell displaying that KPI while preserving row-level detail for drill-down.

Data setup

• Sheet “WorkOrders” contains [A2:A10000] PlannedDate and [B2:B10000] ActualDate.
• Column C will store the comparison flag.
• Cell F2 will hold the overall KPI.

Walkthrough

1. In C2 enter the Boolean formula using dynamic arrays (available in Microsoft 365):

=TEXT(A2:A10000,"yyyymm") = TEXT(B2:B10000,"yyyymm")

Because modern Excel spills the results, a single entry populates all necessary rows without drag-fill.
2. In F2 calculate the KPI:

=AVERAGE(--C2#)

The C2# reference captures the entire spill range. The double unary converts TRUE/FALSE to 1/0, and AVERAGE produces the completion-within-period ratio. Format F2 as Percentage with one decimal place.

Business impact
Management can read “82.4 percent of work orders completed within the planned period” in real time. The same Boolean spill range supports conditional formatting to highlight exceptions or pivot tables for deeper analysis.

Integration touches

• Combine with FILTER to isolate late jobs: `=FILTER(`A2:C10000, C2#=FALSE).
• Use Power Query to append new data monthly and refresh formulas automatically.

Performance considerations
TEXT operates on roughly ten thousand rows instantly. Even hundreds of thousands remain responsive because the function is lightweight. Avoid volatile functions like TODAY inside the comparison; they recalculate with every worksheet change and slow performance.

Example 3: Advanced Technique

Scenario: A global corporation consolidates data from subsidiaries across different fiscal calendars. Some regions operate on a 4-4-5 calendar where months do not align with calendar months. You still need “same fiscal period” matching. The start date of each fiscal month varies by region and year.

Advanced approach

1. A helper table lists fiscal month start dates in Sheet \"FiscalCalendar\" with columns [StartDate], [FiscalYear], [FiscalMonth].
2. For each transaction date, you require a lookup that converts the Gregorian date to a unique fiscal period code.
3. Build a lambda-enabled named function (Excel 365) called MapToPeriod:

= LAMBDA(d,
    LET(
        tbl, FiscalCalendar!A2:C400,
        rowMatch, XMATCH(TRUE, d >= INDEX(tbl,,1), 0, -1),
        FY, INDEX(tbl, rowMatch, 2),
        FM, INDEX(tbl, rowMatch, 3),
        TEXT(FY,"0000") & TEXT(FM,"00")
    )
)

4. Now compare two dates in row 2 for same fiscal period:

=MapToPeriod(A2) = MapToPeriod(B2)

5. Wrap with IFERROR in case a date precedes your earliest calendar entry.

Performance optimization

• XMATCH with binary search (-1 argument) scales logarithmically, making lookups fast over large tables.
• The lambda is calculated once per unique date if you deploy Office365’s by-row caching.

Error handling

• MapToPeriod returns #N/A when a date falls outside the calendar. Trap with IFERROR and decide whether to treat as “different period” or flag for data cleanup.

Professional tips

• Store period codes (“202404” for fiscal year 2024, period 4) as text to avoid leading-zero loss.
• Document the lambda in the Name Manager so other users can audit the logic.

When to use advanced method versus basic

• When fiscal periods diverge from calendar months
• When period definitions change annually and are stored in a table rather than a deterministic formula

Tips and Best Practices

1. Use helper columns: Even if you embed comparison logic inline, a visible “PeriodCode” column aids debugging and pivot grouping.
2. Normalize date inputs during import: Power Query’s “Change Type” step guarantees that your comparisons operate on genuine dates.
3. Prefer “yyyymm” over “mm-yyyy” for sorting and equality tests; lexical order equals chronological order.
4. Convert Booleans to numbers only when mathematical aggregation is required. Avoid unnecessary double unary for readability.
5. Combine with conditional formatting: A red fill for FALSE values in the comparison column surfaces exceptions instantly.
6. Document your assumptions: If your fiscal calendar shifts mid-year, keep a comment or instruction box explaining the lookup source table.

Common Mistakes to Avoid

1. Comparing text-formatted dates: If cells look like 2024-01-31 but are actually text, TEXT(A2,\"yyyymm\") returns the literal string, not a recalculated code, causing deceptive matches. Fix by wrapping original cells in DATEVALUE or using Data ➜ Text to Columns.
2. Using MONTH and YEAR separately without parenthesis: Writing `=MONTH(`A2)`=MONTH(`B2)`=AND(`YEAR(...)) mistakenly compares MONTH twice due to operator precedence. Use the AND function or nest comparisons correctly.
3. Omitting absolute references in helper columns: If you place \"yyyymm\" conversions in a helper row and copy formulas horizontally without locking references, they may shift and compare wrong cells. Lock with $ where needed.
4. Ignoring blank cells: A blank compared with another blank yields TRUE but probably should be excluded. Add an ISBLANK test to avoid inflating your match rate.
5. Hard-coding today’s date in volatile functions: Embedding TODAY recalculates constantly and slows massive workbooks. If you must use today’s value, paste it as a static timestamp or place it in one administrative cell referenced by formulas.

Alternative Methods

Many roads lead to the same result. Choosing the right one depends on audience familiarity, performance requirements, and version support.

When to choose alternatives

• If you need backward compatibility with Lotus functions, choose AND.
• If you want to minimize string operations on very large datasets, EOMONTH performs fewer allocations.
• If you require a single numeric field for database export, concatenate YEAR and MONTH with leading zeros.

FAQ

When should I use this approach?

Use month-and-year matching whenever your metric or policy is defined at “monthly” granularity: monthly sales reporting, budget vs actual reconciliations, same-month service-level agreements, or benefit eligibility tied to calendar periods.

Can this work across multiple sheets?

Yes. Refer to other sheets directly: `=TEXT(`Sheet1!A2,\"yyyymm\") = TEXT(Sheet2!B2,\"yyyymm\"). For mass comparison, align both ranges in the same shape or use INDEX/MATCH to fetch the corresponding date before testing.

What are the limitations?

The TEXT approach relies on valid date serials. It will not automatically detect dates stored as text. Also, it assumes calendar months unless you implement a fiscal calendar lookup as shown in the advanced example.

How do I handle errors?

Wrap formulas with IFERROR to capture #VALUE! or #N/A: `=IFERROR(`TEXT(A2,\"yyyymm\")`=TEXT(`B2,\"yyyymm\"),\"Invalid Date\"). Alternatively, use ISNUMBER(A2)*ISNUMBER(B2) to pre-validate inputs.

Does this work in older Excel versions?

Yes. TEXT, YEAR, MONTH, and EOMONTH exist as far back as Excel 2007. Dynamic array spilling and LAMBDA functions require Microsoft 365 or Excel 2021, but you can still fill formulas manually in legacy versions.

What about performance with large datasets?

On a modern computer, TEXT comparisons on half a million rows execute in under a second. For multi-million rows, consider loading data into Power Query or Power Pivot and writing a DAX measure: `=IF(`YEAR([Date1])`=YEAR(`[Date2]) && MONTH([Date1])`=MONTH(`[Date2]),1,0). This pushes calculation to the VertiPaq engine for better compression and speed.

Conclusion

Determining whether two dates fall in the same month and year is a deceptively simple requirement that unlocks powerful insights across finance, operations, and analytics. By mastering techniques such as TEXT-based month-year codes, EOMONTH comparisons, and even custom fiscal mappings, you can automate reconciliations, enforce policy compliance, and streamline reporting pipelines. Every time you transform raw dates into actionable period flags, you cement a foundational Excel skill that underpins larger workflows like pivot reporting, dashboard KPIs, and database exports. Continue experimenting with alternative methods, integrate your formulas with Power Query or Power Pivot, and you will be well on your way to becoming the go-to Excel expert in your organization.