Date formula page
First Day of Month Formula
Use this when reports need a stable month-start date from a source date.
Best for
Get the first day of a month from a date.
What it returns
If A2 is 2026-01-15, the result is 2026-01-01.
Copy formulas
=DATE(YEAR(A2), MONTH(A2), 1)=DATE(YEAR(A2), MONTH(A2), 1)DATE with YEAR and MONTH returns the first day of the month containing A2; EOMONTH(TODAY(),-1)+1 is the equivalent current-month shortcut.
Example data
| Start Date | End Date | Holiday | Result |
|---|---|---|---|
| 2026-01-01 | 2026-01-31 | 2026-01-19 | 30 |
| 2026-02-10 | 2026-03-12 | 30 | |
| 1990-05-20 | 2026-05-20 | 36 |
What it returns
If A2 is 2026-01-15, the result is 2026-01-01.
How the formula works
- YEAR extracts the year from A2.
- MONTH extracts the month from A2.
- DATE rebuilds the first day of that month.
| Syntax piece | Role in the formula |
|---|---|
| YEAR(A2) | The year extracted from the source date. |
| MONTH(A2) | The month number extracted from the source date. |
| 1 | The day argument that sets the result to the first calendar day. |
Verified examples
=DATE(YEAR(A2), MONTH(A2), 1)Excel: Enter 2026-01-15 in A2 as a real date. Returns: 2026-01-01
=DATE(YEAR(A2), MONTH(A2), 1)Google Sheets: Enter 2026-02-28 in A2 as a real date. Returns: 2026-02-01
=EOMONTH(TODAY(),-1)+1Excel: Open the sheet during any calendar month. Returns: The first calendar day of the current month
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| The formula returns an error | A2 contains text rather than a real spreadsheet date. | Convert A2 to a date value before applying YEAR and MONTH. |
| The result looks like a serial number | The result cell is formatted as General or Number. | Apply a date format such as yyyy-mm-dd to the result cell. |
| The current-month shortcut returns the wrong month | The EOMONTH offset was changed from -1 or the system date is unexpected. | Use EOMONTH(TODAY(),-1)+1 for the first day of the current month and check the device date. |
When not to use this formula
- Do not use this formula to find the last day of a month; use EOMONTH for that boundary.
Alternatives
| Alternative | When to use it |
|---|---|
| Last Day of Month Formula | Use when the month-end date is needed instead of the month start. |
| SUMIFS by Month Formula | Use when the month boundary should feed a conditional total. |
Related formulas
Official references
- DATE function from Microsoft
- EOMONTH function from Microsoft
FAQ
Can A2 be any date in the month?
Yes. YEAR and MONTH use A2 to reconstruct day 1 of that same month.
How do I get the first day of the current month?
Use =EOMONTH(TODAY(),-1)+1, which advances one day from the prior month-end.