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.
- 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 2021
- 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
- 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.