How to Extract Month and Day from a Date in Excel

This dataset showcases Students and their Date of Birth.

Method 1 – Using the Format Cells Option

Use the Format Cells option:

Steps:

Select the entire range containing dates.
Right-click the column and select Format Cells.

In Format Cells, select Category and choose Custom.
In Type, enter mmmm.
Click OK.

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

The month is extracted from the date.

Repeat the same procedure for the Day column.
In Type, enter dddd.
Click OK.

Day is extracted from the date.

Method 2 – Applying the TEXT Function

Use the TEXT function:

Steps:

Go to E5 and enter the formula.

=TEXT(D5,”mmmm”)

‘=TEXT’ selects D5. “mmmm” shows the month.

Press ENTER and drag down the Fill Handle to fill E5:E12.

This is the output.

Select F5 and enter the formula.

=TEXT(D5, “dddd”)

‘=TEXT’ selects D5. “dddd” shows the month.

This is the output.

Method 3 – Using the MONTH and DAY Functions

Use the MONTH function and DAY function:

Steps:

Inl E5, insert the formula.

=MONTH(D5)

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

Press ENTER and drag down the Fill Handle.

Enter the formula in F5.

=DAY(D5)

The DAY(D5) syntax takes the date in D5 and returns the Day Number.

This is the output.

Method 4 – Using the Power Query Feature

Steps:

Select the entire range and go to the Data tab >> choose From Table/Range under Get and Transform Data.

The Create Table dialog box opens. Check My table has headers.

The Power Query Editor will be displayed.

Right-click the Month column and select Transform >> choose Month >> select Name of Month.

This is the output.

Select the Day column >> choose Transform >> select Day >> click Name of Day.

This is the output.

Click Close & Load to see the table in your worksheet.

This is the output, after formatting the table.

Method 5 – Using the CHOOSE Function

Use the CHOOSE function, the MONTH function and the WEEKDAY function:

Go to E5 and enter the formula.

=CHOOSE(MONTH(D5),”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”)

The MONTH function takes the month number from a date. Include the MONTH function in the CHOOSE function and enter the short for month names sequentially. The CHOOSE function selects the month in D5.

Press ENTER to see the result.

In F5 , enter the formula.

=CHOOSE(WEEKDAY(D5),” Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat”)

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

This is the output.

Method 6 – Applying the SWITCH Function

Use the SWITCH function:

Steps:

Go to E5 and enter the formula.

=SWITCH(MONTH(D5),1,”Jan”,2,”Feb”,3,”Mar”,4,”Apr”,5,”May”,6,”Jun”,7,”Jul”,8,”Aug”,9,”Sep”,10,”Oct”,11,”Nov”,12,”Dec”)

The MONTH function takes the number of months from the date; numbers are the expression_value. The SWITCH function swaps the short for the month name.

This is the output.

To get the days from the date insert the formula in F5.

=SWITCH(WEEKDAY(D5),1,”Sun”,2,”Mon”,3,”Tue”,4,”Wed”,5,”Thu”,6,”Fri”,7,”Sat”)

Press ENTER and drag down the Fill Handle.

This is the output.

How to Extract Month and Year from Date in Excel

You can extract month and year from the date by using the Format Cells option (extract the month).

To extract the year, select the entire Date column and press CTRL + 1 to open Format Cells. Choose Custom as Category. Create yyyy in the Type box and click OK.

This is the output.

Practice Section

Practice here.

Download Practice Workbook

Download the practice workbook.

Extracting Month and Day from Date.xlsx

<< Go Back To Extract Data Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Nicol P. Tyler Nov 16, 2022 at 3:51 PM

Hello Mr. Fahim, I have a dataset of two columns of date. Now, I want find out the numbers of days between these days. Can it be possible? Please help me on it if possible.
- Reply
  Fahim Shahriyar Dipto Nov 17, 2022 at 10:08 AM
  
  Hello Nicol,
  Here’s the solution. You can subtract two dates and can find out the days remaining in your hand. I have attached the step-by-step procedure for better understanding.
  Firstly, go to cell E5 and insert the formula.
  =C5-D5
  
  Secondly, press ENTER and drag down the Fill Handle tool.
  
  Finally, you will get the result like the image below.

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

Method 1 – Using the Format Cells Option

Method 2 – Applying the TEXT Function

Method 3 – Using the MONTH and DAY Functions

Method 4 – Using the Power Query Feature

Method 5 – Using the CHOOSE Function

Method 6 – Applying the SWITCH Function

How to Extract Month and Year from Date in Excel

Practice Section

Related Articles

Leave a reply Cancel reply