How to Find the Last Day of the Month in Excel

Open your Excel billing sheet (.xlsx) in Cue Office and use EOMONTH to find the last day of a month. Set the second argument to 0 for the billing month or 1 for the following month. The function uses the last calendar day of the target month, including February 29 in a leap year and January 31 when the next month falls in a new year.

How do you find this month’s and next month’s last day?

Create a new spreadsheet in Cue Office and paste the practice data into A1. Column B contains billing dates entered in MM/DD/YYYY format. Make sure the spreadsheet recognizes these values as dates before filling columns C and D.

Columns A through D with bill IDs, bill dates, and two month-end column headers, with results not yet filled in
Starting from the bill dates in column B, column C will contain the current month-end and column D will contain the next month-end.

Click C2 and paste the four rows and two columns of formulas below all at once. C2 references B2 and uses 0 to get the current month-end, while D2 uses the same date and 1 to get the next month-end. The next three rows reference the bill date in their respective rows.

=EOMONTH(B2,0)	=EOMONTH(B2,1)
=EOMONTH(B3,0)	=EOMONTH(B3,1)
=EOMONTH(B4,0)	=EOMONTH(B4,1)
=EOMONTH(B5,0)	=EOMONTH(B5,1)
C2:D5 filled with formulas, with the first row displaying serial numbers 45322 and 45351
C2:D5 is filled, with the two dates in the first row temporarily displaying as 45322 and 45351.

How do you display a result like 45322 as a date?

If the results show 45322 and 45351, keep C2:D5 selected. On the Home tab, open the number format menu that shows General and choose Short Date. These numbers are date serials. After the format change, they display as 1/31/2024 and 2/29/2024, and the formulas stay in place.

Open number format menu currently showing General, with Short Date available for selection
Use Short Date in the number format menu to display dates in the selected range.

Save and reopen the workbook. Compare B102 and B104. February ends on the 29th in 2024, a leap year, and on the 28th in 2025. For the December bill, B103, C4 shows 12/31/2025 and D4 shows 1/31/2026.

Manual workbook reopened, showing current and next month-end dates formatted as dates for all four bills
After reopening, compare the end of February between 2024 and 2025, and verify that the December bill properly rolls over to January of the following year.

How can Cue AI fill both columns and keep them up to date?

Open a copy of the billing sheet where C2:D5 has not yet been filled, and click Cue AI. Tell it which month's last day to calculate for each column, and how to update when the original dates change.

Cue AI button and panel title on the top left, with the first row of month-end results not yet filled in the billing sheet on the right
When opening Cue AI, bill dates are in column B, while the result areas in columns C and D remain empty.
For each bill, use its billing date in B2:B5. In C2:C5, show the last calendar day of the billing month. In D2:D5, show the last calendar day of the following month. The results should update if a billing date changes, handle leap years and December, and display as short dates. Make changes only in C2:D5 and keep the original billing dates and labels unchanged.

Once written, C2:D5 contains formulas and displays short dates. Save and reopen the copy to verify the original bill IDs, billing dates, headers, and the two month-end columns. To inspect the next-month formula, click D4 and check =EOMONTH(B4,1) in the formula bar.

After reopening AI results, four sets of month-end dates and original bills appear in the same grid
After saving and reopening, columns C and D still display short dates, with original billing dates and headers intact.

In the reopened copy, change B4 from 12/10/2025 to 01/10/2026. C4 and D4 in the same row will update to 01/31/2026 and 02/28/2026, while the other three rows remain unchanged.

B4 changed to 01/10/2026, with C4 and D4 updating to 01/31/2026 and 02/28/2026
After changing B103's billing date to January 2026, both month-end dates in the same row update automatically.

Practice with Four Bills

Show the full example

Paste starting from A1 and confirm column B is recognized as dates by your spreadsheet. Return to formula steps.

Bill	Bill date	This month end	Next month end
B101	01/15/2024
B102	02/10/2024
B103	12/10/2025
B104	02/10/2025

Verify Two Bills Before Month-End Close

When switching to a new batch of bills, tell Cue AI the date column and the two result columns in the new sheet, and ask it to fill in the current and next month-end dates and format them as short dates. Before submitting your spreadsheet, pick a February bill and check the calendar for that year to confirm whether it ends on the 28th or 29th. Then spot-check a December bill to make sure the next-month column properly rolls over to January 31 of the following year.

If your billing dates come from a supplier’s PDF statement (.pdf), open it for free in Cue Office and check those dates against column B of your Excel ledger (.xlsx). When a billing date changes, log the bill ID and the old and new month-end dates in your Word handoff notes (.docx). For a closing meeting, use a PowerPoint deck (.pptx) to list the bills that carry over to the next month. Cue Office provides free editing tools for spreadsheets, documents, and presentations.

Download Cue Office for Free