# How to convert a Duration to a Decimal in Excel?

I have a Duration in this format: `27:40:03`

The cell has custom formatting like this: `[h]:mm:ss`

How do I turn my duration into a decimal of hours? `27.67`

I have tried using `HOUR()` in a calculation but it does not work when the hours are over 24...

Also, if for example, I wanted to edit the minutes to instead be `42`, excel forces me to type the whole duration out again because it changes it to a date. Is there a way around this?

• To turn it into a decimal you could simply multiply the duration with 24 and change the format?
– carl
Commented Jul 1, 2022 at 12:03
• @carl I didn't realise it was that simple! I don't use excel often... How about the second question? Commented Jul 1, 2022 at 12:07

Regarding your first question you have at least two options (assuming your duration is in cell A1): `=A1*24`; and then change the format to e.g., `Number`. The second approach would be to use the built-in function `Convert` as follows: `=CONVERT(A1;"day";"hr")`.
Regarding your second question you also have at least two options: Change 40 to 42 and then change again the format to `[h]:mm:ss`. The second approach, and probably more favourable approach, could be to change your format in the first place to the respective `Time` format. Another way could be to change your format to `[h]:mm:ss;@` and then change from 40 to 42, which converts the expression to a `Time` format anyways.
• @user500665 I think this happens because your date is implicitly stored as `01.01.1900 03:40:03`. To circumvent this issue, highlight the respective cell or columns and go to `Text to Columns` -> `Delimited` -> `Delimiter (Space)`. In this way it is split into a date and a time component. Delete the column with the dates if not needed. If you double-click into the time column afterwards the date should not appear again.