How to Get the Month and Year from Date column in Excel 2016

Date Data

Date is one of the most important pieces of information in all record keeping. Almost all transactions occur on a daily basis and recordings must be done on the day they occur. Common transnational activities like banking, invoice payments, receipts, bookings, orders and etc recording the date of transaction is important. Depending on the volume of transactions, the records can vary from a few hundred records to more than a thousand. When trying to do some data analysis based on time periods like monthly, quarterly or annually, date data become the determining factor.

Therefore, it is important to extract the month and year from the date column before doing the analysis.

Get Month And Year  from Date Column in Excel.

In order to get the month, quarter and year from the date column, you will have to insert three (3) calculated columns. Two (2) columns are need for month because one will return the month in number and another will return the actual name of the month. The month in number will be used to specify the quarter range. In the example image below the MonthNo, Quarter, MonthName and Year column data are calculated fields.

MonthNo = Month(A2)

Cell A2 is the first cell in the date column. Then the rest of the rows is matter of dragging the formula down.

MonthName = TEXT(A2,”MMMM”)

Text function converts the date data into actual month name. “MMMM” will return the full name of the month (eg June) while triplle “mmm” will return short name for month (eg Jun).  Also, note that if actual day is required than the formula is same but “mmmm” needs to be replaced with “dddd” or “ddd”.

Quarter =IF(B2<=3,”1st Qtr”,IF(B2<=6,”2nd Qtr”,IF(B2<=9,”3rd Qtr”,”4th Qtr”)))

If() function is used to determine the month range (eg. 1-3 is 1^st Qtr, 4-6 is 2^nd Qtr, 7-9 is 3^rd Qtr, 10-12 is 4^th Qtr.  Cell B2 is used because it is calculated month number from the date column.

Note: When nested IF function is used, it is important to decide whether the range determination can be done in descending or ascending order. Nested IF function doesn’t work properly if formula doesn’t specify proper order.

Year  = Year(A2).

This returns the year from the date.

Using Filters in Excel

Once the year, quarter, and month name columns are inserted then, the data table is ready for analysis by applying Filter and Slicers. Applying Filters and Slicers in Excel is covered in this post : How to Apply Filters and Slicers in Excel.

Date, month, quarter and year columns are also useful for summarizing data using Pivot tables.