Splitting Date Data into Day, Month, and Year
This article explains how to separate date data into day, month, and year components in Excel. This technique is useful for aggregating or analyzing data based on specific days, months, or years. We cover multiple methods that comply with the latest Excel versions.
Using DAY, MONTH, and YEAR Functions to Split Dates
The DAY, MONTH, and YEAR functions allow you to extract the day, month, and year from a given date.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Month | Day | Year |
| 2 | 01/01/2024 | =MONTH(A2) | =DAY(A2) | =YEAR(A2) |
| 3 | 12/25/2024 | =MONTH(A3) | =DAY(A3) | =YEAR(A3) |
Steps
- Enter dates in cells A2 and A3 (e.g., 01/01/2024, 12/25/2024).
- In cell B2, input the MONTH function:
=MONTH(A2) - In cell C2, input the following DAY function:
=DAY(A2) - In cell D2, input the YEAR function:
=YEAR(A2) - Repeat the same formulas for cells B3 to D3.
Function Roles
DAY returns the day of a specified date, MONTH returns the month, and YEAR returns the year.
Results
For the date in cell A2 (01/01/2024), the functions return 1 (Month), 1 (Day), 2024 (Year). For the date in cell A3 (12/25/2024), the results are 12 (Month), 25 (Day), 2024 (Year).
Using the TEXT Function to Split Dates
The TEXT function can also be used to extract day, month, and year as strings.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Month | Day | Year |
| 2 | 01/01/2024 | =TEXT(A2, “mm”) | =TEXT(A2, “dd”) | =TEXT(A2, “yyyy”) |
| 3 | 12/25/2024 | =TEXT(A3, “mm”) | =TEXT(A3, “dd”) | =TEXT(A3, “yyyy”) |
Steps
- Enter dates in cells A2 and A3 (e.g., 01/01/2024, 12/25/2024).
- In cell B2, input the TEXT function:
=TEXT(A2, “mm”) - In cell C2, input the following TEXT function:
=TEXT(A2, “dd”) - In cell D2, input the TEXT function:
=TEXT(A2, “yyyy”) - Repeat the same formulas for cells B3 to D3.
Function Roles
The TEXT function formats dates into a specified string format. “dd” extracts the day, “mm” extracts the month, and “yyyy” extracts the year as strings.
Results
For the date in cell A2 (01/01/2024), the functions return 01 (Month), 01 (Day), 2024 (Year). For the date in cell A3 (12/25/2024), the results are 12 (Month), 25 (Day), 2024 (Year).
Summary
Excel offers several ways to split date data into day, month, and year components using the DAY, MONTH, and YEAR functions or the TEXT function. These methods are effective for managing and analyzing date-based information.