# How to Separate Date in Excel Using Formulas (5 Methods)

For this demonstration, weâ€™ll be utilizing the dataset provided below. In the range of cells from B5 to B10 on the left, youâ€™ll find the Original Date and Time column, which combines both the date and time. In the adjacent columns labeled Date, Month, Day, and Year, we have separated out the individual date components. We will discuss in detail how to extract these values from the Original Date and TimeÂ column.

## Method 1 – Using TEXT Function to Separate Date

• Select the cell C5.
• Enter the following formulaÂ to extract the date from the combined date and time in cell B5:
`=TEXT(B5,"m/d/yyyy")`
• This formula will display only the date portion in cell C5.
• Drag the Fill Handle down to cell C10. Now the range of cells C5:C10 will be filled with dates extracted from the range of cells B5:B10.

• To extract the month from the date,Â select cell D5Â and enter the formula:
`=TEXT(B5,"mmmm")`
• This will show only the month part of the date in cell D5.
• Drag the Fill Handle down to cell D10Â to fill the range of cells D5:D10 with months from the original date and time data.

• For extracting the Day,Â select cell E5Â and enter the formula:
`=TEXT(B5,"dddd")`

Cell E5 will display only the day part of the date and time.

• Drag the Fill Handle down to cell E10Â to populate the range of cells E5:E10 with days from the original data.

• Finally, to extract the year,Â select cell F5Â and enter the formula:
`=TEXT(B5,"yyyy")`

Cell F5 will show only the year part of the date and time.

• Drag the Fill Handle down to cell F10Â to complete the extraction of years from the original data.

And thatâ€™s how you can separate date, day, month, and year from a combination of date and time using the TEXT function in Excel.

## Method 2 – Utilizing DATE Function in Excel

• Select cell C5.
• Enter the following formulaÂ to extract the date from the combined date and time in cell B5:
`=DATE(YEAR(B5),MONTH(B5),DAY(B5))`

This formula will display only the date portion in cell C5.

• Drag the Fill Handle down to cell C10. Now the range of cells C5:C10 will be filled with dates extracted from the range of cells B5:B10.

• To extract the month from the date, weâ€™ll use the MONTH function.Â Select cell D5Â and enter the formula:
`=MONTH(B5)`

This will show only the month number of the date in cell D5.

• Drag the Fill Handle down to cell D10Â to fill the range of cells D5:D10 with month numbers from the original date and time data.

• For extracting the day, weâ€™ll use the DAY function.Â Select cell E5Â and enter the formula:
`=DAY(B5)`

Cell E5 will display only the day part of the date and time.

• Drag the Fill Handle down to cell E10Â to populate the range of cells E5:E10 with days from the original data.

• Finally, to extract the year, weâ€™ll use the YEAR function.Â Select cell F5Â and enter the formula:
`=YEAR(B5)`

Cell F5 will show only the year part of the date.

• Drag the Fill Handle down to cell F10Â to complete the extraction of years from the original data.

• And thatâ€™s how you can separate date, day, month, and year from a combination of date and time using the DATE function in Excel.

## Method 3 – Separating Dates Using the INT Function in Excel

• Select cell C5.
• Enter the following formulaÂ to extract the date part from the combined date and time in cell B5:
`=INT(B5)`

This formula treats the combined value as a whole number, separating the integer part as the date.

• Drag the Fill Handle down to cell C10. Now the range of cells C5:C10 will be filled with dates extracted from the range of cells B5:B10.
• However, if you look carefully, there is still time formatting showing in the range of cells C5:C10, even though they display a null (zero) value.

• To resolve this issue, we need to change the format of the range of cells C5:C10 from General to Short Date format.
• To do this:
• Select the range of cells C5:C10.
• Go to theÂ cell formatÂ option from theÂ Number groupÂ in theÂ Home tab.
• Click the button, and a drop-down menu will appear.
• From that menu, selectÂ Short Date.

• After clickingÂ Short Date, you will see that all the dates now have only the date format without any time information.

And thatâ€™s how you can separate the date portion from a combination of date and time using the INT function in Excel.

## Method 4 – Using TRUNC Function to Separate Date in Excel

• Select cell C5.
• Enter the following formulaÂ to extract the date part from the combined date and time in cell B5:
`=TRUNC(B5)`

This formula truncates the fractional part of the number, treating it as a whole number representing the date.

• Drag the Fill Handle down to cell C10. Now the range of cells C5:C10 will be filled with dates extracted from the range of cells B5:B10.
• However, if you look carefully, there is still time formatting showing in the range of cells C5:C10, even though they display null (zero) values.

• To resolve this issue, we need to change the format of the range of cells C5:C10 from General to Short Date format.
• To do this:
• Select the range of cells C5:C10.
• Go to theÂ cell formatÂ option from theÂ Number groupÂ in theÂ Home tab.
• Click the button, and a drop-down menu will appear.
• From that menu, selectÂ Short Date.

