Excel Formula for Beginning of Month: Dates Made Easy

Formulas & functions · Updated November 18, 2026

If A2 contains an Excel date, use =EOMONTH(A2,-1)+1 to return the first day of that date’s month. For example, a date of July 19, 2026 returns July 1, 2026; the result remains a real date that you can format or use in calculations.

The simplest formula and what it returns

The formula works for any valid date in A2: it finds the last day of the previous month and adds one day. That next day is the first of the month containing the original date. The input can be the first day, a middle day, or the last day of a month, and the same calculation still returns that month’s beginning.

For instance, if A2 contains March 31, 2025, the result is March 1, 2025. If A2 contains January 12, 2025, the result is January 1, 2025. The formula also works across year boundaries: a date in January produces January 1 of that year, even though the preceding month is in the prior year.

In Microsoft Excel for Microsoft 365 on Windows, enter the formula in another cell, such as B2, and press Enter. EOMONTH is a standard Excel function in current Microsoft 365 versions; it does not require enabling the Analysis ToolPak. If Excel displays a number rather than a familiar date, the calculation may still be correct—the result is an Excel date serial, and you can change its display format.

How EOMONTH gets to the first day

EOMONTH returns the final date of a month a specified number of months before or after a starting date. Its second argument is the month offset. In the formula above, -1 means one month before A2. Adding 1 moves from that previous month’s final day to the first day of A2’s month.

This method is useful because it accounts for months of different lengths and leap years without requiring you to work out how many days are in a month. February may have 28 or 29 days, while other months have 30 or 31, but the formula does not rely on a fixed number of days. It also naturally handles December and January crossing into different years.

Excel stores dates as numbers so it can perform date arithmetic. A displayed date such as July 1, 2026 is represented internally by a serial value. Adding one to a date serial advances it by one day, which is why the final addition in this calculation moves from the prior month’s last day to the target month’s first day.

An alternative using DATE

You can also build the first day directly from the year and month of the source date. With a real Excel date in A2, enter =DATE(YEAR(A2),MONTH(A2),1). YEAR extracts the year, MONTH extracts the month number, and DATE combines those values with day 1.

This version is often easy to read because it expresses the goal directly: use the source date’s year and month, then set the day to one. For an A2 value of July 19, 2026, it returns July 1, 2026. It also works for leap years and dates at year boundaries. Both this formula and the EOMONTH version are suitable for ordinary date values in current Excel.

Choose whichever version will be clearest to the people maintaining the workbook. EOMONTH makes the month-boundary calculation explicit; DATE makes the construction of a year-month-day date explicit. Neither method changes the source date in A2. Each returns a separate result in the cell where you enter the formula.

Worked example for a date column

Suppose a worksheet contains transaction dates in column A, with the first date in A2. To calculate the month’s starting date alongside each transaction, put the EOMONTH formula in B2. In that setup, B2 returns the first day of the month for A2. Select B2 and drag its fill handle down the column, or copy and paste it into the remaining rows. Excel adjusts the relative reference from A2 to A3, A4, and so on.

For a small sample, dates of July 19, 2026, August 2, 2026, and January 12, 2025 produce July 1, 2026, August 1, 2026, and January 1, 2025. The returned values can then serve as month labels for sorting, grouping, or summarizing records. Because each output is a date rather than a label made from text, Excel can compare it chronologically and use it in other date calculations.

If some rows have no date, you may prefer to leave their corresponding results blank rather than show an unexpected date. With source dates in A2:A20 and the result formula entered in B2, use =IF(A2="","",DATE(YEAR(A2),MONTH(A2),1)) and fill it down. The test checks whether A2 is empty; the formula returns an empty string for a blank row and a month-start date otherwise. Remember that an empty string is text, not a date, which can matter if later calculations use the result column.

Format the result as a readable date

A formula’s result and its display format are separate. If the result appears as a number, select the cell and choose a date format from Home > Number. You can use Short Date for a compact display or Long Date to show the weekday and full month name. Formatting changes how the value looks, not the underlying date used in calculations.

For a specific display such as July 1, 2026, select the result cells and open Format Cells with Ctrl+1 on Windows. Choose the Number tab, select Custom, and enter mmmm d, yyyy in the Type box. On Excel for Mac, Command+1 opens Format Cells; the same custom date format can be used. In Excel for the web, use the number-format controls on the Home tab; some desktop dialog details may look different.

Use a date format rather than wrapping the result in TEXT just to make it look attractive. TEXT converts a date into text, which can make sorting and date calculations less straightforward. A real date displayed as a month and year is generally more flexible than a text label such as July 2026.

