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/