Soumik Dutta
Excel has the ability to handle several types of data. Beyond them, time and date are most common. Besides them, Excel has completely different data types where we can insert dates and times together. However, sometimes it becomes required for us, to split them. Excel has several features and functions for separating them. In this article, we will demonstrate to you all possible processes on how to separate the date and time using the formula in Excel. If you are facing difficulties to do that, follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
4 Easy Methods to Separate Date and Time Using Formula in Excel
For describing the approaches, we consider the entry time in the office of an employee of a random organization as our dataset. The last 10 days of the month of March are shown in our dataset. So, our dataset is in the range of cells B5:B14. We are going to use 4 distinct formulas to separate the date and time and place them into columns C and D.
1. Using INT Function
In this method, we will use the INT function to separate date and time in the Excel worksheet. The data we have to modify is in the range of cells B5:B14 and the result will be in the range of cells C5:D14. The steps are given below:
📌 Steps:
- First of all, select the entire range of cells C5:C14.
- After that, in the Number group, select the drop-down arrow of that box and choose the Short Date option.
- Again, select the range of cells D5:D14. In the Number group, select the drop-down arrow and click on Time.
- Now, in cell C5, write the following formula:
=INT(B5)
- Press the Enter on your keyboard to get the result. You will see the date will separate and show in cell C5.
- Then, double-click the Fill Handle icon allocated on the right-bottom side of the cell to copy the formula up to cell C14. Besides it, you can also simply drag the Fill Handle icon up to cell C14.
- All the data will be extracted.
- After that, select cell D5 and write down the following formula into the cell.
=B5-C5
- Press Enter and the time will be shown separately in the cell.
- Double-click on the Fill Handle icon to copy the formula up to cell C14.
- Finally, you will get the date and time in separate columns.
Finally, we can say that our function worked perfectly and we are able to separate date and time using a formula in Excel.
2. Utilizing TEXT Function to Separate Date and Time
In this process, we will use the TEXT function to separate date and time in the Excel spreadsheet. Our dataset is in the range of cells B5:B14 and the result will be in the range of cells C5:D14. The steps of this process are given below:
📌 Steps:
- First, select cell C5.
- Now, write down the following formula into the cell.
=TEXT(B5,"m/d/yyyy")
- Press the You will see the date will separate and show in cell C5.
- Then, double-click the Fill Handle icon allocated on the right-bottom side of the cell to copy the formula up to cell C14. Besides it, you can also simply drag the Fill Handle icon up to cell C14.
- All the dates will be shown separately.
- After that, select cell D5 and write down the following formula into the cell.
=TEXT(B5,"hh:mm:ss AM/PM")
- Press Enter on your keyboard and the time will be shown separately in the cell.
- Double-click on the Fill Handle icon to copy the formula up to cell C14.
- You will finally get the date and time in separate columns.
So, we can say that our function worked successfully and we are able to separate date and time using a formula in Excel.
Read More: How to Separate Date from Text in Excel (4 Methods
3. Applying TRUNC Function
In the following procedure, the TRUNC function will help us to separate the date and time. Our dataset is in the range of cells B5:B14 and the result will be in columns C and D. The steps of this procedure are given as follows:
📌 Steps:
- Select the entire range of cells C5:C14.
- After that, in the Number group, select the drop-down arrow of that box and choose the Short Date option.
- Now, in cell C5, write the following formula:
=TRUNC(B5,0)
- Press the Enter on your keyboard to get the result. You will see the date will separate and show in cell C5.
- Then, select cell D5 and write down the following formula into the cell,
=B5-C5
- Press Enter to get the result.
- After that, select the entire range of cells C5:D5. Then, double-click the Fill Handle icon allocated on the right-bottom side of the cell to copy the formula up to cell D14. Besides it, you can also simply drag the Fill Handle icon up to cell D14.
- Finally, you will get the date and time in separate columns.
Thus, we can say that our function worked perfectly and we are able to separate date and time using a formula in Excel.
4. Separate Date and Time Using ROUNDDOWN Function
In this approach, we are going to use the ROUNDDOWN function to separate the date and time from one column to two columns. Our dataset is in the range of cells B5:B14 and the result will be in columns C and D. The steps of this procedure are given as follows:
📌 Steps:
- At first, select the entire range of cells C5:C14.
- Then in the Number group, select the drop-down arrow of that box and choose the Short Date option.
- In cell C5, write down the following formula-
=ROUNDDOWN(B5,0)
- Now, press the Enter You will see the date will separate and show in cell C5.
- After that, select cell D5 and write down the following formula into the cell:
=B5-C5
- Again, press Enter on your keyboard to get the result for this column.
- Now, select the entire range of cells C5:D5. Then, double-click the Fill Handle icon to copy the formula up to cell D14.
- Finally, you will get the date and time in separate columns.
At last, we can say that our function worked smoothly and we are able to separate date and time using a formula in Excel.
Read More: How to Separate Date and Time in Excel without Formula (3 Methods)
3 Alternative Ways to Separate Date and Time in Excel
Besides these approaches described above, there are three alternative processes to separate date and time. For that are using the same dataset. So, our dataset is in the range of cells B5:B14. We are going to use 3 distinct methods without a formula to separate the date and time and place them into columns C and D.
1. Utilizing Text to Columns Feature
Through this process, we can completely separate the date and time, which are currently showing as a string. In this case, we don’t have to use any formula like the previous methods. The method is described below step by step:
📌 Steps:
- At the beginning of this approach, select the entire data list in the range of cells B5:B14.
- Now, go to the Data tab.
- In the Data Tools group, select Text to Columns.
- A dialog box, entitled Convert Text to Columns Wizard, will appear.
- Then, choose the Delimited option and click the Next button.
- After that, choose the Delimiters as Space and click the Next button.
- Keep the data type in General and change the Destination of the data from $B$5 to $C$5.
- Finally, click Finish to complete the process.
- As we write down the heading of our dataset before completing the process, Excel may give you a warning like an image shown below.
- Ignore the warning and click OK.
- You will see the date and time placed separately in those columns.
So, using this process, we can separate date and time in two different columns in Excel.
2. Split Date and Time Using Flash Fill in Excel
In this case, we will separate the date and time by Excel’s built-in Flash Fill feature. It is the easiest process to separate the date and time. You don’t need to memorize any formula to do this method. Our dataset is in the range of cells B5:B14. The result will be in columns C and D. The process is explained below:
📌 Steps:
- At first, select cell C5.
- Manually write down the date shown in cell B5.
- Now, double-click on the Fill Handle As a result, the value will copy up to cell C14.
- Then, click on the small Auto Fill Options icon at the right-bottom of cell C5.
- After that, choose the Flash Fill option.
- You will see that within a second the entire range of cells C5:C14 is showing the same date as in the range of cells B5:B14.
- Similarly, follow this procedure for the column Time also.
- In the end, you will get the date and time separately.
Finally, we can say that our method worked successfully and we are able to separate date and time in Excel.
3. Separate Date and Time Through Power Query
We can also separate date and time in Excel by using the Power Query method. The process will provide us with the same result as before. The steps of the approach are given as follows:
📌 Steps:
- For starting the process, first, select the range of cells B4:B14.
- After that, go to the Data tab and select the From Table/Range option from the Get & Transform Data group. You can also press ‘Ctrl+T’ to create the table.
- A small dialog box will appear entitled Creat Table. Click on My table has headers and finally click the OK button.
- Another dialog box called Power Query Editor will appear.
- In this box, select the small icon on the left side of the column heading.
- Then, choose the Date/Time option.
- Now, select the entire Date & Time column.
- In the Add Column tab, click on the drop-down arrow of the Date. Then click on Date Only.
- The date of this column will show separately.
- Again, select the entire Date & Time column and click Time > Time Only.
- Now, the time will show in a separate column.
- Finally, click on the Close Button sign in the right corner of that dialog box. Another small box will appear and click on Keep.
- You will find a new sheet that opens in the sheet tab entitled Table.
- Click on the sheet Table and you will get the result.
Thus, we can say that our working approach worked effectively and we are able to separate the date and time in Excel.
Conclusion
That’s the end of this article. I hope that this will be helpful for you and you will be able to separate date and time using formula or without formula in an Excel worksheet. If you have any further queries or recommendations, please share them with us in the comments section below.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
