Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to find the first day of a month
Excel & Google Sheets
=TEXT(DATE(A2,B2,1),"yyyy-mm-dd")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Year | Month | Original day |
| 2026 | 9 | 16 |
=TEXT(DATE(A2,B2,1),"yyyy-mm-dd")→2026-09-01
Both expressions are checked against an independently calculated expected result in HyperFormula. This is a sample execution check, not native Excel or Google Sheets certification.
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 the year and month in A2:B2. C2 shows the original day but is intentionally not used.
- 2DATE(A2,B2,1) constructs the first day of the chosen month.
- 3TEXT makes the example output readable as yyyy-mm-dd.
- 4For a real date already stored in D2, the equivalent numeric-date expression is =DATE(YEAR(D2),MONTH(D2),1).
Tips
- The displayed example returns text. Remove TEXT and apply Date formatting to obtain a numeric date for arithmetic.
- Validate month values from 1 to 12; DATE otherwise normalizes out-of-range values into another year.
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
Does the original day matter?
No. The day argument is always 1.
Can this be used for grouping transactions by month?
Yes, use the numeric date expression as a month-start key, keeping the reporting timezone consistent.
Sources and compatibility
Reviewed 2026-09-16