How to Get First Day of Month from Month Name in Excel (3 Ways)

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.

First day Of Month From 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.

First day Of Month From Month Name excel

Steps:

At the beginning of our method, we will format the First day of Month column.

  • So, just select the entire range and press CTRL+1.

First day Of Month From Month Name by formatt

  • 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

First day Of Month From Month Name using EOMONTH

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

First day Of Month From Month Name using EOMONTH

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)

First day Of Month From Month Name using weekday

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.

First day Of Month From Month Name using weekday

  • 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

First day Of Month From Month using Day function

  • After that, press the ENTER key.

First day Of Month From Month using Day function

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")

First day Of Month From Month using Text function

  • Press the ENTER Key.

  • Finally, drag down to AutoFill the series.

First day Of Month From Month using Text function

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:


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

First day Of Month From Month using custom format

  • Now, press the ENTER key.

  • Now, select cell E5 and press CTRL+1, and a dialogue box will pop up.

First day Of Month From Month using custom format

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

First day Of Month From Month in excel

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

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo