Hi expert,



I have a cell e.g I14 in format [h]:mm:ss and it is showing 178:50:34.

I want to convert it so that the number of hour (178) multiply by 10. Which should be equal to 1780.

So, i type a formula in another cell which is :
=HOUR(I14)*10
However, it show 2400:00:00 and the format of this cell is [h]:mm:ss which is not my expected.

Please advice?

Regards
Grace

Hi Grace,

The problem occurs as a combination of the format and the way the function works.

The Excel HOUR function will read/convert your time entry (178:50:34) as (1900-01-07 10:50:34) and return a result of 10 hours because that's how many hours are in the current day for that time entry. Then, Excel will assume you want to continue using your format of "[h]:mm:ss", and convert the 10 to 240 because that's how many hours are in 10 days.

The solution will depend on what you intend to do with your result (1780 hours). Do you want Excel to treat this as a number or as a length of time? If you just want the number, then change the formula to this:

And set the format for that cell to "General".

Cheers,