Return the beginning of the current month

If you want the first day of the current month rather than the month belonging to a date in a cell, the formula can use Excel’s current-date function. This version requires no input cells; it reads the computer’s current date when Excel recalculates: =DATE(YEAR(TODAY()),MONTH(TODAY()),1).

TODAY updates when the workbook recalculates, so the result advances to a new month when the current date moves into one. This is useful for a current-month report or a dashboard marker. It is not a fixed historical value: if you need to preserve the date used for a report, copy the result and paste it as a value, or record the relevant date separately. TODAY is a volatile function, meaning Excel may recalculate it as the workbook recalculates.

Use month starts for grouping and filtering

A month-start date is a convenient common key for every date in the same month. For example, transaction dates throughout July all map to July 1. You can use that output as a PivotTable row field or as a helper column when building a monthly summary. A PivotTable can also group date fields by month, so a separate formula column is not always necessary; a helper column is useful when you need a visible, reusable month key in formulas, exports, or other reports.

Real dates sort in calendar order. Text month names may sort alphabetically instead, placing April before February, for example. A month-start value avoids that problem while still allowing a friendly display format. If a report needs only a month label, format the date to show the month and year rather than replacing the value with text.

When using the helper column in a PivotTable or a lookup, make sure its cells contain dates, not a mixture of dates and typed labels. Consistent values make grouping and comparisons more reliable. A month-start date is also different from a month number alone: the date preserves the year, so January 2025 and January 2026 remain distinct.

Common problems and how to fix them

  • The result is a number. Excel is showing the date serial with a General or numeric format. Apply a date format; do not change the formula just because the value is not displayed as a calendar date.
  • The formula returns an error. Check that the source is a valid Excel date, not text that merely looks like a date. Imported data can contain text dates, and interpretation may depend on regional settings. Convert the source data to genuine dates before relying on date functions.
  • The formula displays as text instead of calculating. The cell may be formatted as Text, or the entry may have a leading apostrophe. Change the cell to General, then re-enter the formula. Also confirm that formulas are being calculated rather than displayed; a leading equals sign is required.
  • A blank input produces an odd result. Date functions treat an empty reference differently from a meaningful date, so guard blank rows with an IF test if needed. Use the blank-handling example above, and be aware its empty-string results are not date values.
  • A date includes a time. The month-start formulas return midnight at the beginning of the month, effectively dropping the source time from the returned value. If the time matters for a separate calculation, keep the original date-time value in another cell.
  • The result seems one day off. Check whether the source date was interpreted using the expected day-month order, especially after importing data. Also check the displayed format and the actual source value; a regional parsing issue can make a valid-looking text date mean a different day.

Which formula should you choose?

For most workbooks, either EOMONTH with an offset of -1 and an addition of 1, or DATE using the source year and month, is a sound choice. The result is the same for a valid date. Pick the form that your workbook’s users will understand and maintain most easily.

  • Choose the EOMONTH approach when you want a concise formula based on month boundaries.
  • Choose the DATE approach when you want the year, month, and day components to be visible in the calculation.
  • Use the TODAY-based version only when the desired month should move with the current date.
  • Use a blank check when your source column includes empty rows and you want the output to remain visually empty.

These approaches answer slightly different input needs, but they share an important principle: return a true Excel date, then control its appearance with number formatting. That keeps the result useful for sorting, filtering, date arithmetic, and summaries.

Frequently Asked Questions

What formula returns the first day of the month in Excel?
If the date is in A2, use =EOMONTH(A2,-1)+1. It returns the first day of the month containing the date in A2.

Can I get the first day of the current month?
Yes. Use the TODAY-based DATE formula described above. It updates with recalculation, so it is dynamic rather than a permanent record of the current month’s start.

Does this work in February and leap years?
Yes. Both the EOMONTH and DATE approaches rely on Excel’s date functions rather than a hard-coded month length, so February and leap-year dates are handled automatically.

Why does Excel show a number instead of a date?
The result is likely a valid date serial displayed with a numeric format. Apply Short Date, Long Date, or a custom date format to make it readable.

Can I use the result to group dates by month?
Yes. The first-of-month date acts as a shared key for all dates in that month. It can support summaries and PivotTable layouts, though PivotTables can also group date fields directly.

Will the formula work if the date in my cell is text?
Not reliably. A value that looks like a date may be stored as text, particularly after importing a file. Convert it to a real Excel date first, and check regional date-order settings if Excel interpreted the text unexpectedly.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.