How to Convert Date to Years in Excel

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.

Businesswoman typing on computer
credit: BananaStock/BananaStock/Getty Images

Step

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

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

Step

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

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

Step

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).