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

Get FREE Advanced Excel Exercises with Solutions!

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 the 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


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

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, that the first day of December is extracted from the given month.

  • Now, drag down to auto-fill the 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 WEEKDAY function to know in detail.

  • Now, press the ENTER Key.

First day Of Month From Month Name using weekday

  • Finally, drag down to AutoFill the 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


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 subtracts 3 days from the December month and adds 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: Calculate First Day of Previous Month in Excel


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


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.

Read More: Excel Formula for Current Month and Year


Download Practice Workbook


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


<< Go Back to Excel MONTH Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo