How to Get First Day Of Month in Excel

Why This Task Matters in Excel

Every meaningful spreadsheet ultimately deals with time: revenue per month, head-count per quarter, churn by subscription cycle, or project milestones by fiscal period. Virtually all of these analyses begin with a deceptively simple question: “What is the first day of this month?” Getting that single date correct unlocks accurate month-to-date (MTD) comparisons, rolling forecasts, dynamic dashboards, and calendar-driven reports.

Imagine a finance analyst who needs to calculate MTD sales every morning. If the formula that anchors the period start is off by even one day, all subsequent KPIs—growth percentages, targets, bonuses—are wrong. Or consider HR, where benefits eligibility often depends on employment start dates relative to the first of the month. Automating that logic prevents costly manual corrections. In supply-chain planning, replenishment systems frequently restock “on the first business day of the month.” Missing that date can trigger stockouts or carrying-cost overruns.

Excel is uniquely strong in handling dates because its date system stores every day as a sequential integer (serial number), allowing arithmetic, lookups, and formatting to work seamlessly. Once you learn to extract the month’s starting point, you can cascade that insight into countless downstream tasks: grouping in PivotTables, filtering Power Query loads, or building time-intelligent Power BI models. Conversely, failing to master first-of-month logic causes recurring errors that ripple through an organization—skewed charts, incorrect cash-flow timing, or mis-aligned rolling averages. Therefore, knowing several robust, version-compatible ways to get the first day of any month is foundational to every analyst’s toolkit and complementary to skills such as date arithmetic, conditional formatting, and advanced filtering.

Best Excel Approach

The most reliable and universally supported method is a two-part formula combining EOMONTH with simple addition:

=EOMONTH(date,-1)+1

1. EOMONTH(date,-1) returns the last day of the previous month.
2. Adding 1 yields the first day of the current month, regardless of the input day.

Why is this approach considered best?

• EOMONTH is available in all modern Excel versions (desktop, Microsoft 365, Excel Online, and even many BI connectors).
• It handles leap years, different month lengths, and any valid Excel date without extra checks.
• The logic is transparent: “end of prior month plus one day.”
• It is scalable to arrays and dynamic ranges in Microsoft 365, so a single formula can spill an entire list of period starts.

Still, there are scenarios where an alternative is preferable:

=DATE(YEAR(date),MONTH(date),1)

DATE with component extraction is slightly simpler and more intuitive for beginners. Both formulas yield identical results, but EOMONTH offers the extra advantage of offset flexibility—change the second argument and you instantly get the first of next month, two months ahead, or twelve months back.

Prerequisites are minimal: the cell referenced in date must contain a valid Excel date (a serial number) or a formula that evaluates to one, and the workbook must use a date system (Windows: 1900; Mac: 1904) consistent with the rest of your models.

Parameters and Inputs

• date (required) — Any cell, constant, or formula that evaluates to a valid Excel date serial number. Text such as \"01/15/2024\" is acceptable only if the workbook’s regional settings interpret it correctly.
• Months Offset (EOMONTH only) — The second argument, an integer. We use −1 in this tutorial to jump to the previous month’s end. Positive offsets return future months.
• Array Inputs — In Microsoft 365 you can feed a range [A2:A100] to either formula, and Excel will spill a vertical list of month starts.
• Empty Cells — If date is blank, both formulas return 0 (January 0, 1900). Wrap with IF to trap empties.
• Non-Date Values — Text that cannot be parsed as a date produces the #VALUE! error. Use IFERROR or ISNUMBER validation.
• Leap Years — No special handling required; both functions respect Excel’s serial date system.
• Time Stamps — Dates with times (e.g., 04/12/2023 18:35) are safe because both formulas truncate the fractional day.
• Regional Date Systems — Users on the 1904 Mac system will see different serial numbers but the formatted calendar date remains identical.

Step-by-Step Examples

Example 1: Basic Scenario

Objective: Convert a list of random transaction dates into the first day of their respective months.

1. Sample data in column A:

• A2: 3-Jan-2024
• A3: 17-Feb-2024
• A4: 28-Feb-2024
• A5: 5-Jul-2025

