How to Convert Date to Years in Excel

By Christy Flora

Many spreadsheets include date-specific information. It is useful to be able to break the date data into years and/or months for reporting purposes. Converting a date to a year or a month will allow you to sort, create pivot tables and/or charts grouping information by year or month. This will allow you to extrapolate year- or month-specific information for year-end or quarterly reporting. This conversion will not replace your original date data.

Things You'll Need

  • Excel

Step 1

Open your workbook in Excel that has the dates that you want converted to years. If you don't have that file available, for practice, populate some date data into a single column in a new Excel workbook.

Step 2

Label your date column "Date." Label the column that you want the year to be populated in as "Year."

Step 3

Enter the following formula in the second row of the "Year" column: "=YEAR(A2)," where A2 is the cell that is located in the second row of the "Date" column. If your "Date" column is not column "A," change this cell reference accordingly to indicate the appropriate column (letter A through IV) and the appropriate row (number 1 through 65,536).

Step 4

Label the column that you want the month to be populated in as "Month."

Step 5

Enter the following formula in the second row of the "Month" column: "=MONTH(A2)," where A2 is the cell that is located in the second row of the "Date" column. If your "Date" column is not column "A," change this cell reference accordingly to indicate the appropriate column (letter A through IV) and the appropriate row (number 1 through 65,536).

Tips & Warnings

  • If you need to convert the date to days, simply follow the above steps, labeling the column "Day" and using the formula "=DAY(A2)."
  • If you would like to convert the date to the day of the week, use the formula: "=TEXT(A2,"ddd")."
  • Ensuring that you are referring to the correct cell is essential to return the correct information when you are using a formula. If you are unsure whether or not your formula is referring to the correct cell with the formula cell selected, left-click with your mouse in the function (fx) box at the top of your workbook. The cell referred to in the formula cell will be highlighted in the function box, and the cell will be highlighted in the workbook.

References & Resources