ThisCalculator

Everyday

Get Last Entry by Month and Year in Excel

Updated · Standard formula, shown below

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 article explains how to find the last entry (latest date) for a specific month and year in a list of dates, such as the last sale in March 2024 or the final log entry for December. You’ll learn formulas that work in Excel and Google Sheets, and you’ll see how to handle blank cells, unsorted data, and text-formatted dates. The main formula uses MAX with IF or MAXIFS to return the most recent date that falls within the chosen month.

Formula

The core formula is an array formula that checks every date in your list and keeps only those that match the given month and year, then returns the maximum (largest) of those dates.

In symbols: Last = MAX( IF( YEAR(dates)=y AND MONTH(dates)=m, dates ) )

  • dates – the range of cells containing dates (e.g., A2:A100).
  • y – the year you want, as a number (e.g., 2024).
  • m – the month you want, as a number 1–12 (1=January, 12=December).
  • MAX – returns the largest date, which is the most recent.

In Excel, if you are using a version without dynamic arrays, you must enter this as an array formula by pressing Ctrl+Shift+Enter instead of just Enter. In Excel 365 and Google Sheets, it works with a normal Enter because they handle arrays automatically.

Worked Example

Suppose your date list is in cells A2:A10 and contains these dates:

  • 2024-01-15
  • 2024-03-10
  • 2024-03-25
  • 2024-03-18
  • 2024-04-02

You want the last entry for March 2024 (month=3, year=2024). The formula checks each date:

  • 2024-01-15 – month 1, not 3 → ignore
  • 2024-03-10 – month 3, year 2024 → include
  • 2024-03-25 – month 3, year 2024 → include
  • 2024-03-18 – month 3, year 2024 → include
  • 2024-04-02 – month 4, not 3 → ignore

Now take the maximum of the included dates: MAX(2024-03-10, 2024-03-25, 2024-03-18) = 2024-03-25. That is your last entry for March 2024.

When to Use This

You need this whenever you have a list of dated records and you want the most recent one for a particular month. Common situations:

  • Finding the last sale in a given month from a sales log.
  • Getting the most recent transaction for a customer in a specific month.
  • Finding the latest entry in a project log for a monthly report.
  • Checking the last time an action occurred in a given month.

It works for any date range, as long as your data is stored as real dates, not text.

How to Read the Result

The result is a date. If you see a number like 45376, that’s because the cell is formatted as a number. Change the cell format to a date format (e.g., DD-MMM-YYYY) to see it as a date. If the result is 0 or shows 00-Jan-1900, that means no entry exists for that month and year – the formula found nothing to match.

If you want to return a blank instead of 0 when there’s no match, wrap the formula in an IF: =IFERROR(MAX(IF(...)),"") or use =IF(COUNTIFS(...)=0,"",MAX(...)).

Common Mistakes and Fixes

  • Dates stored as text – If your dates are text like “2024-03-25” instead of real dates, the formula won’t work. Convert them using DATEVALUE or the Text to Columns wizard.
  • Forgetting Ctrl+Shift+Enter – In older Excel, if you enter the formula normally, it may return #VALUE! or a wrong result. Use Ctrl+Shift+Enter to make it an array formula.
  • Using AND inside an array – Don’t write IF(AND(YEAR(dates)=y,MONTH(dates)=m),...) because AND does not work element-by-element in arrays. Use nested IFs or multiplication as shown above.
  • Incorrect month number – Ensure your month is 1–12, not 0–11. January is 1, not 0.
  • Whole column references – Using A:A can slow down the formula. Limit the range to your actual data, e.g., A2:A1000.

Alternatives and Variations

If you have Excel 2019 or later, you can use the MAXIFS function, which is simpler and doesn’t require array entry:

=MAXIFS(dates, dates, ">="&DATE(y,m,1), dates, "<"&DATE(y,m+1,1))

This finds the maximum date that is on or after the first day of the month and before the first day of the next month.

If your data is sorted by date, you can also use LOOKUP or XLOOKUP, but MAXIFS is the most robust for unsorted data.

For Google Sheets, the same MAXIFS syntax works, and the array formula also works without special entry.

Frequently Asked Questions

Why does my formula return 0 when there is no match?

Because MAX of an empty array returns 0. To show a blank, wrap the formula in IFERROR or check with COUNTIFS first. For example: =IF(COUNTIFS(dates,">="&DATE(y,m,1),dates,"<"&DATE(y,m+1,1))=0,"",MAXIFS(...))

Can I get the last entry for the current month automatically?

Yes, replace the month and year with MONTH(TODAY()) and YEAR(TODAY()). For example: =MAXIFS(dates,dates,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),dates,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))

What if my dates have times attached?

The formula still works because a date with a time is a larger number than the date alone. If you want to ignore times and consider only the date, wrap each date with INT() to strip the time: =MAX(IF(YEAR(INT(dates))=y,IF(MONTH(INT(dates))=m,INT(dates))))

How do I get the last entry for each month in a whole year?

You can use a pivot table, or create a helper column that shows the month-year (e.g., =TEXT(A2,"YYYY-MM")) and then use MAXIFS with that helper column as the criteria range. Alternatively, use a formula that lists each month and finds the last date for that month.

Does this work in Google Sheets?

Yes, the array formula works without Ctrl+Shift+Enter, and MAXIFS is also available. The syntax is identical.

Why does my formula return #VALUE!?

This usually happens when you didn’t enter it as an array formula in an older Excel version, or when your date range contains text or errors. Check that all cells in the range contain real dates, and press Ctrl+Shift+Enter after typing the formula.

Related

Formula used
in d | Date list (column A) | - | 45000 | 0
in m | Month (1-12) | - | 3 | 1
in y | Year (e.g., 2024) | - | 2024 | 2000
out last | Last entry date | - | MAX(IF(YEAR(d)=y,IF(MONTH(d)=m,d))) | 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.