2. In B2 enter:

=EOMONTH(A2,-1)+1

Copy or fill down to B5.

3. Format B2:B5 as “Date (14-Mar-2012)” or your preferred custom code yyyy-mm-dd.

Expected Results
B2 shows 1-Jan-2024, B3 and B4 both show 1-Feb-2024, and B5 shows 1-Jul-2025.

Why it works
EOMONTH jumps to 31-Dec-2023 for A2. Adding 1 lands on 1-Jan-2024. Because February 2024 has 29 days (leap year), rows A3 and A4 both backtrack to 29-Feb-2024, plus 1 equals 1-Mar-2024. The logic is consistent regardless of varying month lengths.

Variations

• Replace -1 with 0 and subtract one day at the end if you need the last day of the current month.
• Wrap inside TEXT to return "2024-01-01" as text.

Troubleshooting
If you see January 0, 1900, your source cell is blank or text not recognized as a date. Verify with =ISNUMBER(A2); TRUE indicates a valid date.

Example 2: Real-World Application

Scenario: Finance team must compute Month-to-Date revenue for a rolling dashboard. The raw data table [SalesData] stores each order with a timestamp in [OrderDate] and an amount in [Amount]. A pivot or SUMIFS aggregate requires a dynamic start-of-month anchor.

1. In cell F1, enter a driver date—either =TODAY() or a slicer-controlled date.
2. In F2, calculate the current month start dynamically:

=EOMONTH(F1,-1)+1

3. Create a SUMIFS measure:

=SUMIFS([Amount],[OrderDate],">="&$F$2,[OrderDate],"<="&$F$1)

4. Add an adjacent formula in F3 for the previous month start:

=EOMONTH($F$2,-1)

You can now build a comparative MTD versus Prior MTD KPI by copying the SUMIFS pattern.

Business Impact
This approach removes hard-coded dates from the workbook. When the calendar turns, the dashboard self-updates without manual edits. Management always sees fresh MTD numbers at 9 am every day.

Integration with Other Features

• Use the month start value as the minimum bound in slicer timelines.
• Feed it into conditional formatting rules to shade rows belonging to the current month.
• In Power Query, treat the value as a parameter to filter source SQL queries server-side, improving refresh speed for large data.

Performance Considerations
EOMONTH is volatile only when its first argument is volatile (e.g., TODAY()), so large models remain efficient. SUMIFS leverages vectorized calculations and is faster than looping VBA.

Example 3: Advanced Technique

Objective: Generate an entire calendar table with first-of-month values for five years ahead, then use it for relationship mapping in a data model.

1. In A2 enter the start date of your fiscal calendar: =DATE(2024,1,1).
2. In B2 generate the month start:

=DATE(YEAR(A2),MONTH(A2),1)

3. In A3 use a dynamic array formula (Microsoft 365):

=SEQUENCE(60,1,A2,1)

This spills 60 consecutive days.

4. In B2 convert the entire list to month starts with one dynamic formula:

