How to Get the First Day of the Month in Excel

Snap any date to the 1st of its month, find the 1st of next month, and total a month’s expenses from any date in it.

You will learn to

• Return the first day of a date’s month
• Return the first day of the next month
• Total values between a month’s first and last day

How it works

Two formulas return the first day of a date’s month: =DATE(YEAR(B2),MONTH(B2),1) and =EOMONTH(B2,-1)+1. For the first day of next month, use =EOMONTH(B2,0)+1 — the day after this month ends — which works across year ends.

First and last days are the building blocks of monthly reports: =SUMIFS(amounts, dates, ">="&first_day, dates, "<="&EOMONTH(any_date,0)) totals one month from any date in it, so a report can be driven by a single input cell.

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