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


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.

split date into day month year1

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

split date into day month year2

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)

split date into day month year3

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)

split date into day month year4

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.

split date into day month year5

#2 go to DATA tab, click Text to Columns command under Sort & Filter group. And the Convert Text to Columns Wizard dialog will open.

split date into day month year6

#3 select Delimited as the file type, and click Next button.

split date into day month year7

#4 only check Other checkbox under Delimiter section, type the delimiter / into the Other text box, and click Next button.

split date into day month year8

#5 select one Destination cell, such as: B2, click Finish button.

split date into day month year9

#6 the date column has been split into separate day, month and year.

split date into day month year10

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)…
Related Posts

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the ...

How to Count Dates of Given Year in Excel
count dates of given year7

This post will guide you how to count Dates of a certain year in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by a given year in Excel. And ...

How to Sum Values Based on Month and Year in Excel
Sum Values Based on Month 6

We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product ...

How to Convert Date & Time Format to Date in Excel
Convert Date & Time Format to Date 7

Sometimes we want to convert date and time format to date only format in excel for example convert 01/29/2019 06:51:03 to 01/29/2019, we can convert format by Formula or Format Settings. The two ways are easy to learn, so you ...

How to Extract Year from Date & Time Format in Excel
Extract Year from Date & Time Format 6

Sometimes we want to get Year information from date and time format to show the Year only in excel. For example convert 01/29/2019 06:51:03 to 2019, we can get Year information by Formula or Format Settings. The two ways are ...

How to Calculate Remaining Days in a Month or Year in Excel
calculate remaining days5

This post will guide you how to calculate remaining days in a given month or year in Excel. How do I calculate the number of days left in a month or year using a formula in Excel 2013/2016. Calculate Remaining ...

How to Split Comma separated Values into Columns or Rows in Excel
split comma separated values9

This post will guide you how to separate comma delimited cells to new rows or columns in Excel. How do I split comma separated values into rows with Text to Column Feature in Excel. How to convert comma separated text ...

How to sort Dates by Month and Day Only in Excel
sort dates by month7

This post will guide you how to sort dates or birthdays by month or day only in Excel. How do I sort dates by Year or Month or Day only with a formula in Excel 2013/2016. How to use the ...

How to Add the Current Month or Year in a Cell in Excel
add current date in cell6

This post will guide you how to add the current month or year into a cell or header or footer in your worksheet. How do I insert the current date or time in a cell with a formula in Excel. ...

How to Split Multiple Lines in One Cell into Separate Rows or Columns in Excel
split multiple lines in a cell into rows6

This post will guide you how to split multiple lines in a cell into a spate rows or columns in Excel. How do I split cell contents into multiple cells with Text to Columns feature in Excel. Split Multiple Lines ...

Sidebar