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.

formula excel


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:


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


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:


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 formula excel

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:

formula excel

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
        • formula excel

Interesting Management