Excel Formula: Convert Timestamp to Time
This tutorial introduces a formula to convert timestamp to Excel valid time as above screenshot shown.
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula:
| TIME(MID(timestamp,1,2),MID(timestamp,4,2),MID(timestamp,7,2)) |
Syntaxt and Arguments
| Time: the timestamp that you want to convert to Excel valid time. |
Return Value
It returns values in time format.
How this formula work
For example, to convert the timestamps in cell B3:B6 to Excel time, please use below formula:
| =TIME(MID(B3,1,2),MID(B3,4,2),MID(B3,7,2)) |
Press Enter key, then drag autofill handle down to cell C6.
Then you can format the formula results to a time format you need in the Format Cells dialog.
Explanation
MID function: Find and return a specific number of characters from the middle of given text string.
TIME function: Create Excel time with given hour, minute and second.
Relative Formulas
- Convert Unix Timestamp to Excel Time or Vice Inverse
This tutorial provides formulas to convert between Unix time and Excel time. - Convert Text to Time
Introducing formulas for convert text stored date to Excel standard date. - Convert Time to Decimal Hours
Here provides a formula that converts time to decimal hours in Excel and explains how the formula works. - Convert Time To Decimal Minutes
In this tutorial, it provides a formula for converting time to decimal minutes in Excel.
Relative Functions
- Excel IF Function
Test for specified conditions, then returns the corresponding values - Excel VALUE Function
Convert text to number. - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date. - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel YEAR Function
The YEAR function returns the year based on the given date in a 4-digit serial number format.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
