ThisCalculator

Everyday

Excel First & Last Day of Month from Date

Updated · Standard formula, shown below

Excel First & Last Day of Month from Date

To get the first day of the month from a date in Excel, use the formula =EOMONTH(A1,-1)+1; to get the last day, use =EOMONTH(A1,0). These two formulas work for any date in any version of Excel, returning a real date value that you can format however you like.

How EOMONTH Works

EOMONTH is Excel’s “end of month” function. It takes two arguments: a start date and a number of months to shift forward or backward. The result is always the last day of that target month. For example, =EOMONTH(A1,0) gives the last day of the month containing A1, while =EOMONTH(A1,1) gives the last day of the next month, and =EOMONTH(A1,-1) gives the last day of the previous month.

The first-day formula builds on this: take the last day of the previous month and add 1 day. That always lands on the first of the current month, regardless of how many days the previous month had. This is more reliable than using DATE(YEAR(A1),MONTH(A1),1), though that simpler formula also works and is easier to read for some users.

Step-by-Step: Get First Day

  1. Place a valid date in cell A1 (e.g., 2024-03-15).
  2. In cell B1, enter: =EOMONTH(A1,-1)+1
  3. Press Enter. Excel returns a serial number like 45322.
  4. Format cell B1 as a date (Home → Number Format → Short Date).

The result is 2024-03-01. If you want the first day of the next month, change the formula to =EOMONTH(A1,0)+1.

Step-by-Step: Get Last Day

  1. Keep the same date in A1.
  2. In cell C1, enter: =EOMONTH(A1,0)
  3. Press Enter and format as a date.

The result is 2024-03-31. For the last day of the previous month, use =EOMONTH(A1,-1).

Why These Formulas Beat the Alternatives

You might see other approaches online, such as =DATE(YEAR(A1),MONTH(A1),1) for the first day or =DATE(YEAR(A1),MONTH(A1)+1,0) for the last day. Both are valid, but EOMONTH is shorter and less error-prone when you need to shift months. The DATE version requires you to remember that day 0 of a month means the last day of the previous month, which trips up many users.

EOMONTH also handles year boundaries automatically. For a date like 2024-12-15, =EOMONTH(A1,1) returns 2025-01-31 without any extra logic. The DATE version would need =DATE(YEAR(A1),MONTH(A1)+2,0) to get the same result, which is less intuitive.

Common Mistakes and How to Avoid Them

  • Forgetting to format as a date: Excel shows a number like 45322. Apply a date format or the cell will display a serial number.
  • Using text instead of a date: If A1 contains “March 15, 2024” as text, EOMONTH returns a #VALUE! error. Use =DATEVALUE("3/15/2024") to convert it first.
  • Confusing +1 and -1: =EOMONTH(A1,-1)+1 gives the first of the current month. =EOMONTH(A1,0)+1 gives the first of the next month. The +1 after EOMONTH is what shifts from last day to first day.
  • Assuming the last day is always 31: EOMONTH correctly returns 30, 28, or 29 for shorter months.

Using These Formulas in Real Scenarios

These formulas are building blocks for many common tasks:

  • Monthly reporting: Filter records between =EOMONTH(TODAY(),-1)+1 and =EOMONTH(TODAY(),0) to get the current month’s data.
  • Calculating days in a month: =EOMONTH(A1,0)-EOMONTH(A1,-1) gives the number of days in the month.
  • Creating a series of month-end dates: Enter =EOMONTH($A$1,ROW()-1) in a column and drag down to list consecutive month ends.
  • Grouping by month in pivot tables: Add a helper column with the first-day formula, then group by that column instead of the raw date.

You can also combine these with other functions. For example, =TEXT(EOMONTH(A1,0),"mmmm") returns the month name, and =EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)) finds the last Friday of the month.

Frequently Asked Questions

Does EOMONTH work with dates before 1900?

No. Excel’s date system starts on January 1, 1900. For dates before that, EOMONTH returns a #NUM! error. If you work with historical dates, use the DATE function instead, but be aware that Excel will still treat them as text or numbers, not true dates.

What if my date is in a different cell or another sheet?

Just replace A1 with the correct reference, such as =EOMONTH(Sheet2!B3,0). The formula works the same way regardless of where the date lives.

Can I use EOMONTH in Google Sheets?

Yes, Google Sheets has the same EOMONTH function with identical syntax. The formulas in this article work in both Excel and Google Sheets without modification.

Why do I see a number instead of a date?

Excel stores dates as serial numbers. The cell is formatted as General or Number. Select the cell, press Ctrl+1 (Cmd+1 on Mac), and choose a date format under the Number tab.

How do I get the first day of the current month dynamically?

Use =EOMONTH(TODAY(),-1)+1. This updates automatically each time the workbook recalculates, so it always shows the first of the current month.

Is there a way to get the last day of a month without EOMONTH?

Yes, =DATE(YEAR(A1),MONTH(A1)+1,0) works, but it’s longer and less readable. EOMONTH is the standard, recommended approach.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.