I need to convert number of hours, such as
156:54 in excel 2003.
All of the conversions I found did this incorrectly. The number of hours shouldn't be changed, only the decimal.
Where A1 is the decimal value.
Be aware that dates and times in Excel are stored internally as an amount of days. You can use this fact to simplify conversions of time amounts.
If you have an amount of hours in A1 you can calculate an amount of days using
For more information about custom formats, see Create or delete a custom number format and Number format codes (look at the last section "Displaying hours, minutes, and seconds"). The brackets
You can use this kind of method to convert other amounts of time (amounts of seconds, minutes, or hours) into display formats like
I find this method easier to remember and re-create as needed. The disadvantage is if other people need to edit your spreadsheet and they change the number format or copy and Paste Special, Values to a new location, the values no longer make sense.
The easiest way to do this is to use @Bavi_H 's method, but instead wrap the formula in a
That way you don't need to rely on setting and maintaining number formats for the cell. And even though the value is stored as text, it can still be used in calculations (e.g.