If you want to convert a number to time values, you need some additional operations, multiplying and dividing between days, hours, minutes, and seconds. Here, we're going to take a look at a simpler approach by using Excel's TEXT function.
Syntax
=TEXT(numeric value / 24, "[h] ""hours,"" m ""minutes, "" s ""seconds""")
Steps
- Begin by typing in =TEXT(
- Select or type in the range reference that contains numeric value you want to convert B3,
- Divide the numeric value by 24
- Type in the format code that includes h, m and s placeholders "[h] ""hours,"" m ""minutes, "" s ""seconds"""
- Close the formula with ) and press Enter to complete it.
How
Excel's TEXT function converts numerical values to text values by applying a formatting. That formatting logic works the the same way as number formatting, with which you add $ sign or decimal digits. Almost all number formatting options are supported by TEXT function as well.
Excel keeps date time values as numbers and assumes that the date 1/1/1900 is equal to 1, then adds 1 for every next single day. For example; 5/18/2018 is equal to 43238. While days are counted as integers, time values get decimal part. For example; 1 is equal to 24 hours, 0.5 is equal to 12 hours, and 0.001 is equal to 1 minute and 26 seconds.
Formula
Because the integer part of numbers represent the day value, the first step is converting the initial value from day base to hour base. This can be done by dividing the value by 24. This is the only arithmetic operation we need.
B3/24
Now we have the numeric value in hours. Excel has predefined placeholders for every part of a time value: h, m and s represent hour, minutes and seconds respectively. You can also use rich text for these parameters (i.e. hours, minutes and seconds). Remember to enter string values inside quotes.
"[h] ""hours,"" m ""minutes, "" s ""seconds"""
Note that quotes are used multiple times to tell Excel that, in those instances they are not being used for closing another quote, but they are special characters.
Finally, we enclosed hour (h) value withing square brackets to make sure values exceed 24 hours. Otherwise, hour value will start form 0 for every 24 hours, which means that values like 1.5 or 4.5 will return return 30 minutes.
=TEXT(B3/24,"[h] ""hours,"" m ""minutes, "" s ""seconds""")
For more information about number formatting options and possibilities see: https://www.spreadsheetweb.com/number-formatting-excel/
