## Convert a decimal number into hour/minute/second with a formula in an excel report

In some companies I worked for, it was allowed to do some extra hours and after people from my team including me could enjoy it as a day off for instance. The point is that my HR department sent me the file but the number was in decimal and not in time so for each member, I needed to convert it in hour, taking advantage, I will show how to convert it in minutes and seconds.

When I use the formula ?

Each time I need to convert a decimal number into time.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How are the formulas ?

For hours, divide the cell by 24, in cell E3, I put this formula:

=D3/24

For minutes, divide the cell by 1440 (24 x 60), in cell F3, I put this formula:

=D3/1440

In the other hand, if I want to convert minutes (decimal number) into hours, I will use the same formula.

For seconds, divide the cell by 86400 (24 x 60 x 60), in cell G3, I put this formula:

=D3/86400

And if I want to convert seconds (decimal number) into minutes, I will use the same formula too.

As you can see with my example, the result is still in decimal number

So I have to format the cells but not using the default one because if I am using the “time” option, I will get this:

As you can see on the cell E4, the hour is not correct, I mean, it is not showing more than 24h. To fix it:

• 1.Right click on the cell
• 2. Select “format cells”
• 3. Select “custom” and in the “type” field, put:
• [hh]:mm:ss