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.
To understand this formula, you need to split it into three parts:
- In the first part, we have used the date that we have in cell A1.
- After that, in the second part, we have 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 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.
Using 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 the cell A3, and for the year we have used value from the cell A4.
- Add Month to a Date
- Add Years to Date
- Add-Subtract Week from a Date
- Calculate Age using Date of Birth
- Compare Two Dates
- Convert Date to Number
- Count Years Between Two Dates
- Create a Date Range
- DATEDIF Function in Excel
- Get Day Number of Year
- Get Month from a Date
- Get Quarter from a Date [Fiscal + Calendar]
- Get Years of Service
- Get Day Name from a Date in Excel
- Get End of the Month Date in Excel
- Get Total Days in Month in Excel
- Highlight Dates Between Two Dates
- Quickly Concatenate Two Dates
- Random Date Generator (Excel Formula)
- 5 Custom Date Formats I Always Use and You Should Too
- Convert Text to Date