Excel and AI automation, plus free calculators

First Day of Month Formula in Excel

Need the first or last day of a month in Excel? These copy-ready formulas return the first day of the current, previous or next month for any date.

Quick answer

For a date in A1, the first day of that month is:

=DATE(YEAR(A1),MONTH(A1),1)

Format the result cell as a date (Home > Number Format > Short Date).

First day of the current month

=EOMONTH(TODAY(),-1)+1

EOMONTH returns the last day of the month. Going back one month and adding 1 day gives the first day of this month. The same result with DATE: =DATE(YEAR(TODAY()),MONTH(TODAY()),1).

First day of the next month

=EOMONTH(A1,0)+1

First day of the previous month

=EOMONTH(A1,-2)+1

Last day of the month

=EOMONTH(A1,0)

Example table

If A1 holds 18 Nov 2026:

  • First day of this month: 1 Nov 2026
  • Last day of this month: 30 Nov 2026
  • First day of next month: 1 Dec 2026
  • First day of previous month: 1 Oct 2026

First weekday of the month

To find the first Monday, start from the first day and move forward: =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2),7). Use 8 for Monday, 9 for Tuesday, and so on up to 14 for Sunday.

Troubleshooting

  • You see a number like 46327: Excel stores dates as numbers. Format the cell as a date.
  • #VALUE!: A1 contains text, not a real date. Convert it with DATEVALUE or retype it.
  • EOMONTH not found: it exists in all modern Excel versions. Check the spelling.

For counting days between dates see our days from today calculator and working days calculator.

Frequently asked questions

What is the formula for the first day of the month in Excel?

With a date in A1 use =DATE(YEAR(A1),MONTH(A1),1) or =EOMONTH(A1,-1)+1.

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

Use =EOMONTH(TODAY(),-1)+1.

How do I get the last day of the month in Excel?

Use =EOMONTH(A1,0).

Why does my result show a number instead of a date?

Excel stores dates as serial numbers. Format the cell as a date.

Days from today calculator · Working days calculator · IF formula in Excel · Excel calendar template · All free calculators