=UNIQUE(DATE(YEAR(A2#),MONTH(A2#),1))

The # notation refers to the spilled array from A2. You now have a tidy list of 60 unique first-of-month dates (5 years).

5. Load this table into Power Pivot or Power BI. Set [MonthStart] as the primary key to relate fact tables at a month granularity.

Edge Cases & Error Handling

• If fiscal years do not align with calendar months, offset the SEQUENCE start date.
• For 4-4-5 accounting calendars, replace the date arithmetic with a look-up table and still apply DATE logic to normalise.

Professional Tips
Combining dynamic arrays with UNIQUE and SEQUENCE eliminates manual copy-fill procedures and ensures calendar extensions remain automated. Naming the range Calendar_MonthStart improves downstream formula readability.

Tips and Best Practices

1. Anchor dates with absolute references ($F$2) in conditions to prevent mis-aligned ranges during copy operations.
2. Store all dates in genuine date format, never as text, to keep sorting, grouping, and timeline slicers functional.
3. For dashboards, drive [StartOfMonth] from =TODAY() so the view auto-rolls but add an override cell for historical analysis.
4. Combine the first-day formula with EOMONTH(date,0) to create paired start-end period boundaries for cleaner SUMIFS logic.
5. Use named formulas such as FirstOfMonth =EOMONTH(TodayDate,-1)+1 to centralise edits and promote reusability across sheets.
6. In large models, calculate the first day once in a helper column and reference that value rather than repeating the formula in every row to save recalculation time.

Common Mistakes to Avoid

1. Using TEXT dates: Typing \"2024-02-15\" as plain text forces #VALUE! errors in date functions. Confirm date serials with ISNUMBER.
2. Hard-coding month lengths: Adding or subtracting 30 days fails for months with 28, 29, or 31 days. Always use EOMONTH or DATE.
3. Forgetting absolute references: Relative references in SUMIFS shift when copied, causing period mis-alignment. Fix anchor cells with `

How to Get First Day Of Month in Excel

Why This Task Matters in Excel

Every meaningful spreadsheet ultimately deals with time: revenue per month, head-count per quarter, churn by subscription cycle, or project milestones by fiscal period. Virtually all of these analyses begin with a deceptively simple question: “What is the first day of this month?” Getting that single date correct unlocks accurate month-to-date (MTD) comparisons, rolling forecasts, dynamic dashboards, and calendar-driven reports.

Imagine a finance analyst who needs to calculate MTD sales every morning. If the formula that anchors the period start is off by even one day, all subsequent KPIs—growth percentages, targets, bonuses—are wrong. Or consider HR, where benefits eligibility often depends on employment start dates relative to the first of the month. Automating that logic prevents costly manual corrections. In supply-chain planning, replenishment systems frequently restock “on the first business day of the month.” Missing that date can trigger stockouts or carrying-cost overruns.

Excel is uniquely strong in handling dates because its date system stores every day as a sequential integer (serial number), allowing arithmetic, lookups, and formatting to work seamlessly. Once you learn to extract the month’s starting point, you can cascade that insight into countless downstream tasks: grouping in PivotTables, filtering Power Query loads, or building time-intelligent Power BI models. Conversely, failing to master first-of-month logic causes recurring errors that ripple through an organization—skewed charts, incorrect cash-flow timing, or mis-aligned rolling averages. Therefore, knowing several robust, version-compatible ways to get the first day of any month is foundational to every analyst’s toolkit and complementary to skills such as date arithmetic, conditional formatting, and advanced filtering.

Best Excel Approach

The most reliable and universally supported method is a two-part formula combining EOMONTH with simple addition:

CODE_BLOCK_0

1. EOMONTH(date,-1) returns the last day of the previous month.
2. Adding 1 yields the first day of the current month, regardless of the input day.

Why is this approach considered best?

• EOMONTH is available in all modern Excel versions (desktop, Microsoft 365, Excel Online, and even many BI connectors).
• It handles leap years, different month lengths, and any valid Excel date without extra checks.
• The logic is transparent: “end of prior month plus one day.”
• It is scalable to arrays and dynamic ranges in Microsoft 365, so a single formula can spill an entire list of period starts.

Still, there are scenarios where an alternative is preferable:

CODE_BLOCK_1

DATE with component extraction is slightly simpler and more intuitive for beginners. Both formulas yield identical results, but EOMONTH offers the extra advantage of offset flexibility—change the second argument and you instantly get the first of next month, two months ahead, or twelve months back.

Prerequisites are minimal: the cell referenced in date must contain a valid Excel date (a serial number) or a formula that evaluates to one, and the workbook must use a date system (Windows: 1900; Mac: 1904) consistent with the rest of your models.

Parameters and Inputs

• date (required) — Any cell, constant, or formula that evaluates to a valid Excel date serial number. Text such as \"01/15/2024\" is acceptable only if the workbook’s regional settings interpret it correctly.
• Months Offset (EOMONTH only) — The second argument, an integer. We use −1 in this tutorial to jump to the previous month’s end. Positive offsets return future months.
• Array Inputs — In Microsoft 365 you can feed a range [A2:A100] to either formula, and Excel will spill a vertical list of month starts.
• Empty Cells — If date is blank, both formulas return 0 (January 0, 1900). Wrap with IF to trap empties.
• Non-Date Values — Text that cannot be parsed as a date produces the #VALUE! error. Use IFERROR or ISNUMBER validation.
• Leap Years — No special handling required; both functions respect Excel’s serial date system.
• Time Stamps — Dates with times (e.g., 04/12/2023 18:35) are safe because both formulas truncate the fractional day.
• Regional Date Systems — Users on the 1904 Mac system will see different serial numbers but the formatted calendar date remains identical.

Step-by-Step Examples

Example 1: Basic Scenario

Objective: Convert a list of random transaction dates into the first day of their respective months.

1. Sample data in column A:

• A2: 3-Jan-2024
• A3: 17-Feb-2024
• A4: 28-Feb-2024
• A5: 5-Jul-2025

2. In B2 enter:

CODE_BLOCK_2

Copy or fill down to B5.

3. Format B2:B5 as “Date (14-Mar-2012)” or your preferred custom code yyyy-mm-dd.

Expected Results
B2 shows 1-Jan-2024, B3 and B4 both show 1-Feb-2024, and B5 shows 1-Jul-2025.

Why it works
EOMONTH jumps to 31-Dec-2023 for A2. Adding 1 lands on 1-Jan-2024. Because February 2024 has 29 days (leap year), rows A3 and A4 both backtrack to 29-Feb-2024, plus 1 equals 1-Mar-2024. The logic is consistent regardless of varying month lengths.

Variations

• Replace -1 with 0 and subtract one day at the end if you need the last day of the current month.
• Wrap inside TEXT to return "2024-01-01" as text.

Troubleshooting
If you see January 0, 1900, your source cell is blank or text not recognized as a date. Verify with =ISNUMBER(A2); TRUE indicates a valid date.

Example 2: Real-World Application

Scenario: Finance team must compute Month-to-Date revenue for a rolling dashboard. The raw data table [SalesData] stores each order with a timestamp in [OrderDate] and an amount in [Amount]. A pivot or SUMIFS aggregate requires a dynamic start-of-month anchor.

1. In cell F1, enter a driver date—either =TODAY() or a slicer-controlled date.
2. In F2, calculate the current month start dynamically:

CODE_BLOCK_3

3. Create a SUMIFS measure:

CODE_BLOCK_4

4. Add an adjacent formula in F3 for the previous month start:

CODE_BLOCK_5

You can now build a comparative MTD versus Prior MTD KPI by copying the SUMIFS pattern.

Business Impact
This approach removes hard-coded dates from the workbook. When the calendar turns, the dashboard self-updates without manual edits. Management always sees fresh MTD numbers at 9 am every day.

Integration with Other Features

• Use the month start value as the minimum bound in slicer timelines.
• Feed it into conditional formatting rules to shade rows belonging to the current month.
• In Power Query, treat the value as a parameter to filter source SQL queries server-side, improving refresh speed for large data.

Performance Considerations
EOMONTH is volatile only when its first argument is volatile (e.g., TODAY()), so large models remain efficient. SUMIFS leverages vectorized calculations and is faster than looping VBA.

Example 3: Advanced Technique

Objective: Generate an entire calendar table with first-of-month values for five years ahead, then use it for relationship mapping in a data model.

1. In A2 enter the start date of your fiscal calendar: =DATE(2024,1,1).
2. In B2 generate the month start:

CODE_BLOCK_6

3. In A3 use a dynamic array formula (Microsoft 365):

CODE_BLOCK_7

This spills 60 consecutive days.

4. In B2 convert the entire list to month starts with one dynamic formula:

CODE_BLOCK_8

The # notation refers to the spilled array from A2. You now have a tidy list of 60 unique first-of-month dates (5 years).

5. Load this table into Power Pivot or Power BI. Set [MonthStart] as the primary key to relate fact tables at a month granularity.

Edge Cases & Error Handling

• If fiscal years do not align with calendar months, offset the SEQUENCE start date.
• For 4-4-5 accounting calendars, replace the date arithmetic with a look-up table and still apply DATE logic to normalise.

Professional Tips
Combining dynamic arrays with UNIQUE and SEQUENCE eliminates manual copy-fill procedures and ensures calendar extensions remain automated. Naming the range Calendar_MonthStart improves downstream formula readability.

Tips and Best Practices

1. Anchor dates with absolute references ($F$2) in conditions to prevent mis-aligned ranges during copy operations.
2. Store all dates in genuine date format, never as text, to keep sorting, grouping, and timeline slicers functional.
3. For dashboards, drive [StartOfMonth] from =TODAY() so the view auto-rolls but add an override cell for historical analysis.
4. Combine the first-day formula with EOMONTH(date,0) to create paired start-end period boundaries for cleaner SUMIFS logic.
5. Use named formulas such as FirstOfMonth =EOMONTH(TodayDate,-1)+1 to centralise edits and promote reusability across sheets.
6. In large models, calculate the first day once in a helper column and reference that value rather than repeating the formula in every row to save recalculation time.

Common Mistakes to Avoid

1. Using TEXT dates: Typing \"2024-02-15\" as plain text forces #VALUE! errors in date functions. Confirm date serials with ISNUMBER.
2. Hard-coding month lengths: Adding or subtracting 30 days fails for months with 28, 29, or 31 days. Always use EOMONTH or DATE.
3. Forgetting absolute references: Relative references in SUMIFS shift when copied, causing period mis-alignment. Fix anchor cells with .
4. Incorrect regional formats: Entering 03-04-2024 may be interpreted as 3-Apr or 4-Mar depending on locale. Input with DATE(year,month,day) to avoid ambiguity.
5. Overlooking blank cells: Formulas that return January 0, 1900 indicate blanks upstream. Wrap IF(A2="","",formula) to maintain clean visuals.

Alternative Methods

Compatibility
EOMONTH is supported natively beginning Excel 2007. DATE rebuild works in all versions back to 1993. Power Query is available in Excel 2010 (add-in) onward.

Performance
EOMONTH and DATE operate in native C code, extremely fast. Text parsing and VBA introduce overhead; use them only when necessary.

Migration Strategies
Replace old DATEVALUE(TEXT()) chains with EOMONTH during workbook modernisation to reduce error points.

FAQ

When should I use this approach?

Use it whenever you need a dynamic period boundary: MTD, cohort analysis, billing cycles, or any place where “the current month” is referenced in a formula rather than typed.

Can this work across multiple sheets?

Yes. Reference the driver date on Sheet1 from formulas on Sheet2:

=EOMONTH(Sheet1!$A$2,-1)+1

Make the driver date a named range (DriverDate) to avoid sheet qualifiers.

What are the limitations?

EOMONTH cannot produce dates before 1-Jan-1900 in the default system, and the 1900 leap-year bug persists for that century. Extremely old historical data, such as 1800s events, requires text storage or an offset system.

How do I handle errors?

Wrap in IFERROR:

=IFERROR(EOMONTH(A2,-1)+1,"")

Alternatively, test with ISNUMBER(A2) before applying the date formula.

Does this work in older Excel versions?

Yes. In Excel 2003 you need the Analysis ToolPak for EOMONTH. The DATE rebuild works even in Excel 97. Power Query alternatives require Excel 2010 or later.

What about performance with large datasets?

Calculate once per row in a helper column, then use that value in all aggregations. Avoid array formulas referencing entire columns (A:A) pre-Excel 365. In 365, dynamic arrays are efficient but still cache the result only once.

Conclusion

Mastering the skill of deriving the first day of any month is a foundational date technique that underpins revenue analysis, HR eligibility, supply-chain scheduling, and countless other business processes. Whether you embrace the concise EOMONTH method, the intuitive DATE rebuild, or Power Query’s point-and-click transformation, the outcome is the same: dependable period boundaries that make your workbooks smarter and your insights sharper. Continue by exploring adjacent concepts such as calculating month-ends, fiscal offsets, and dynamic rolling windows, and you will be well on your way to full calendar intelligence in Excel.