Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to get the first day of the month in Excel and Google Sheets
Excel & Google Sheets
=DATE(YEAR(A2),MONTH(A2),1)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your date in A2.
- 2In a result cell, enter =DATE(YEAR(A2),MONTH(A2),1).
- 3This reconstructs the date using the same year and month, but always with day 1.
- 4Format the result cell as a date.
- 5Alternatively, use EOMONTH to get the first day of the next month: =EOMONTH(A2,-1)+1.
Tips
- =EOMONTH(A2,-1)+1 is a concise alternative: EOMONTH with -1 returns the last day of the previous month, and adding 1 gives the first day of the current month.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I get the first day of the next month?
Use =DATE(YEAR(A2),MONTH(A2)+1,1). Excel and Sheets handle the December-to-January rollover automatically.
How do I get the first day of the previous month?
Use =DATE(YEAR(A2),MONTH(A2)-1,1). The January-to-December rollover is handled correctly.