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.

**Table of Contents**hide

**Download Practice Workbook**

You can download the workbook and practice with them.

**7 Different Ways to Separate Time Using Formula in Excel**

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

**Read More:** **How to Separate Date from Text in Excel (4 Methods)**

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

**Read More:** **How to Split Date and Time in Excel (8 Easy Methods)**

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

**Read More: How to Separate Date in Excel Using Formula (5 Suitable Ways)**

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

**Read More: How to Separate Date and Time in Excel without Formula (3 Methods)**

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

**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. Or you can have a glance at our other articles in the **ExcelDemy.com** blog!

## Related Article