Skillsheet-Extract the year and month from a date in a spreadsheet

Much of the time when you receive or download data, dates are put all into one column. That’s great if you just want to sort by date, or eyeball the range of dates.

But what if you want to analyze your data to see how many dates were in a particular year or month, or see trends across either. That’s where the YEAR and MONTH functions come in.

We’re going to use Excel for the Mac for this, but these functions are pretty well universal across makes.

Let’s start with a worksheet that has some dates in it. This one is of parking tickets in Ottawa. You can download an approximately one per cent sample of the data here.

We’re going to create year and month columns. Highlight columns B and C and then use your spreadsheet’s functionality to insert two new columns. In most cases, you can right click after highlighting the columns and choose Insert. On a Mac without a right mouse button, CTRL>Click will do it.

Label your new columns Issue_Year and Issue_Month and change the format of both columns to General by highlighting the column, right clicking (or CTRL>Click on a Mac), choosing Format Cells, and choosing General in the Number formats tab. If you don’t do this, your years and months may appear as dates, because the inserted columns pick up the formatting of the column to their left.

Now, the main event.

To extract the year, type the YEAR function into the cell right below the column label for Issue_Year.

The formula is =YEAR(A2).

Copy the formula down the remainder of the column. In Excel you do that by dragging down or double clicking the bottom right of the cell containing the formula.

The MONTH function follows the same pattern. Enter it in the first cell below the header in column C.

Copy the function down, and you’re done. Now you can analyze the data looking for patterns related to years and months, using pivot tables and other analytical tools.