How to Split Date into Day, Month and Year in Excel

If you have a date column in your Excel spreadsheet and need to separate it into day, month, and year, you may be wondering how to do so efficiently. This post will guide you through four different methods for splitting a date into day, month and year using Excel formulas, Text to Columns, Flash Fill, and VBA Code. How do I quickly split date as Day, Month and Year using Formulas or Text to Columns feature in Excel.

1. 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:

Step1: 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

Step2: 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.

Step3: 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

Step4: 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.

2. 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:

Step1: select the range of cells that you want to split.

split date into day month year5

Step2: 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

Step3: select Delimited as the file type, and click Next button.

split date into day month year7

Step4: 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

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

split date into day month year9

Step6: the date column has been split into separate day, month and year.

split date into day month year10

3. Split Date into Day, Month and Year with Flash Fill Feature

Flash Fill can quickly and easily split a date column into day, month, and year components in Excel. Just do the following steps:

Step1: First select three new columns to the right of the date column.

Step2: In the first new column, enter the first value you want to extract. For example, the day value.

Step3: In the second new column, enter the second value you want to extract. For example, the month value.

Step4: In the third new column, enter the third value you want to extract. For example, the year value.

How to Split Date into Day, Month and Year flash fill 1.png

Step5: Click on the first day value in the first new column, then click on Flash Fill command under Data Tools group in Data tab.

How to Split Date into Day, Month and Year flash fill 2.png

Step6: Excel will automatically show the suggested day values in the remaining cells of the new column.

How to Split Date into Day, Month and Year flash fill3.png

Step7: Repeat step 6 for the remaining two new columns to extract the month and year values.

How to Split Date into Day, Month and Year flash fill4.png

Once you have extracted all three values, you can delete the original date column and the temporary columns containing the extracted values.

How to Split Date into Day, Month and Year flash fill5.png

4. Split Date into Day, Month and Year with VBA Code

You can also use VBA code to split a date into day, month, and year components in Excel. Just do the following steps:

Step1: Open your Excel workbook and press ALT + F11 to open the Visual Basic Editor.

Step2: Click on “Insert” from the top menu and choose “Module” to create a new module.

Step3: Copy and paste the following code into the module, Save the module and close the Visual Basic Editor.

How to Split Date into Day, Month and Year vba code1.png
Sub SplitDate_excelgeek()
    Dim dateRange As Range
    Set dateRange = Application.InputBox("Select the range of cells containing the dates to split", Type:=8)
    Dim cell As Range
    For Each cell In dateRange
        Dim dayValue As Integer
        Dim monthValue As Integer
        Dim yearValue As Integer
        dayValue = Day(cell.Value)
        monthValue = Month(cell.Value)
        yearValue = Year(cell.Value)
        cell.Offset(0, 1).Value = dayValue
        cell.Offset(0, 2).Value = monthValue
        cell.Offset(0, 3).Value = yearValue
    Next cell
End Sub

Step4: Click on “Macros” under the “Code” group. Select the “SplitDate_excelgeek” macro from the list and click on “Run“.

How to Split Date into Day, Month and Year vba code2.png

Step5: select the range of cells containing the date values that you want to split. For example, select range of cells A2:A5.

How to Split Date into Day, Month and Year vba code3.png

Step6: The VBA Macro will split the selected date column into day, month, and year components and place the results in the adjacent columns.

How to Split Date into Day, Month and Year vba code4.png

5. Video: Split Date into Day Month and Year

This video tutorial will guide you on how to split a date into day, month, and year components using various methods in Excel.

6. Conclusion

Whether you need to work with a large dataset or just a few dates, these methods can help you quickly and accurately separate your date values into separate columns.

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

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

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 into rows or columns with VBA Macro in Excel.

Split Comma Separated Values into Rows or Columns with Text To Columns


Assuming that you have a list of data in range B1:B5, in which contain text string separated by comma characters. And you need to split those comma-separated text string into different columns in Excel. How to do it. You can use the Text To Columns Feature to achieve the result in Excel. Here are the steps:

#1 select the range of cells B1:B5 that you want to split text values into different columns.

split comma separated values1

#2 go to DATA tab, click Text to Columns command under Data Tools group. And the Convert Text to Columns Wizard dialog box will open.

split comma separated values2

#3 select the Delimited radio option in the first Convert Text to Columns Wizard dialog box, and click Next button.

split comma separated values3

#4 only check the Comma Check box under Delimiters section, and click Next button.

split comma separated values4

#5 select one cell as the destination to place the last values. And click Finish button.

split comma separated values5

split comma separated values6

You would notice that all comma-separated text values in the selected range of cells have been split into the different columns.

Split Comma Separated Values into Rows or Columns with VBA Macro


You can also write a simple User Defined Function with VBA code to achieve the same result of splitting comma separated values into different columns. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

split comma separated values7

Function SplitValues(a As String, b As String)
    Dim Text() As String
    Text = Split(b, a)
    SplitValues = Text
End Function

#5 back to the current worksheet,  and select the cell range C1:F1,  then type the following formula in a blank cell, and then press Ctrl  + Shift + Enter keys on your keyboard to change it as array formula.

=SplitValues(“,”,B1)

split comma separated values8

#6 keep the cell range to be selected, and drag the AutoFill Handle down to other cells to apply this formula.

split comma separated values9

 

 

How to Split Multiple Lines in One Cell into Separate Rows or Columns in Excel

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 in a Cell into Multiple Rows or Columns


Assuming that you have a list of data in range B1:B4 which contain multiple lines text string in each cell, and you want to split multiple lines in each cell in range B1:B4 into a spate rows or columns in Excel. How to achieve it.

You can use the Text to Columns feature to achieve the result. And you need to specify the delimiter in Text to Columns, and then it will use this delimiter to split the content of the cells in Range B1:B4. Just do the following steps:

#1 select the range of cells B1:B4 that you want to split.

split multiple lines in a cell into rows1

#2 go to DATA tab, click Text to Columns command under Data Tools group. And the Text to Columns dialog box will open.

split multiple lines in a cell into rows2

#3 choose the Delimited radio button under Original data type section. And click Next button.

split multiple lines in a cell into rows3

#4 only check the Other check box in the Delimiters section, and select the text box of Other, and then press Ctrl + J shortcut in your keyboard. And you can see the expected result in the Data preview section. Click Next button.

split multiple lines in a cell into rows4

#5 select one cell as the destination cell, such as: put the result in Cell C1, and you can type the value $C$1 in destination text box. and then click Finish button.

split multiple lines in a cell into rows5

#6 let’s see the result:

split multiple lines in a cell into rows6

Note: if you want to keep the original data, and you do not to specify the destination cell. And it will overwrite the existing data and generate the result.

Video: Split Multiple Lines in a Cell into Multiple Rows or Columns