Mahbubur Rahman
During our work with Excel, sometimes we need to know the first day of the given month or date. But there is no direct function or formula in Excel to generate the First Day of Month from the Month Name. In this tutorial, we will show you, how to do it easily. For your better understanding, we are going to use a sample dataset containing Name, Gender, and Join Month.
Download Practice Workbook
3 Ways to Get First Day of Month from Month Name in Excel
In this article, we will see 3 simple and easy ways to extract the first day of the month using the EOMONTH function, DAY function, and Custom Format option.
Method 1: Get the First Day of Month from Month Name Using EOMONTH Function
At first, we will see the use of the EOMONTH function. If we click on cell D5 we can see that the date is 12/3/2022 (this is an imaginary dataset, so we have taken future dates) and in the Join Month column, only December is given. So, the date formatting is correct.
Steps:
At the beginning of our method, we will format the First day of Month column.
- At this point, a dialogue box will pop up and select Date and the format as shown in the image below.
- Now, type the following formula in cell E5.
=EOMONTH(D5,-1)+1
- After that, press the ENTER key.
So, you can see, the first day of December is extracted from the given month.
- Now, drag down to AutoFill rest of the series.
We have extracted the date by the EOMONTH function. EOMONTH(D5,-1) yields the result 44895 which is the text format of the date 11/30/2022 and =EOMONTH(D5,-1)+1 this whole function will add one day with the date 11/30/2022 and we will get the result 12/01/2022 but as we formatted the column at the beginning of the method the result we will get is 1-Dec.
- After we got the first date, now we will see which day it is in the week, type the following formula in cell F5.
=WEEKDAY(E5,1)
Here, we’ve used 1 as return_type value as we want to start the week from Sunday, as we have chosen 1 so it counts Sunday as 1 and Saturday as 7. There are other options, check the function article to know in detail.
- Now, press the ENTER Key.
- Finally, drag down to AutoFill rest of the series.
That’s it, we have found the first day of the month & its corresponding number for the weekdays.
Read More: Get the First Day of the Current Month in Excel (3 Methods)
Method 2: First Day of Month from Month Name Using Excel DAY Function
In this method, we will see the use of the DAY Function.
Steps:
- At first, we will do the formatting in the First day of Month column as we have done in Method 1.
- Now, type the following formula in cell E5.
=D5-DAY(D5)+1
- After that, press the ENTER key.
Here, DAY(D5) means the number of days in the given month which is December, and D5-DAY(D5) will subtract all the days from the month and we will get the first date of the month by adding simply one.
If we remember correctly our date in cell D5 is the 3rd of December, so the function Subtract 3 days from the December month and add 1 to get our desired result.
- At this point, drag down to AutoFill rest of the series.
- Now, type the following formula in cell F5. It’s a formula using the TEXT function, which will format the result in a more specific way.
=TEXT(E5,"dddd")
- Press the ENTER Key.
- Finally, drag down to AutoFill the series.
Done, we have got the first day and its name.
Read More: How to Convert Date to Day of Year in Excel (4 Methods)
Similar Readings:
- How to Get Last Day of Previous Month in Excel (3 Methods)
- How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
- VBA to Remove Time from Date in Excel (3 Methods)
- Excel Formula for Current Month and Year (3 Examples)
- Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)
Method 3: Excel Custom Formatting to Get First Day of Month from Month Name
At the very end of this tutorial, we will see a custom formatting option.
Steps:
- First Type any of the formulas we used in Method 1 or 2 in cell E5.
=EOMONTH(D5,-1)+1Â Or
=D5-DAY(D5)+1
- Now, press the ENTER key.
- Now, select cell E5 and press CTRL+1, and a dialogue box will pop up.
- From the dialogue box, we have selected custom and Typed dddd in the Type box as shown in the image and click OK.
- Then, drag down to AutoFill rest of the series.
Related Content: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
Conclusion
These are 3 different methods in Excel to extract first day of month from month name. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback
Related Articles
- Last Business Day of the Month in Excel (9 Examples)
- How to Change Default Date Format from US to UK in Excel (3 Ways)
- Calculate First Day of Previous Month in Excel (2 Methods)
- How to Stop Excel from Auto Formatting Dates in CSV (3 Methods)
- Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
