How to Split Date into Day, Month and Year in Excel
This post will guide you how to split date into separate day, month and year in excel. How do I quickly split date as Day, Month and Year using Formulas or Text to Columns feature in Excel.
- Split Date into Day Month and Year with Formulas
- Split Date into Day Month and Year with Text to Column Feature
- Video: Split Date into Day Month and Year
Table of Contents
Split Date into Day Month and Year with Formulas
Assuming that you have a list of data in range A1:A6, which contain date values. And you want to split each date value into three separate columns for day, month and year. How to achieve it. You can use three formulas based on the DAY function, the Month function and the Year function to achieve the result of splitting dates. Just do the following steps:
#1 type Day string in a blank cell in a new column, such as:B1, and type Month string in Cell C1, type Year string in Cell D1.
#2 Type the following formula in Cell B2, and press Enter key, then drag the AutoFill Handle over to other cells to apply this formula.
=DAY(A2)
This formula will extract Day Value from the given date in range A2:A6.
#3 Type the following formula based on the Month function to get Month value from date in Cell C2, and press Enter key, and then drag the AutoFill Handle over to other cells to apply this formula.
=MONTH(A2)
This formula will extract Month value from the given date in range A2:A6
#4 Type the following formula in Cell D3 to get the Year value, and press Enter key, and then drag the AutoFill Handle over other cells.
=Year(A2)
You should see that all dates in range A2:A6 have been split into separate day, month and year.
Split Date into Day Month and Year with Text to Column Feature
You can also use Text to Column options to achieve the same result of splitting date into separate day, month and year in three columns. You just need to do the following steps:
#1 select the range of cells that you want to split.
#2 go to DATA tab, click Text to Columns command under Sort & Filter group. And the Convert Text to Columns Wizard dialog will open.
#3 select Delimited as the file type, and click Next button.
#4 only check Other checkbox under Delimiter section, type the delimiter / into the Other text box, and click Next button.
#5 select one Destination cell, such as: B2, click Finish button.
#6 the date column has been split into separate day, month and year.
Video: Split Date into Day Month and Year
Related Functions
-
Excel YEAR function
The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)… -
Excel MONTH function
The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)… -
Excel DAY function
The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…