Working with Microsoft Excel, improve the approaches to recognize and handle everyday challenges. Excel includes a large quantity of information that allows us to combine dates and times. Sometimes, it may be necessary for us to divide them at times. Excel includes numerous tools and functionalities for separating the date and time. In this article, we mainly look at the ways to separate time using formulas in Excel. We will demonstrate every possible Excel formula to separate time.
How to Separate Time Using Formula in Excel: 7 Different Ways
In our daily lives, time is quite important. We may develop a healthy practice of planning and managing our normal tasks by using time. Sometimes, we need to record only the time. For separating the time using the formula we are going to use the following dataset. The dataset contains some employees’ office entry details on a particular date of a company in column B. Now, we want to split the time in column C using different formulas.
1. Combine TIME, HOUR & MINUTE Functions to Separate Time
The TIME function is a built-in Excel function categorized under the Date/Time Function. It constructs a legal Excel time from the specified hour, minute, and second values. The HOUR function in Excel returns a value between 0 and 23 as the hour part of a time. The MINUTE function in Excel returns a value between 0 and 59 as the minute value of a time. We can combine the TIME, HOUR, and MINUTE functions to separate time. For this, we need to follow the procedures down.
STEPS:
- Firstly, select the cell where you want to put the formula combining the TIME, Hour & MINUTE functions. So, we select cell B5.
- Secondly, put the formula into that selected cell.
=TIME(HOUR(B5),MINUTE(B5),0)- Thirdly, press Enter.
- Now, drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the Plus (+) symbol.
- Finally, we are able to see only the time separated from the date in the cell range C5:C10.
🔎 How Does the Formula Work?
- MINUTE(B5): This will extract only the minutes from a date and time.
- HOUR(B5): This will extract the hour from the date of time.
- TIME(HOUR(B5),MINUTE(B5),0): This will return the hour and the minute of a time as we put the second as 0.
2. Apply TEXT Formula to Split Time in Excel
The TEXT function allows you to modify the appearance of a number by using format codes to apply the format to it. We can use this function to separate the time from a date. Let’s see the steps below.
STEPS:
- To begin with, choose the cell (C5) where you want to insert the TEXT functions’ formula.
- Secondly, type the formula below into the selected cell.
=TEXT(B5,"hh:mm:ss AM/PM")- Further, press the Enter key to finish the procedure.
- Furthermore, to copy the formula over the range, drag the Fill Handle down or Double-click on the Plus (+) icon.
- And, that’s it! Lastly, you can see the result in column C.
3. Using the MOD Function For Separating Time in Excel
The MOD function in Excel gives the residual of two values after division. We can manipulate the function to separate the time. Let’s look at the procedures to use the function to separate time in excel.
STEPS:
- Similarly, as in the previous method, select cell C5 and substitute the formula.
=MOD(B2,1)- Then, press Enter. And the formula will show in the formula bar.
- Further, to replicate the formula throughout the range, drag the Fill Handle downward. To AutoFill the range, double-click on the Plus (+) symbol.
- Finally, we can see the time separated from the date in cells C5:C10.
4. Use INT Formula to Separate Time in Excel
The INT function in Excel will return the integer component of a decimal value by decimal digits to the integers. But this function has much use. We can use this function to separate the time in Excel. Let’s follow the steps for this.
STEPS:
- First, select cell C5.
- Second, enter the formula into that selected cell.
=B3-INT(B3)- Third, press the Enter key on your keyboard.
- Then, drag the Fill Handle downward to repeat the formula across the range. Double-click on the Plus (+) sign to AutoFill the range.
- Finally, this will separate the time in column C.
5. Excel ROUNDDOWN Function to Split Time
The ROUNDDOWN function in Excel produces a value that has been rounded down to a specified number of decimal places. We can utilize this function to separate time in Excel. Let’s see the procedures for this.
STEPS:
- To begin with, choose the cell where you want to put the function to separate the time.
- Then, enter the following formula there.
=B2-(ROUNDDOWN(B2,0))- After that, to complete the operation hit the Enter key.
- Further, drag the Fill Handle downward to apply the formula across the range. Double-click on the Plus (+) sign to AutoFill the range.
- That concludes the method. Column C displays the outcome.
6. The QUOTIENT Function for Splitting Time
One of the arithmetic and mathematics functions is the QUOTIENT function. It’s used to get the fractional part of a division. It can also be used to separate the time. Let’s see the procedures for this.
STEPS:
- In the first place, select the cell where we want to separate the time. So, we select cell C5.
- Secondly, type the formula into the cell we have selected.
=B2-(QUOTIENT(B2,1))- Then, hit Enter to see the result.
- After that, drag the Fill Handle to the bottom to reproduce the formula throughout the whole range. Double-click the Plus (+) sign to AutoFill the range.
- Finally, in the cell range C5:C10, we can only observe the time separated from the date.
7. Utilize TRUNC Function to Separate Time in Excel
The TRUNC function in Excel produces a shortened number with an optional numerical value. The fractional component of the number is removed using TRUNC. With this function, we can separate the time. Now, we will see the steps to use this function to separate the time.
STEPS:
- Likewise the previous methods, first, select cell C5.
- Then, in that selected cell, type in the formula below.
=B2-(TRUNC(B2,0))- Further, hit the Enter key to complete the process.
- The result will now display in the selected cell, along with the formula in the formula bar.
- Furthermore, drag the Fill Handle down to duplicate the formula across the range. Alternatively, to AutoFill the range, double-click the Plus (+) symbol.
- That’s everything for now! Last but not least, column C shows only the time as a result.
Things to Keep in Mind
Note that, whenever you put a time in a cell, it would be better to change the cell format to Time. Sometimes, excel does that work automatically. But if the cells that contain time are not formatted as Time, you may see an error. To change the format, follow the below process.
STEPS:
- Firstly, select the cells that contain the time value.
- Then, go to the Home tab from the ribbon.
- After that, under the Number category, you will be able to see a drop-down menu bar selected General by default.
- Further, click on the drop-down menu bar and select Time.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will assist you in Separate Time Using a Formula in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.
<< Go Back to Date and Time | Split | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