• After clickingÂ Short Date, you will see that all the dates now have only the date format without any time information.

And thatâ€™s how you can separate the date from the combination of date and time using the TRUNC function in Excel.

## Method 5 – Utilizing ROUNDDOWN Function

• Select cell C5.
• Enter the following formulaÂ to extract the date part from the combined date and time in cell B5:
`=ROUNDDOWN(B5,0)`

This formula will round down the number to the nearest integer, effectively treating it as a whole number representing the date.

• Drag the Fill Handle down to cell C10. Now the range of cells C5:C10 will be filled with dates extracted from the range of cells B5:B10.
• However, if you look carefully, there is still time formatting showing in the range of cells C5:C10, even though they display null (zero) values.

• To resolve this issue, we need to change the format of the range of cells C5:C10 from General to Short Date format.
• To do this:
• Select the range of cells C5:C10.
• Go to theÂ cell formatÂ option from theÂ Number groupÂ in theÂ Home tab.
• Click the button, and a drop-down menu will appear.
• From that menu, selectÂ Short Date.

• After clickingÂ Short Date, you will see that all the dates now have only the date format without any time information.

And thatâ€™s how you can separate the date from the combination of date and time using the ROUNDDOWN function in Excel.

## 3 Alternative Methods to Separate the Date without a Formula in Excel

### Method 1 – Separating Dates Using Power Query

• Select the Cells: Begin by selecting the range of cells (B4:B10) that contain the dates you want to separate.
• Access Power Query: Go to the Data tab and find the Get & Transform Data group. Click on From Table/Range.

• Create New Columns:
• Date Column: In the new window, go to the Add Column tab. Under the From Date & Time group, select the Date command. Choose Date only from the drop-down menu.

• This will create a new column with only the dates.

• Month Column: Repeat the process for the month. Under From Date & Time, select the Date command.Â
• Choose Month > MonthÂ from the drop-down menu.

• Day Column: Again, create a new column for the day.

• Use the Date command and select Day.

• Year Column: Finally, create a column for the year.

• Use the Date command and choose Year > Year.

• Remove Original Date and Time Column: Right-click on the original date and time column and select RemoveÂ from the context menu.

• Load the Result: Go to the Home tab and click on the Close and LoadÂ icon. Choose where you want to load the result (e.g., a new worksheet).

• Choose where you want to load the result (e.g., a new worksheet).
• A new window named Import Data will open, from that window, choose Existing worksheet from Where do you want to put the data? option.
• After that choose the location of the new columns. In this case, we select \$C\$4:\$F\$10. Click OK.

• You will notice that the column is loaded next to the original dataset. And it contains the dates, days, months, and years of the original dataset.

### Method 2 – Utilizing Text to Columns to Separate Dates

• Select the Cells: Begin by selecting the range of cells (B5:B10) that contain the combined date and time values.
• Access Text to Columns: Go to the DataÂ tab and click on the Text to ColumnsÂ icon.

• Choose Delimited Option:
• In the new window, select the Delimited option and click OK.

• In the next window, mark only the SpaceÂ option under the DelimitersÂ group and click Next.

• Select Data Format:
• Choose General under the Column data formatÂ group.
• Specify the destination location for the separated values (e.g., \$C\$5:\$D\$10).
• Preview and Finish:
• A preview below the destination box will show how the data will look after separation.
• Click FinishÂ to complete the process.

After following these steps, youâ€™ll notice that the dates are now separated from the time in the range of cells C5:C10.

### Method 3 – Using Flash Fill to Separate Dates in Excel

• Enter the Date Format:
• Start by selecting cell C5.
• Enter the date exactly as mentioned in cell B5.
• Apply Flash Fill:
• Drag the Fill Handle icon (located at the bottom right corner of the cell) while right clicking the mouse to cell C10.
• From the context menu, select Flash Fill.

• The range of cells C5:C10 will now contain only dates.
• Separate Month, Day, and Year:
• In cell D5, enter the month number from cell B5.
• Drag the Fill Handle to cell D10.
• Again, select Flash FillÂ from the context menu.

• The range of cells C5:C10 will now display only the month numbers.
• Enter the day exactly as mentioned in cell E5.
• Drag the Fill Handle to cell E10.
• Then select Flash Fill from the context menu.

• The range of cells C5:C10 will now display only the day numbers.
• Next, enter exactly the year number of cell B5 in cell F5. Then, drag the Fill Handle to cellÂ F10.
• Then select Flash Fill from the context menu.

• Result:
• After completing these steps, the range of cells C5:C10 will have separate columns for dates, months, days, and years, effectively separating the date from the original combined date and time in the Excel worksheet.

<< Go Back to Date and Time | Split | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF