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.
Formula to Get Beginning of the Month Date
Below is the formula that you can use to get the first day of the month from the date.
=A1-DAY(A1)+1
- 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 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.
Note: When dealing with February in leap years, ensure that your date calculations consider February 29th. Excel’s date functions handle this automatically, but it’s an excellent point to remember when writing a formula.
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)+1
- EOMONTH(A1,-1): This part of the formula is responsible for returning the end of the month for a specific date. The EOMONTH function is equipped to find the last day of a month, either prior or future, relative to the date provided. In this case, A1 is the cell containing our reference date, and 1 instructs the function to look at the previous month. Hence, EOMONTH(A1,-1) gives us the last day of the previous month relative to the date in cell A1.
- +1: This part of the formula is an addition. Given that EOMONTH(A1,-1) provides the last day of the previous month, by adding one day to it (+1), you move to the first day of the current month of the given date.
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 using the DATEVALUE.
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) DATEVALUE converts a string into a valid Excel date. Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1, and each subsequent day increases by 1.
- “1”: This string represents the month’s first day. The formula sets the day component of the date to 1.
- “-“: These are string characters used to concatenate the parts of the date in a format recognized by Excel. The dashes are separated between the day, month, and year.
- A3 and A4: These cell references contain the month and year, respectively. The value of A3 is the month’s name. A4 contains a four-digit year (like “2022”).
Using DATE, YEAR, and MONTH Functions
This formula takes the first day of the month by using the current date’s year and month.
=DATE(YEAR(A1), MONTH(A1), 1)
A1 has the reference date. You can replace A1 with any cell containing the date of interest or a specific date. YEAR(A1) extracts the year from the date in cell A1. MONTH(A1) extracts the month from the date in cell A1. The 1 at the end of the DATE function specifies the first day of that month.