In this post, I’ll show you how to split date into day, month and year in Excel using two methods.
When analysing data, you may need to extract the day or month or year from a date column. If the dates are not many, you can choose to do this manually. But if you have a large dataset with hundreds of date data, then you will definitely need a quicker way to fix this.
If you have a date column in a dataset, and you want to split date into day, month and year in Excel, as shown in the screenshot above, there are two ways you can do this.
You can either use formulas or Text to Columns feature in Excel.
1. Split Date into Day, Month and Year Using Formulas
FOR DAY
1.Create different columns for day, month and year.
2. In the first cell of the Day column, enter the formula:
=DAY(A2)
3. Press Enter
This will extract the day from the date.
FOR MONTH
Go to the next column for Month and enter the formula in the first cell:
=MONTH(A2)
Press enter.
This will extract only the month in the date.
FOR YEAR
Click on the first cell in the Year column and enter the formula:
=YEAR(A2)
Now, select cells C2:E2 and drag to auto fill the formulas in the cells below.
2. Separate Date using Text to Columns
Another way to split date into Day, Month and Year in Excel is by using Text to Column function.
To separate the date into different columns, follow the steps below.
1.Select the date column (without the header).
2. Go to Data tab -> Text to Columns
3. In Convert Text to Columns Wizard – Step 1 of 3 dialog box, check Delimited option and click Next
4. In Step 2 of 3, check Other option and type in / in the text box, then click Next.
5. In Step 3 of 3, enter the cell Destination you want the output
6. Click Finish
