How To Extract Month From Date

Excel allows you to format dates in many dissimilar ways. You can cull to prove the engagement in a brusque date format or in a long appointment format.

You can also just show the day number, the month name, or the year from a given date.

In this brusk Excel tutorial, I will testify y'all some easy methods to become the calendar month proper name from a date in Excel.

So, let's go started!

Getting the Month Proper noun from the Date

There are multiple different ways to get monthly from a date in Excel.

The method you choose would depend on how you want the result (i.e., whether you lot want it every bit a text string or take the entire date but only show the proper name of the month)

Let's see a couple of methods to exercise this that you tin can use in different scenarios.

Custom Formatting

Using custom number formatting to get the month name is the best method out of all those covered in this tutorial.

This is because information technology does not alter the underlying appointment. It just changes the way the engagement is being displayed in the cell – which is by showing only the month name from the date.

The benefit of using this method is that y'all can even so use the date in calculations.

Suppose you accept the dates as shown below and you want to only display the month proper noun and not the entire date.

Beneath are the steps to do this:

Select all the cells that have the dates for which y'all want to show the month name
Click the Home tab
In the Number group, click on the dialog box launcher icon (or you can use the keyboard shortcut Command +i). This will open up the Format Cells dialog box
In the Category options, click on Custom
In the type field, enter – 'mmmm'. you should see 1 of the month names in the Sample preview.
Click OK

The above steps would convert all the dates into their respective full calendar month names (as shown below).

Every bit I mentioned, the good thing about this method is that even though yous are seeing the month names in the prison cell, the underlying value is nonetheless a date (which ways that the underlying value is still a number that represents the date).

So, if yous want to apply these dates in calculations, you can hands do that.

You can also employ custom number formatting to evidence the month name or the month value in unlike means. To practise this, you will take to give custom number formatting the right code to display the month name.

Below are the dissimilar month codes that you can apply:

m – this will show the calendar month number. For example, a appointment in Jan would be shown as one, a date in February would be shown as 2, and so on
mm – this will as well prove the month number, simply it will as well make sure that there are always ii digits that are displayed. For example, a date in January would be shown every bit 01, a date in February would exist shown as 02, and a date in November would be shown as eleven
mmm – this will show the month proper noun in a iii-letter lawmaking. For example, a date in January would exist shown as Jan, a appointment in August would be shown as Aug, and then on
mmmm – this is the option that we used in the to a higher place example, and it would testify the consummate calendar month name
mmmmm – this selection shows only the first alphabet of the calendar month name. For example, January is shown equally J and February is shown every bit F, and then on. I've never seen this being used because it's confusing every bit January would as well show J and July would also show J

TEXT Part

TEXT function allows us to convert a appointment into whatsoever permissible format that we want and gives the result as a text cord.

For example, we can use the TEXT part to evidence the month proper name from a date.

Now if y'all're wondering how is it different from the custom number formatting we used before, the big departure here is that with the TEXT office, you can combine the upshot with other functions or text strings.

Don't worry if you are a little lost as of at present, the adjacent few examples will go far clear.

Suppose you have a dataset as shown below and you desire to show the month name instead of the total date.

Beneath is the TEXT formula will give you the month proper noun:

=TEXT(A2,"mmmm")

The above text formula takes the appointment equally the input and applies the specified format to it (which is "mmmm" in this formula).

Unlike the Custom Number Formatting method, when you use the TEXT function, the result is a text value. This ways that you cannot utilize the outcome as a date or number in calculations.

But a good matter about using the TEXT function is that you lot can combine the issue of the function with other text strings.

Let me explicate using an instance.

Suppose you lot have the same data set and in this example, instead of simply getting the month name, y'all want to get the month proper name followed by the quarter number (such as Jan – Quarter 1).

The below formula would do this for you:

=TEXT(A2,"mmmm")&" - Quarter "&ROUNDUP(MONTH(A2)/3,0)

The above formula uses the ROUNDUP and the Month function to become the quarter number of the agenda year, and so it is combined with the month name which is given by the TEXT role.

Since the outcome of the TEXT function is a text string, I tin combine it with other text strings or formula results.

CHOOSE Function

Another formula that you lot tin apply to chop-chop go the calendar month name from the calendar month number is using the Cull formula.

While it ends up existence a long formula, the cull formula technique is useful when you want to go the outcome which you cannot get with custom number formatting or the text function.

For example, if y'all want to return custom month names (such every bit calendar month names in any other language or only 5 alphabets for each month proper name), you can practice that using the CHOOSE formula.

Suppose you lot have a information prepare as shown beneath and yous desire to get the month name for each of these dates.

Below is the formula that will do that:

=CHOOSE(MONTH(A2),"Jan","Feb","Mar","April","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

CHOOSE formula takes an index number (which is given by the month formula in our example) so uses that index number to determine what value to render.

In this example, I take kept the month names to standard 3-alphabetic character names, but you can change this and use whatever proper name y'all want.

Using Power Query

I have started using Power Query a lot more in my piece of work as I find information technology a lot easier to clean the information using it.

Information technology has an inbuilt feature that allows you lot to quickly catechumen a date into the calendar month proper name.

The real value of using Power Query in such a scenario would be when you're importing the information from other Excel files (or consolidating information from multiple Excel files into ane file), and while doing it you desire to convert your dates into calendar month names.

You lot can bank check out my Ability Query course on YouTube if you want to learn more about it.

For this technique to work, your data needs to exist in an Excel table. While you can still utilise Power Query with named ranges, if your information is non in an Excel table I recommend you apply any of the to a higher place methods.

Suppose you have the below data and you desire to convert these dates into month names.

Below are the steps to exercise this:

Select any cell in the dataset
Click the Data tab
In the Become & Transform Data tab, click on From Table/Range
In the Power Query editor that opens upwards, right-click on the Date column header
Go to Transform >> Month >> Proper noun of Calendar month
Click on Close and Load

The above steps would insert a new worksheet and give you the resulting tabular array in that new sheet.

Now, if you're wondering why do all this when yous tin can just use custom number formatting or the text function, yous demand to sympathise the existent value of Power Query trying to automate work.

If you need the month proper noun from the date just one time, experience free to use the methods shown in a higher place.

But if y'all're using Power Query already to manage information from multiple unlike sources or combined files or sheets, then knowing that you tin easily go the month name from the date tin save you lot a lot of time.

So these are some of the methods that you tin use to quickly get the month name from a date in Excel.

I hope you lot institute this tutorial useful.

Other Excel tutorial'south yous may also like:

Calculate the Number of Months Between Two Dates in Excel
Calculate Fourth dimension in Excel (Fourth dimension Difference, Hours Worked, Add/ Subtract)
How to Get the First Mean solar day of the Calendar month in Excel
How to Add or Subtract Days to a Appointment in Excel (Shortcut + Formula)
How to Convert Serial Numbers to Dates in Excel)
How to Stop Excel from Changing Numbers to Dates Automatically
How to Brand an Interactive Calendar in Excel? (Costless Template)