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:
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
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: