Return time from date and time string
This tutorial shows how to extract only the time from a date and time string using an Excel formula, with the TIMEVALUE and MID functions
|
=TIMEVALUE(MID(B5,13,8))
=TIMEVALUE(MID(B5,C5,D5))
|
GENERIC FORMULA
=TIMEVALUE(MID(date_time,start_num,num_chars))
ARGUMENTS GENERIC FORMULA
=TIMEVALUE(MID(date_time,start_num,num_chars))
ARGUMENTS EXPLANATION This formula uses the TIMEVALUE and MID functions to extract only the time from a date and time string.
The MID function is used to extract the relevant number of characters from the date and time string that only represent the time starting at a specific position. Please note that for the MID function to work for this purpose the date and time string need to be in text format. In this example we have used the "dd mmm yyyy hh:mm:ss" text format. Given that the first character of the time in this string starts in position 13 and can have a total of 8 characters (hh:mm:ss) in the date and time string this is why we have selected 13 as the start number and 8 as the number of characters to be extracted. The TIMEVALUE function is then used to convert this text string into an Excel time.
Click on either the Hard Coded or Cell Reference button to view the formula that has the start position and number of characters to be extracted from the date and time string directly entered into the formula or referenced to specific cells. |
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return date from date and time string | How to extract only the date from a date and time string | |
| Return days remaining between two dates | How to return days remaining between two dates | |
| Return days remaining as of today | How to return days remaining as of today |
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| MID Function | The Excel MID function returns the specified number of characters from a selected string, starting at a specified position |