Start of month formula in Excel

Surprisingly, there is no start of month formula in Excel. There is an end of month formula (EOMONTH) but no formula that gives you day 1 of a month based on any date given.

Start of month with the EOMONTH

One way is to use some simple maths and the EOMONTH formula (end of month). As shown below we have a random selection of dates. We want to see the start date of each month.

  1. Create the EOMONTH for the previous month

    Enter the EOMONTH formula so that it gives you the END of the previous month. As shown below we use -1 in the EOMONTH’s month option. This turns, for example, 16 Aug 2021 into 31 July 2021start of month formula in excel

  2. Make it the 1st day of the next month

    We know that the 1st day of a month always follows the last day of the previous month so we can just add 1 to it as shown below

  3. CRITICAL- Add 1 outside the EOMONTH formula

    Note that the +1 is OUTSIDE the EOMONTH formula. You cannot put the +1 (meaning add a day) into the EOMONTH formula as it is adding/ subtracting months and not days

Start day of month with DATE formula

Another way to achieve this is to break a date down into its month and year and then force it to be day 1 of that month and year

As shown below we have created a formula to extract the month (using the MONTH formula) and year (using the YEAR formula). These could have been done within the next formula but for clarity we have split them up.

We then use the DATE formula and simply put a 1 in the day option and refer the month and year to the appropriate cells.

Related

Excel first day of month

Excel IF current month and year

End of month Excel formula

Convert the month number into the months name