How to get the first and last day of the month
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Snap any date to the start or end of its month — billing cycles, report ranges, month boundaries.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =EOMONTH(A2,-1)+1 | First day. Last day is =EOMONTH(A2,0). Format the result cell as a date. |
| Google Sheets | =EOMONTH(A2,-1)+1 | Identical. |
| LibreOffice Calc | =EOMONTH(A2,-1)+1 | Identical. |
How it works
EOMONTH jumps to a month's last day: offset 0 gives this month's end, and offset -1 gives LAST month's end — add 1 to that and you land on the first day of the current month (July 1 for a July date). So first day = EOMONTH(date,-1)+1 and last day = EOMONTH(date,0). An alternative first-day formula is =DATE(YEAR(A2),MONTH(A2),1), which reads more literally. These two dates are the natural bounds for a month's SUMIFS date range or a report filter.
Verified, not just documented
We ran =TEXT(EOMONTH(DATE(2026,7,21),-1)+1,"YYYY-MM-DD") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 2026-07-01 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 2026-07-01 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
EOMONTH — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to count business days left in the month
- How to count the working days in a month
- How to get the number of days in a month
- How to get the first day of next month
- How to get the last day of the previous month