How to extract month from date in Google Sheets – Tutorial
Google Sheets is a powerful spreadsheet software that allows you to perform a variety of data analysis tasks. One common task is extracting the month from a date. This can be useful if you need to group data by month or perform calculations based on the month. Here I am to make an easy Google Sheets tutorial for you!
Fortunately, Google Sheets provides a simple function to extract the month from a date. Here are some ways of doing it.
I’ve recorded a video Google Sheets tutorial, if you prefer this format.
Ways to present the month
To extract a month from a date presented by its ordinal number, you can just use a =MONTH(A2) function. It will be shown in a format “1”, “2”, “3” etc.
If you want your month’s ordinary number to look like “01”, “02” you can use the formula =TEXT(A2, “MM”).
To present a month in a shortened way like “Feb” or “Dec” you need to add a formula =TEXT(A2, “MMM”).
To present the month with a full word, for example “February”, you need to use the formula =TEXT(A2, “MMMM”).
It is not that easy!
There are more complex but at the same time more flexible formulas.
Using the INDEX formula you can create a custom name of the months. For example, “February Carnival”. To do this, you need to create a data range that contains all the months’ names. Here is where you change them.
The formula will look like this: =INDEX($N$2:$N$13,month(A2)). The first part of the formula is the data range and the second one is the cell you’re extracting the date from.
Using ArrayFormula you can just write the formula one time and it will be extended automatically. It is another way of presenting the month in Google Sheets. What the formula looks like:
=ArrayFormula(if(A2:A=””,””,VLOOKUP(MONTH(A2:A),{1 , “Jan” ; 2 , “Feb” ; 3 , “Mar”; 4 , “Apr” ; 5 , “May” ; 6 , “Jun” ; 7 , “Jul”; 8 , “Aug”; 9 , “Sep” ; 10 , “Oct” ; 11 , “Nov” ; 12 , “Dec”},2,0)))
Let’s observe the formula part by part.
if(A2:A=””,”” – this part means, if the cells in column A starting from the A2 are empty, leave the cell empty. If it’s not blank, use VLOOKUP
VLOOKUP(MONTH(A2:A), {…},2,0))) means that if the cell is not empty, find a corresponding name for the extracted month from a date from A2:A. If the extracted month shown by a number is “1” then it is “Jan”, if it is “2” then it is “Feb” etc.
Hope this article was useful for you! Leave your opinion about it in the comments section!