Rubayed Razib Suprov
Separating dates from different parts of the text or time is one of the most common uses of Excel. Also extracting day month and year from part of the date is also quite a common feature. Both of these processes to separate date from text or extracting day, month, and year from the date in Excel using different types of formula are explained here with adequate examples.
Download Practice Workbook
Download this practice workbook below.
5 Ways to Separate Date Using Formula in Excel
For the demonstration purpose, we are going to use the below dataset. In the left-side range of cells B5:B10 the Original Date and Time column, where both time and date are combined. And in the Date, Month, Day, Year columns we extracted date, day, month, and year. How we extract those values from the Original Date and Time column is discussed here elaborately.
1. Using TEXT Function to Separate Date
Using the TEXT function you can easily extract dates from the combination of date and time. Not only that you can also extract dates by Month, Year, and Day.
Steps
- First, select the cell C5, and enter the following formula:
=TEXT(B5,"m/d/yyyy")- After entering the formula you will only date in the C5 cell.
- After that, drag the Fill Handle to cell C10, and you will notice the range of cells C5:C10 is now filled with only dates from the range of cells B5:B10.
- Next, we need to extract the Month from the date. To do this, select cell D5, and enter the following formula:
=TEXT(B5,"mmmm")- After entering the formula, you will see only the month part of the date and time in cell D5.
- After that, drag the Fill Handle to the cell D10, and you will notice the range of cells D5:D10 is now filled with only months from the range of cells B5:B10.
- Next, we need to extract the Day from the date. To do this, select cell E5, and enter the following formula:
=TEXT(B5,"dddd")- After entering the formula, you will see only the day part of the date and time in cell E5.
- After that, drag the Fill Handle to the cell E10, and you will notice the range of cells E5:E10 is now filled with only days from the range of cells B5:B10.
- Next, we need to extract the Year from the date. To do this, select cell F5, and enter the following formula:
=TEXT(B5,"yyyy")- After entering the formula, you will see only the year part of the date and time in cell F5.
- After that, drag the Fill Handle to the cell F10, and you will notice the range of cells F5:F10 is now filled with only years from the range of cells B5:B10.
- And that’s how we can separate date, day, month, and year from the combination of date and time using a function like the TEXT in Excel.
Read More: How to Separate Time Using Formula in Excel (7 Ways)
2. Utilizing DATE Function in Excel
The DATE function is one of the most flexible ways to separate the Date.
Steps
- First, select the cell C5, and enter the following formula:
=DATE(YEAR(B5),MONTH(B5),DAY(B5))- After entering the formula you will only date in the C5 cell.
- After that, drag the Fill Handle to cell C10, and you will notice the range of cells C5:C10 is now filled with only dates from the range of cells B5:B10.
- Next, we need to extract the Month from the date, we are using the MONTH function. To do this, select cell D5, and enter the following formula:
=MONTH(B5)- After entering the formula, you will see only the month number of the date in cell D5.
- After that, drag the Fill Handle to cell D10, and you will notice the range of cells D5:D10 is now filled with month numbers from the range of cells B5:B10.
- Next, we need to extract the Day from the date, we are using the DAY function. To do this, select the cell E5, and enter the following formula:
=DAY(B5)- After entering the formula you will only day part of the dates and time in cell B5.
- After that, drag the Fill Handle to the cell E10, and you will notice the range of cells E5:E10 is now filled with only days from the range of cells B5:B10.
- Next, we need to extract the Year from the date, we are using the YEAR function. To do this, select the cell F5, and enter the following formula:
=YEAR(B5)- After entering the formula you will see only the year part of the date in the cell B5
- After that, drag the Fill Handle to the cell F10, and you will notice the range of cells F5:F10 is now filled with only years from the range of cells B5:B10.
- And that’s how we can separate date, day, month, and year from the combination of date and time using a formula like the DATE in Excel.
Read More: How to Split Date and Time in Excel (8 Easy Methods)
3. Applying INT Function
The INT function is capable of separating dates from date and time combination quite easily by treating them as a whole number and separating the integer part as Date. The remainder part denotes time.
Steps
- First, select the cell C5, and enter the following formula:
=INT(B5)- After entering the formula you will date part of the date and time values in cell B5.
- After that, drag the Fill Handle to cell C10, and you will notice the range of cells C5:C10 is now filled with dates from the range of cells B5:B10.
- But if you look carefully that there is still time formatting showing in the range of cells C5:C10, though they show a null( zero) value.
- To resolve this issue, we need to turn 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, then go to cell format from the Number group in the Home tab.
- After clicking the button, a drop-down menu option will spawn. From that menu select Short Date.
- After clicking the Short Date, you will see that all of the dates now have only dates format with them.
4. Using TRUNC Function to Separate Date in Excel
The TRUNC function actually truncates the fractional part of your number according to your argument.
Steps
- First, select the cell C5, and enter the following formula:
=TRUNC(B5)- After entering the formula you will see the date part of the date and time values of B5 cells in the C5 cell.
- After that drag the to Fill Handle the cell C10, and you will notice the range of cells C5:C10 is now filled with dates from the range of cells B5:B10.
- But if you look carefully that there is still time formatting showing in the range of cells C5:C10, though they show null( zero) values.
- To resolve this issue, we need to turn 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, then go to cell format from the Number group in the Home tab.
- After clicking the button, a drop-down menu option will spawn. From that menu select Short Date.
- After clicking the Short Date, you will see that all of the dates now have only dates format with them.
- And that’s how we can separate date from the combination of date and time using a formula like the TRUNC in Excel.
Read More: How to Separate Date and Time Using Formula in Excel (4 Easy Ways)
5. Utilizing ROUNDDOWN Function
The ROUNDDOWN function will round the number on the specified number of decimal places.
Steps
- First, select the cell C5, and enter the following formula:
=ROUNDDOWN(B5,0)- After entering the formula you will see the date in the C5 cell.
- After that, drag the Fill Handle to cell C10, and you will notice the range of cells C5:C10 is now filled with dates from the range of cells B5:B10.
- But if you look carefully that there is still time formatting showing in the range of cells C5:C10, though they show null( zero) values.
- To resolve this issue, we need to turn 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, then go to cell format from the Number group in the Home tab.
- After clicking the button, a drop-down menu option will spawn. From that menu select Short Date.
- After clicking the Short Date, you will see that all of the dates now have only dates format with them.
- And that’s how we can separate date from the combination of date and time using a formula like the ROUNDDOWN in Excel.
3 Alternative Ways to Separate Date without Formula in Excel
1. Separate Date Using Power Query
Power Query is a data preparation or processing engine. Here we will take data from the Excel table and then process it in another window. Then we will get the output and load the result in the Excel worksheet.
Steps
- First, select the cells that need separation of dates. In this case, this is the range of cells B4:B10.
- Then from the Data tab, go to Get & Transform Data group and click on the From Table/Range.
- After clicking From Table/Range option, a whole new window with the selected cells as the table will appear.
- Then go to Add Column tab, then From Date & Time group, and click on the Date command.
- A new drop-down menu will appear, from that menu select Date only.
- Then you will see a new column names Date with only dates just right side of the Original Date and Time column.
- Just like the previous one, repeat the same process, from the Add Column tab, go to From Date & Time group, and click on the Date command.
- A new drop-down menu will appear, from that menu go to Month > Month as shown in the image.
- After selecting a month, you will see a new column names Month with only months just right side of the Original Date and Time column.
- Next, again from the Add Column tab, got From Date & Time group, click on Date command.
- After selecting Day, you will see a new column name Day with only days just the right side of the Month column.
- Next, just like the previous one, repeat the same process, from the Add Column tab, go to From Date & Time group, and click on the Date command.
- A new drop-down menu will appear, from that menu go to Year > Year as shown in the image.
- After selecting Year, you will see a new column name Year with only years just right side of the Day column.
- Then click on the Original Date and Time column and right-click on the mouse, from the context menu click Remove to remove the Original Date and Time column.
- After that from the Home tab, click on the Close and Load icon, then click on Close & Load To.
- After that, 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 that we just created in the Power Query window. In this case, we select $C$4:$F$10. After selecting the location, click OK.
- Next, you will notice the column now loaded just beside the original dataset. And it contains the dates, days, months, and years of the original dataset.
2. Utilizing Text to Columns to Separate Date
Text to the column is a helpful method to separate text or numbers according to your specified delimiter. In this case, you can select the data that contains the texts, and then we will separate the texts by space.
Steps
- First, select the range of cells B5:B10, and then from the Data tab click on the Text to Column icon.
- A new window will open, from that select the Delimited option and click OK.
- In the next window, tick mark only the Space option on the Delimiters group. And click Next.
- Next, select General on the Column data format group.
- Then select the destination location of the delimited values. In this case, it is $C$5:$D$10.
- A preview right below the destination box will show a preview of what the data will look like.
- Click on Finish after this.
- After clicking on Finish, you will notice that the dates are now separated from the time in the range of cells C5:C10.
3. Using Flash Fill to Separate Date in Excel
Flash Fill is an efficient tool that recognizes the pattern of cell values and fills cell values accordingly.
Steps
- First, select cell C5, and enter the date exactly as mentioned in cell B5.
- Then drag the Fill Handle icon while right-clicking the mouse to cell C10, then select Flash Fill from the context menu.
- You will see the range of cells C5:C10 now only have dates.
- Next, enter exactly the month number of cell B5 in cell D5, and then drag the Fill Handle to cell D10.
- Then select Flash Fill from the context menu.
- You will see the range of cells C5:C10 now only has the month number.
- Next, enter exactly the day number of cell B5 in cell E5, and then drag the Fill Handle to cell E10.
- Then select Flash Fill from the context menu.
- You will see the range of cells C5:C10 now only has a day number.
- 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.
- You will see the range of cells C5:C10 now only has days which separates the date from the Original Date and Time in the given Excel worksheet.
Read More: How to Separate Date and Time in Excel without Formula (3 Methods)
Conclusion
To sum it up, the question “how to separate dates in Excel” is answered here in 8 different ways. Starting from using functions like TEXT, DATE, INT, TRUNC, ROUNDDOWN, etc. Among all of the methods used here, using DATE, TEXT functions are the easier to understand and simple ones. As they don’t need formatting after the separation of dates. Other processes need prior formatting to visualize the dates properly.
For this problem, a workbook is available to download. Where you can practice and get used to these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
