First Entry by Month and Year in Excel
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This guide explains how to find the first entry (earliest date or first occurrence) in a dataset grouped by month and year, using Excel formulas and PivotTables. You will learn several methods, see worked examples, and understand which approach works best for different data layouts.
Formula
The simplest way to get the first entry by month and year is to use the MINIFS function (available in Excel 2019 and Microsoft 365). The formula is:
=MINIFS(date_range, month_range, month_value, year_range, year_value)
Where:
date_range– the column containing the dates you want to search.month_range– the column containing the month numbers (1–12) of those dates.month_value– the specific month number you are filtering for (e.g., 1 for January).year_range– the column containing the years of those dates.year_value– the specific year you are filtering for (e.g., 2023).
This returns the earliest date in the dataset that falls in the given month and year. If no dates match, it returns 0.
Worked Example
Suppose your data has dates in column A (A2:A100), and you want the first entry for January 2023. You have helper columns B and C with =MONTH(A2) and =YEAR(A2) filled down.
Enter the month number (1) in cell E1 and the year (2023) in cell E2. Then use:
=MINIFS(A2:A100, B2:B100, E1, C2:C100, E2)
If the earliest date in January 2023 is January 5, 2023, the formula returns that date. The calculator above demonstrates the same logic with a single date input: it returns the first day of the specified month/year, which is the earliest possible date. For a real dataset, you replace that with the MINIFS formula.
When to Use This Method
Use MINIFS when you have a flat table of dates and you need the earliest date for one specific month-year combination, or when you want to copy the formula down a list of month-year pairs. It is the most direct approach and does not require sorting the data.
If you need the first entry for every month-year combination at once, a PivotTable is faster. Put the date field in the Rows area, then group by months and years, and set the value field to Min of date.
Alternative: Array Formula (for older Excel)
If you do not have MINIFS, use an array formula. Enter this with Ctrl+Shift+Enter (in Excel 2016 and earlier):
=MIN(IF((MONTH(date_range)=month_value)*(YEAR(date_range)=year_value), date_range))
This checks each date, keeps those that match the month and year, and returns the minimum. It works, but it is harder to maintain and can slow down large datasets.
Common Mistakes and Pitfalls
- Forgetting to press Ctrl+Shift+Enter for array formulas in older Excel – the formula returns 0 or an error.
- Mixing up month and year columns – double-check the ranges in the formula.
- Dates stored as text – if your dates are not real Excel dates, MONTH and YEAR will fail. Convert text to dates using DATEVALUE or Text to Columns.
- Blank cells in the date range – MINIFS ignores blanks, but array formulas may treat them as 0, giving January 0, 1900 as the result. Use IF to exclude blanks.
How to Read the Result
The result is a date serial number. Format the cell as a date to see it as “Jan 5, 2023”. If you see a number like 44931, just change the number format to Date.
If the result is 0 or January 0, 1900, there is no matching entry – check your month and year values.
Frequently Asked Questions
How do I get the first entry for each month-year combination without writing many formulas?
Use a PivotTable. Drag the date field to Rows, right-click and group by Months and Years, then drag the date field again to Values and change it to Min. This gives a compact list of first dates for every month-year group.
What if my dates include times? Does MINIFS still work?
Yes. Excel dates are numbers with a fractional part for time. MINIFS returns the smallest number, which is the earliest date and time. If you only want the earliest date (ignoring time), use =INT(MINIFS(...)).
Can I use this with a date range that has duplicates?
Yes. MINIFS returns the earliest date, so duplicates do not matter. If you need the first unique entry, you would need a more complex array formula, but for most purposes the earliest date is what you want.
Why does my formula return 0 even though I have matching dates?
Check that the month and year cells contain numbers, not text. Also confirm that the date range is formatted as dates, not as text. If using an array formula, make sure you pressed Ctrl+Shift+Enter.
Is there a way to do this without helper columns?
Yes. In Excel 365, you can use =MINIFS(A:A, MONTH(A:A), 1, YEAR(A:A), 2023) but this requires dynamic arrays and may not work in older versions. For compatibility, helper columns are safer.
What is the difference between MINIFS and a PivotTable?
MINIFS gives you a single answer for one month-year pair. A PivotTable gives you all month-year pairs at once, but requires a few clicks to set up. Choose based on whether you need one result or many.
Related
Formula used
in d | Date (as serial number) | - | 45000 | 1 in m | Month number | - | 1 | 1 in y | Year | - | 2023 | 2000 out first | First date in month/year | date | DATE(y,m,1) | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.