The dataset contains some employees’ office entry details for a particular company date in column B. We want to split the time in column C using different formulas.
Method 1 – Combine TIME, HOUR & MINUTE Functions to Separate Time
STEPS:
- Select the cell where you want to put the formula combining the TIME, Hour & MINUTE functions. We selected cell B5.
- Enter the following formula into that selected cell:
=TIME(HOUR(B5),MINUTE(B5),0)- Press Enter.
- Drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the Plus (+) symbol.
We can 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.
- TIME(HOUR(B5),MINUTE(B5),0): This will return the hour and the minute of a time as we put the second as 0.
Method 2 – Apply TEXT Formula to Split Time in Excel
STEPS:
- Choose the cell (C5) where you want to insert the TEXT functions’ formula.
- Enter the following formula into the selected cell:
=TEXT(B5,"hh:mm:ss AM/PM")- Press Enter.
- Dag the Fill Handle down or Double-click the Plus (+) icon.
You can see the result in column C.
Method 3 – Using the MOD Function For Separating Time in Excel
STEPS:
- Select cell C5 and enter the following formula:
=MOD(B2,1)- Press Enter.
- Drag the Fill Handle downward. To auto-fill the range, double-click on the Plus (+) symbol.
We can see the time separated from the date in cells C5:C10.
Method 4 – Use the INT Formula to Separate Time in Excel
STEPS:
- Select cell C5.
- Enter the following formula into that selected cell:
=B3-INT(B3)- Press Enter.
- Drag the Fill Handle downward to repeat the formula across the range. Double-click on the Plus (+) sign to AutoFill the range.
- This will separate the time in column C.
Method 5 -Using the Excel ROUNDDOWN Function to Split Time
STEPS:
- Choose the cell where you want to put the function to separate the time.
- Enter the following formula into the cell:
=B2-(ROUNDDOWN(B2,0))- Press Enter.
- Drag the Fill Handle to apply the formula across the range. Double-click on the Plus (+) sign to AutoFill the range.
Column C displays the outcome.
Method 6 – Using the QUOTIENT Function to Split Time
STEPS:
- Select the cell where we want to separate the time. We selected cell C5.
- Enter the following formula into the cell:
=B2-(QUOTIENT(B2,1))- Press Enter.
- Drag the Fill Handle to the bottom to reproduce the formula throughout the range. Double-click the Plus (+) sign to AutoFill the range.
We can observe the time separated from the date.
Method 7 – Using the TRUNC Function to Separate Time in Excel
STEPS:
- Select cell C5.
- Enter the following formula into the cell:
=B2-(TRUNC(B2,0))- Press Enter.
The result will now be displayed in the selected cell and the formula in the formula bar.
- Drag the Fill Handle down to duplicate the formula across the range. Alternatively, to AutoFill the range, double-click the Plus (+) symbol.
- Column C shows only the time as a result.
Things to Keep in Mind
Note that whenever you put a time in a cell, changing the cell format to Time would be better. 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:
- Select the cells that contain the time value.
- Go to the Home tab from the ribbon.
- Under the Number category, you can see a drop-down menu bar selected General by default.
- Click on the drop-down menu bar and select Time.
Download the Practice Workbook
You can download the workbook and practice.
<< Go Back to Date and Time | Split | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
