How to Separate Date and Time Using Formula in Excel (4 Easy Ways)

 

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.

Using INT Function to Separate Date and Time Using Formula in Excel

  • Again, select the range of cells D5:D14. In the Number group, select the drop-down arrow and click on Time.

Using INT Function to Separate Date and Time Using Formula in Excel

  • 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.

Using INT Function to Separate Date and Time Using Formula in Excel

  • 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.

Using INT Function to Separate Date and Time Using Formula in Excel

  • 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.

Using INT Function to Separate Date and Time Using Formula in Excel

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.

Utilizing TEXT Function to Separate Date and Time

  • 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.

Utilizing TEXT Function to Separate Date and Time

  • 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.

Utilizing TEXT Function to Separate Date and Time

  • 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.

Utilizing TEXT Function to Separate Date and Time

So, we can say that our function worked successfully and we are able to separate date and time using a formula in Excel.


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.

Applying TRUNC Function to Separate Date and Time Using Formula in Excel

  • 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.

Applying TRUNC Function to Separate Date and Time Using Formula in Excel

  • Then, select cell D5 and write down the following formula into the cell,

=B5-C5

  • Press Enter to get the result.

Applying TRUNC Function to Separate Date and Time Using Formula in Excel

  • 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.

Applying TRUNC Function to Separate Date and Time Using Formula in Excel

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.

Separate Date and Time Using ROUNDDOWN Function

  • 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.

Separate Date and Time Using ROUNDDOWN Function

  • 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.

Separate Date and Time Using ROUNDDOWN Function

  • 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.

Separate Date and Time Using ROUNDDOWN Function

At last, we can say that our function worked smoothly and we are able to separate date and time using a formula in Excel.


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.

Utilizing Text to Column Command to Separate Date and Time Using Formula in Excel

  • 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.

Utilizing Text to Column Command to Separate Date and Time Using Formula in Excel

  • 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.

Utilizing Text to Column Command to Separate Date and Time Using Formula in Excel

  • 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.

Utilizing Text to Column Command to Separate Date and Time Using Formula in Excel

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.

Split Date and Time Using Flash Fill in Excel

  • 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.

Split Date and Time Using Flash Fill in Excel

  • Similarly, follow this procedure for the column Time also.
  • In the end, you will get the date and time separately.

Split Date and Time Using Flash Fill in Excel

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.

Separate Through Power Query

  • 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.

Separate Through Power Query

  • 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.

Separate Through Power Query

  • The date of this column will show separately.

Separate Through Power Query

  • Again, select the entire Date & Time column and click Time > Time Only.

Separate Through Power Query

  • Now, the time will show in a separate column.

Separate Through Power Query

  • 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.

Separate Through Power Query

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!


Further Readings

Soumik Dutta
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo