Excel Formula for First Day of Month: Examples

If the date you want to use is in cell A2, enter =DATE(YEAR(A2),MONTH(A2),1) to return the first day of its month. For example, if A2 contains November 18, 2026, the result is November 1, 2026.

The simplest formula and a worked example

The formula works by taking the year and month from the date in A2, then supplying 1 as the day. The YEAR function returns the year, MONTH returns the month number, and DATE combines those parts into a valid Excel date. The input can be any day of the month; the formula always sets the day to 1.

For a small example, put a date such as November 18, 2026, in A2 and enter the formula in B2. The result is November 1, 2026. If A2 contains March 7, 2025, the result is March 1, 2025. The result is a date value, not a text label, so it can be sorted chronologically, used in date calculations, or compared with other dates.

In Microsoft Excel for Microsoft 365 on Windows, type the formula into the cell where you want the result and press Enter. The same formula works in Excel for Mac and Excel for the web. If your source date is in a different cell, replace A2 with that cell reference. For instance, if the date is in D5, use D5 in each of the three places where the formula refers to A2.

Why the formula returns a real date

Excel stores dates as serial numbers and displays them using a date format. The formula therefore returns a value Excel can use in calculations, rather than merely creating text that looks like a date. This distinction matters when you later subtract dates, sort a list, build a chart, or use the result as a date criterion in another formula.

The number 1 at the end of the formula is the day argument passed to DATE. The month and year arguments come from the input date, so the result remains in the same calendar month and year. Excel also normalizes values that go beyond ordinary calendar ranges: for example, a month number of 13 is treated as January of the following year. That behavior is useful for month-offset formulas, as shown below.

If the result appears as a number instead of a familiar date, the calculation may still be correct. The cell is likely formatted as General or Number, so Excel is showing the underlying serial value. Apply a date format to make it readable; changing a display format does not change the stored date.

An alternative using EOMONTH

You can also find the last day of the previous month and add one day. With the source date in A2, enter =EOMONTH(A2,-1)+1. For November 18, 2026, EOMONTH with an offset of -1 returns October 31, 2026; adding one day gives November 1, 2026.

Both approaches are valid. The DATE version makes the construction explicit: take the input year and month, then use day 1. The EOMONTH version can feel natural if you already work with month-end calculations, because it moves to a month boundary and then advances a day. For a straightforward first-of-month result, the DATE formula is often easier to read.

EOMONTH is a standard Excel function in current Microsoft 365 versions. It returns a date serial, so the result may need date formatting just like the DATE method. Be careful not to omit the final +1: without it, the formula returns the last day of the previous month, not the first day of the input date’s month.

Return the first day of the current month

If you need the first day of the month based on today’s date, use =DATE(YEAR(TODAY()),MONTH(TODAY()),1). This version does not need a date entered in a worksheet cell. It reads the current date from TODAY, extracts its year and month, and sets the day to 1. It updates when Excel recalculates the workbook, such as when the file is opened or a calculation occurs.

For an equivalent month-end approach, enter =EOMONTH(TODAY(),-1)+1. Either formula will produce the first day of the current month according to the date recognized by Excel. If the computer’s system date is wrong, the result can be wrong too. Also keep in mind that a formula based on TODAY is dynamic: it will show a different month after the calendar turns, rather than preserving the month in which you first entered it.

For a fixed reporting period, use an actual date in a cell instead of relying on TODAY. That makes the period explicit and prevents a report from changing its month automatically later. If you need a permanent date stamp, enter or paste a fixed date value rather than a formula that recalculates.

Calculate month starts for a list or another month

For a column of dates, put the first-day formula beside the first date and fill it down. If the source dates are in A2:A100, enter the formula in B2 and copy it through B100. Because A2 is a relative reference, Excel adjusts it to A3, A4, and so on in the copied formulas. Each row then returns the first day of the month corresponding to its own source date.

You can also calculate the first day of a month a chosen number of months before or after the input. Suppose A2 contains a starting date and B2 contains a whole-number month offset: use =DATE(YEAR(A2),MONTH(A2)+B2,1). If A2 is November 18, 2026, and B2 is 2, the result is January 1, 2027. If B2 is -1, the result is October 1, 2026. A zero offset returns November 1, 2026.

This works because DATE adjusts an out-of-range month number into the appropriate year. Another option, using the same A2 date and B2 offset layout, is =EOMONTH(A2,B2-1)+1. For a positive offset of 2, it finds the end of the month one month before the target, then adds a day. Use whole-number offsets for whole months; fractional offsets are not a clear way to specify calendar months and can produce confusing results.

Format the result and use it in reports

To display the result as a date in Excel for Microsoft 365 on Windows, select the formula cell and choose a format from Home > Number. A short date is convenient for ordinary tables; a custom format such as mmm yyyy can display November 2026 while the cell still contains November 1, 2026. To open the Format Cells dialog, use Ctrl+1 on Windows or Command+1 on Mac. In Excel for the web, use the number-format controls on the Home tab; the available interface can differ slightly from desktop Excel.

A month-start date is useful as a consistent key for monthly summaries. For example, dates on November 2 and November 29 both become November 1, so they can be grouped under the same month in a table or chart. This is generally more reliable than making a text label such as “Nov 2026,” because a real date can be sorted and compared as a date. You can still apply a month-and-year display format to make the key look compact.

When creating a monthly report, decide whether you want to show the actual first day or only a month label. A date formatted as mmm yyyy still represents the first day internally. That can be useful for matching, but readers may not realize the underlying day is 1. If the exact date matters, display the day as well.

Common problems and how to fix them

The result shows a number. Select the cell and apply a date format. The value may already be correct; Excel is simply displaying the underlying serial number.
The formula returns an error. Check that the referenced cell contains a usable Excel date, not text that only looks like a date. Imported data may contain spaces or date strings that Excel does not recognize under the computer’s regional settings. Convert the source to a real date before extracting its month.
The result is in an unexpected month. Check the source value and how Excel interpreted it. A string such as 04/05/2026 can mean April 5 or May 4 depending on regional date conventions. Use an unambiguous date entry or inspect the formatted source cell.
A blank input produces an unexpected date. The basic formula treats a blank reference like a zero in the date calculation, so the output is not necessarily blank. If the source column can contain empty cells, use a blank check. With the source date in A2, enter =IF(A2="","",DATE(YEAR(A2),MONTH(A2),1)) to leave the result blank when A2 is empty.
The formula changes after copying. This is usually expected: a relative reference such as A2 becomes A3 when copied down one row. If every row should use the same source cell, use an absolute reference such as $A$2 instead.

When diagnosing a problem, first select the source cell and inspect its value and format, then check the formula reference. Formatting can make a valid date look wrong, while text that resembles a date can prevent date functions from behaving as expected. Separating those two issues makes troubleshooting faster.

Which method should you choose?

Use =DATE(YEAR(A2),MONTH(A2),1) when you want the clearest general-purpose formula and already have a date in a cell. Choose =EOMONTH(A2,-1)+1 if your worksheet already uses EOMONTH for other month-boundary calculations or if that pattern is easier for your team to follow. Both produce the first day of the source date’s month when the input is a valid date.

For a current-month dashboard, use the version based on TODAY, but remember that its output moves forward as time passes. For a historical or future month, use a fixed date cell or a month offset. For data imports, validate the input dates before relying on either formula. The best choice is usually the one that makes the source, time period, and intended behavior most obvious to the next person reading the workbook.

Frequently Asked Questions

Does the formula work if the date is in the middle of the month?
Yes. Any valid date in the input cell works. The formula keeps its year and month and replaces the day with 1.

How do I get the first day of the previous month?
With the reference date in A2, use =DATE(YEAR(A2),MONTH(A2)-1,1). For November 18, 2026, it returns October 1, 2026. The month adjustment also works across year boundaries.

Can I return the first day of next month?
With the starting date in A2, use =DATE(YEAR(A2),MONTH(A2)+1,1). If A2 is December 18, 2026, the result is January 1, 2027.

Why does the formula show 1/1/1900 or another strange date?
Check whether the referenced cell is blank, zero, or not a real date. Excel performs date arithmetic on numeric serial values; a blank or incorrect input can therefore create a result that looks unrelated to the intended month.

Will the formula update automatically next month?
Only a formula using TODAY updates to the current month when Excel recalculates. A formula based on a fixed date cell continues to return the first day of that date’s month unless the source date changes.

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.