# How to get the first day of the month in Excel

Use `=EOMONTH(B2,-1)+1`: the day after the end of last month is the first of this month.

**Excel:** `=EOMONTH(B2,-1)+1` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Transaction | Date | Month start |
| TX-1041 | 2026-01-19 |  |
| TX-1042 | 2026-02-01 |  |
| TX-1043 | 2026-02-28 |  |
| TX-1044 | 2026-07-04 |  |
| TX-1045 | 2026-12-31 |  |

Formula in C2, filled to C6: `=EOMONTH(B2,-1)+1`

Result: 1/1/2026; 2/1/2026; 2/1/2026; 7/1/2026; 12/1/2026

## How it works

`EOMONTH(B2,-1)` is the last day of the previous month, so one day later is the first of this month. The result is a real date, which makes it a good month key for SUMIFS, pivot tables and charts.

## Other ways

- `=DATE(YEAR(B2),MONTH(B2),1)`: Builds the date from the year and month with day 1.
- `=B2-DAY(B2)+1`: Steps back by the day number. Short, but keeps any time in B2.

## Common mistakes

- `TEXT(B2,"mmm yyyy")` looks similar but returns text, which sorts alphabetically. Keep the date and give it a "mmm yyyy" number format.
- If B2 has a time, EOMONTH drops it, so the result is midnight on the 1st.

## Questions

**How do I get the first day of next month?**

`=EOMONTH(B2,0)+1`.

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

Use today's date: `=EOMONTH(TODAY(),-1)+1`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/first-day-of-the-month