To return the first day of the month for a date in cell A2, use =DATE(YEAR(A2),MONTH(A2),1). If A2 contains November 18, 2026, the result is November 1, 2026; format the result as a date if Excel displays a number instead.
The Basic Formula and What It Returns
The formula uses three functions together: YEAR gets the year from A2, MONTH gets its month number, and DATE builds a new date using those values and day 1. It therefore works whether A2 contains the first day, middle day, or final day of a month. For example, January 31, 2025 becomes January 1, 2025, while February 28, 2025 becomes February 1, 2025.
With the source date in A2, enter the formula in another cell, such as B2, and press Enter. To apply it to more rows, drag the fill handle down or double-click it when adjacent rows contain data. The relative reference changes automatically from A2 to A3, A4, and so on. The formula returns a real Excel date, not a text label, so you can sort it, compare it, or use it in calculations.
How Excel Builds a Date
Excel stores dates as serial numbers and displays them according to the cell’s number format. A date formula can therefore return the correct date while the cell shows a plain number. The formula is not wrong in that case: the displayed value is simply not formatted as a date. DATE is useful here because it creates a valid date from year, month, and day components rather than piecing together text.
DATE also handles month numbers that cross a year boundary. For example, a month number of 13 is interpreted as January of the following year. That behavior makes DATE handy for related month calculations, but the basic start-of-month formula keeps the year and month taken from the source date and sets the day to 1.
Examples for Different Cell Layouts
With dates in A2:A100, enter the basic formula in B2 and copy it down to produce a matching month-start date for every row. If A2 contains a date with a time, such as November 18, 2026 at 3:45 PM, the formula still returns November 1, 2026; the time does not affect YEAR or MONTH.
If the year and month are in separate cells rather than in a complete date, DATE can assemble the first of that month directly. With the year in A2 and the month number in B2, use this formula in C2:
=DATE(A2,B2,1)
If A2 is 2026 and B2 is 11, C2 represents November 1, 2026. B2 must be a month number, from 1 through 12; a month name stored as text may need conversion first. Keeping the year and month in separate numeric columns is common in imported reports, but a single proper date cell is usually easier to sort and work with.
Return the Start of the Current Month
To calculate the first day of the current month without referring to an input date, use TODAY as the source. No input-cell layout is required; enter this formula in any cell:
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
TODAY returns the current date according to Excel’s recalculation, and the surrounding functions reduce it to day 1 of that month. The result updates when the workbook recalculates on a later date. It is a formula-driven value, not a permanently fixed date. If you need to preserve the date as a record, copy the result and use Paste Special > Values, or enter a fixed date instead.
You can also get the current month’s first day by finding the end of the previous month and adding one day. With no input cells required, enter:
=EOMONTH(TODAY(),-1)+1
Both approaches return the same month start. The DATE version makes the year-month-day construction explicit; EOMONTH can be convenient when a workbook already uses month-end calculations.
Get the Start of a Previous or Next Month
To move from a date to the first day of the following month, use the source year and increase its month number. With the original date in A2, enter:
=DATE(YEAR(A2),MONTH(A2)+1,1)
If A2 is November 18, 2026, the result is December 1, 2026. If A2 is December 18, 2026, the result is January 1, 2027: DATE adjusts the year automatically. To get the start of the previous month instead, reduce the month number by one. With the date still in A2, use:
=DATE(YEAR(A2),MONTH(A2)-1,1)
For a variable shift, put the number of months to move in B2, where positive values move forward and negative values move backward. With the source date in A2 and the offset in B2, enter:
=EOMONTH(A2,B2-1)+1
For example, if A2 is April 17, 2026 and B2 is 2, the result is June 1, 2026. A value of 0 returns April 1, and -1 returns March 1. This pattern is useful for generating month starts from a selected reporting date.
Use Month Starts in Reports and Date Filters
A month-start date is a useful key for grouping records by month. It is more dependable than grouping by a typed label such as “Nov 2026,” because the result remains a date that Excel can compare chronologically. You can use the month-start column in a PivotTable, chart, or summary table, and apply a display format such as mmm yyyy to make it compact without changing the underlying date.
For date filtering, use the first day of the month as an inclusive lower boundary and the first day of the next month as an exclusive upper boundary. This matters when the source timestamps include times: a condition that stops at the final calendar date can accidentally omit records later that day if the boundary is midnight. An exclusive next-month boundary includes every timestamp in the target month while excluding the next month.
For example, with transaction dates in A2:A100, amounts in B2:B100, the target month’s first day in D2, and the next month’s first day in E2, use this formula to sum that month:
=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&D2,$A$2:$A$100,"<"&E2)
D2 and E2 should be true dates, with E2 exactly one month after D2. For example, boundaries of November 1 and December 1 include dates and times from November 1 through November 30, but not December 1. The formula assumes amounts and dates align row by row in the stated ranges.
Format the Result as a Readable Date
If the result looks like 46327 instead of a calendar date, select the formula cell and choose Home > Number Format, then select Short Date or Long Date. You can also press Ctrl+1 on Windows to open Format Cells and choose a date format. On Mac, Command+1 opens Format Cells. The formula itself does not need to change.
For a month label that still sorts as an actual date, choose a custom display format such as mmm yyyy in Format Cells. The cell will show something like Nov 2026, while its stored value remains November 1, 2026. Avoid converting dates to text solely for appearance if you still need date comparisons, sorting, or calculations. Excel for the web uses the same formula; its formatting controls may be arranged differently from the Windows desktop ribbon.
Common Problems and Misconceptions
- The source looks like a date but is text. Imported values can resemble dates without being recognized as dates. YEAR and MONTH may then return an error. Check whether the value is stored as a real date, and convert the source using Excel’s date-import or conversion tools if necessary.
- The result appears as a number. This is usually a number-format issue, not a calculation problem. Apply a date format to the result cell.
- The formula gives an unexpected month. Check the source value and regional interpretation of dates. A string such as 04/05/2026 can mean different dates under different locale settings. A cell containing an actual date serial avoids that ambiguity.
- A date includes a time. The start-of-month formula ignores the time when extracting year and month. For filtering, however, remember to use an exclusive first-of-next-month boundary so late-day timestamps are not missed.
- The formula shows an error after copying. Confirm that each row points to the intended source cell and that the source is not blank or text. If you need to keep a particular reference fixed while copying, use an absolute reference such as $A$2; for row-by-row calculations, a relative reference like A2 is usually correct.
One common misconception is that changing a cell’s display to show only a month changes the underlying date. Number formatting changes appearance only. A date formatted as mmm yyyy still represents a particular day, so comparisons and summaries may distinguish November 1 from another date in November unless you first convert each source date to its month start or use month boundaries.
Choosing Between DATE and EOMONTH
For a single input date, =DATE(YEAR(A2),MONTH(A2),1) is often the clearest option because it states exactly which year, month, and day form the result. EOMONTH is useful when the problem is already expressed in terms of month ends, or when you need to shift by a number of months. Adding one day to the prior month’s end produces the next month’s first day.
Choose based on what the workbook needs rather than on display style: both return date values. In Microsoft 365 for Windows, Mac, and Excel for the web, these formulas use the same function names and comma-separated arguments. Some regional Excel settings use semicolons as formula separators; if Excel rejects a comma version, the local list-separator setting may require semicolons. Do not confuse that syntax setting with the date’s display format.
Frequently Asked Questions
What is the simplest Excel formula for the first day of a month?
With a date in A2, use =DATE(YEAR(A2),MONTH(A2),1). It returns a valid date for the first day of the same month and works for dates throughout the month.
How do I get the first day of the current month in Excel?
Enter =DATE(YEAR(TODAY()),MONTH(TODAY()),1) in a cell. It recalculates with the current date, so it is not a fixed historical value.
How can I get the first day of next month?
With the starting date in A2, use =DATE(YEAR(A2),MONTH(A2)+1,1). Excel adjusts the year automatically when the source date is in December.
Why does my month-start formula show a number?
Excel stores dates as numbers. Apply Short Date or another date format to the formula cell; changing the format affects how the result appears, not the calculation.
Does the formula work if the original date has a time?
Yes. YEAR and MONTH read the date portion, so a timestamp in A2 still produces the first day of its month. Use month-boundary filtering when analyzing timestamped records.
Related
- Excel Timesheet for Multiple Employees: Setup and Tips
- Excel Invoice Bill Format: Build a Useful Template
- Excel Accounting Solver: What It Does and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Lock a Number in Excel: Keep It Fixed in FormulasTo keep a number fixed when you copy a formula, make its cell reference absolute by adding dollar…
- Excel Count Nonblank Cells: Formulas and ExamplesTo count nonblank cells in Excel, use =COUNTA(A2:A20), replacing the range with the cells you want to check.…
- Freeze Values in Excel: Turn Formulas Into Fixed ResultsTo freeze values in Excel, select the formula cells, copy them, then choose Home > Paste > Paste…
- Unique in Excel: Get Values Without DuplicatesTo list each value once in Excel for Microsoft 365, enter =UNIQUE(A2:A10) in an empty cell, replacing A2:A10…
- Power Query XLOOKUP From Another Table: How It WorksPower Query does not run the Excel XLOOKUP function inside a query. To return columns from another table,…
- How to Tell If Your Excel File Is Corrupted: Key SignsYou can suspect an Excel file is corrupted if Excel cannot open it, displays a repair warning, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.