How to Extract Month and Day from Date in Excel (6 Methods)

While working on an Excel spreadsheet program, you may need to insert dates to make your work easier. If you want to extract the month and day from inserted dates, you can easily do it in Excel. In this article, we are going to discuss six handy methods to extract the month and day from a date in Excel with some easy methods. So, let’s get started.

How to Extract Month and Day from Date in Excel: 6 Methods

Whenever you deal with dates in Excel, there are some fundamental formulas to extract the month, day, and year from your entered date. To do this, you need to insert some dates first. Here, we have taken a dataset of Students with their Date of Birth. Now, we want to extract the month and day from the date.

Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.

1. Using the Format Cells Option

If you want to change the format of the date by extracting the month and day, you can simply use the Format Cells option. It is an easy and time-saving task. Follow the below steps to do it.

📌 Steps:

• Firstly, select the entire range of the date. In our case, we enter the date in columns E and F and now we format the cell.
• Secondly, right-click on the column and select Format Cells.

• Apparently, the Format Cells dialog box appears. Choose Custom as Category and write down mmmm as Type. Then hit OK.

Note: You can also press CTRL + 1 to open the Format Cells dialog box.

Consequently, you will get the month extracted from the date and get it in text format as we have entered the Type as “mmmm” (the full form of the month name).

• Now, repeat the same procedure for the Day column. Write dddd in the Type of the Format Cells window and hit OK.

Sequentially, the extracted day from the date becomes visible on the monitor just like in the image below.

Read More: How to Extract Month from Date in Excel

2. Applying the TEXT Function

There are some built-in functions in Excel. With those functions, we can perform a variety of activities. The Excel TEXT function is one of the useful functions. Using this function, we can extract the months of dates. In the same token, we are using the following dataset. We have attached the steps to extract the month and day with the TEXT function.

📌 Steps:

• Initially, go to cell E5 and write down the formula.

We take the date from D5, so after writing ‘=TEXT’ select the cell D5 where we want to take the date from. Then just put down “mmmm” to show the month.

• Then, press ENTER and drag down the Fill Handle tool for range E5:E12.

Finally, you get the desired result just like in the image shown below.

• Again, select cell F5 and enter the formula.

Here, we take the date from D5, so after writing ‘=TEXT’ select the cell D5 where we want to take the date from. Then just put down “dddd” to show the name of the day.

Eventually, we get our results. See the below image for better visualization.

Read More: How to Extract Year from Date in Excel

3. Employing MONTH and DAY Functions

You can also use the MONTH function and the DAY function to count the number of months and days from the date. With this method, we can show the number of months and the number of days on your date. For example, if today is “10/27/22,” then the MONTH function will show 10 as a month and the DAY function will return 27 as a day. Follow the steps to do it.

📌 Steps:

• Primarily, move to cell E5 and insert the formula.

It takes the date as input and returns the Month Number of cell D5.

Eventually, you get the result after pressing ENTER and dragging down.

• Then, enter the below formula in cell F5.

The DAY(D5) syntax takes the entered date of cell D5 and returns the Day Number.

Finally, you get your desired result.

4. Using Power Query Feature

The Power Query Editor is another way to extract the month and day from the date in Excel. To use the Power Query, you need to create a table with your dataset. In our dataset, we entered the date in the Month and Day column, which will transform into the only month and day list like before.

📌 Steps:

• First of all, select the entire range and go to the Data tab >> choose From Table/Range under Get and Transform Data.

• Sequentially, a Create Table dialog box pops out. Check the My table has headers box.

• Consequently, it will take to the Power Query Editor box. See the below image to clear up the confusion.

• Then, right-click on the Month column and select Transform >> choose Month >> pick Name of Month.

Finally, you extract the month from the date.

• Similarly, select the Day column >> choose Transform >> pick Day >> click on Name of Day.

Sequentially, you get the day extracted from the date.

• At this moment, click on the Close & Load command to get the table in your worksheet.

Finally, after formatting the table, your result will look something similar to the image below.

Read More: How to Extract Specific Data from a Cell in Excel

5. Using the CHOOSE Function

The CHOOSE function will also help to withdraw the month and day from a date. We also need the MONTH function to take the number of months and the WEEKDAY function to take the number of weekdays. The CHOOSE function chooses the same match for the inserted date. Follow the steps to get a proper idea.

📌 Steps:

• Firstly, go to cell E5 and enter the formula.

The MONTH function will help us to take the month number from a date. So, we put the MONTH function inside the CHOOSE function and write the short month name sequentially. The CHOOSE function selects the same month as the entered date in cell D5.

• Then, press ENTER and get the following result.

• Similarly, move to cell F5 and input the formula.

The WEEKDAY(D5) syntax takes the number of days from the date, and the CHOOSE function then chooses the same match for the date.

Finally, you get your day extracted from the date.

Read More: How to Extract Data Based on Criteria from Excel

6. Applying the SWITCH Function

Another function to extract a month from a date is the SWITCH function. We can get the number of the month with the MONTH function and the number of days with the WEEKDAY function. After that, we will switch the month and day from the date. So, let’s take a look at the steps.

📌 Steps:

• Initially, go to cell E5 and input the formula.

The MONTH function takes the number of months from the date and the numbers are the expression_value and the SWITCH function swaps the short form of the month name as we enter the short form in the argument.

Eventually, you get the result in the image below.

• Then, for getting the days from the date insert the formula in cell F5.

• Press ENTER and drag it down for other cells.

Finally, you get the day extracted from the date.

How to Extract Month and Year from Date in Excel

As we mentioned earlier, we can extract the month and year from the date in Excel by using the Format Cells option. We have used this method to extract the month above.

• For extracting the year from a date. Select the entire column of Date and press CTRL + 1 to open the Format Cells. Choose Custom as Category. Create yyyy in the Type box and hit OK.

Eventually, you will extract the year from the date.

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

Conclusion

That’s all about today’s session. And these are some easy methods to extract month and day from date in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet.

Thanks for your patience in reading this article.

Related Articles

• How to Extract Data from a List Using Excel Formula
• How to Extract Data from Excel Sheet 
• How to Extract Data from Cell in Excel
• How to Extract Data From Table Based on Multiple Criteria in Excel
• Excel Formula to Get First 3 Characters from a Cell

<< Go Back To Extract Data Excel | Learn Excel