In Excel, to get the month’s first day, you can use a formula. For Example, if you have 16th Jan’2022, and you want to get the 1st of Jan, you can subtract days from it, and to get to the last day or the previous month and then add one to get the first day of the current month.
In the following example, we have a date in cell A1 and we need to get the first day of the month for this.
Formula to Get Beginning of the Month Date
- First of all, enter the equals sign in cell B1.
- After that, refer to cell A1 where you have the actual date, and enter the sum operator.
- Now, use the DAY function and refer to cell A1.
- Next, close the DAY function and enter the minus operator.
- In the end, enter “1” and hit enter to get the result.
=A1-DAY(A1)+1
To understand this formula, you need to split it into three parts:
- In the first part, we used the date that we have in cell A1.
- After that, in the second part, we used the DAY function and get the day number (it returns 16 for 16-Jan) of the original date. And we have deleted that number from the original date. When you do that (date – day number) you get the ending date of the last month.
- Now in the third part, we add 1 in the last month’s date (we got in the second part) to the get the first day of this month.
Use EOMONTH to Get the Beginning of the Month Date
You can also use EOMONTH to get the first day of the month. EOMONTH takes a date and helps you get the last date of a month, and you can add 1 into it to get the first day of the month.
=EOMONTH(A1,-1)+1Using Month Name to Get the First Day of the Month
If want to have a first date for a month and want to use a month name, you can use the following formula.
This formula creates a date by combing the day, month, and year and converting it into an actual date. For the day we have already used 1, for the month we have used a month name from cell A3, and for the year we have used value from cell A4.
=DATEVALUE("1"&"-"&A3&"-"&A4)