Converting a decimal number into hours and minutes poses a recurring problem in payroll management, project tracking, or data analysis. The decimal format (7.75) and the sexagesimal format (7h45) are not manipulated in the same way, and a conversion error can distort a payslip or an invoice. This guide details the calculation mechanisms, common pitfalls, and applicable methods in a spreadsheet, a script, or simple mental calculation.
Why base 60 complicates the conversion of a number into hours
The time system is based on base 60 (sexagesimal system): one hour contains 60 minutes, one minute contains 60 seconds. The numbers we handle daily work in base 10. This incompatibility explains why a simple copy-paste between two Excel columns produces absurd results.
You may also like : Discover how to quickly find a job through online listings
Let’s take a concrete case. A time tracking software displays 8.50 as the working duration. This is not 8 hours and 50 minutes, but 8 hours and 30 minutes, because 0.50 hour corresponds to half of 60 minutes. Confusing the decimal part and minutes is the most frequent source of error in payroll.
For those who want to convert a number into hours on Sparh, the logic remains the same: isolate the integer part (the hours), then convert the decimal part into minutes by multiplying it by 60.
Read also : How to Donate Furniture to a Charitable Organization: Guide and Practical Tips
Conversion from decimal hours to hours and minutes: the manual method
The procedure consists of two steps, and it works in all cases, without a spreadsheet.
- Separate the integer part of the number: it directly gives the hours. For 6.75, the integer part is 6.
- Multiply the decimal part by 60 to obtain the minutes. Here, 0.75 x 60 = 45. The result is therefore 6h45.
- If the result of the minutes itself has a decimal (for example, 0.83 x 60 = 49.8), multiply the remaining decimal part by 60 to obtain the seconds: 0.8 x 60 = 48 seconds.
This method poses a practical trap. Values like 0.33 (one-third of an hour, or 20 minutes) give 0.33 x 60 = 19.8 minutes. Rounding is essential to avoid results like 19 minutes and 48 seconds when the required precision stops at the minute.
Inverse conversion: minutes to decimal
The return path is just as common, especially for entering data into payroll software that expects a decimal format. We divide the minutes by 60. Thus, 7h45 becomes 7 + (45/60) = 7.75.
Payslips often present hours in decimal format known as “base 100”, which simply means that the part after the decimal point represents a fraction of an hour, not minutes. This vocabulary point generates persistent confusion among managers who are new to the subject.
Excel formula and format errors in spreadsheets
In Excel or Google Sheets, the display format of a cell changes how a number appears without altering its actual value. A cell containing 0.75 displayed in “hh:mm” format will show 18:00, because Excel interprets 0.75 as 75% of a 24-hour day (which is 18 hours). This is not 0h45.
The value 1 in Excel represents exactly 24 hours. To convert a decimal number of hours into time format, you must divide by 24. The basic formula is simple: if A1 contains 8.5 (decimal hours), the result cell should display =A1/24, formatted as [h]:mm.
Exceeding the 24-hour limit
The standard hh:mm format resets the counter after 24 hours. For cumulative durations (weekly total, project duration), the [h]:mm format keeps the hours beyond 24. Without the brackets, a total of 37 hours displays as 13:00, which distorts the result.
The TIME() function in Excel also caps at 24 hours. For higher values, the formula using INT() and MOD() (or a division by 24 with [h]:mm format) remains the only reliable approach.
Rounding rules in payroll and invoicing: a choice that impacts amounts
In recent years, time tracking software publishers have emphasized the need to standardize rounding rules before any conversion. Rounding to the quarter hour, half hour, or minute does not produce the same totals over a month of work.
Three rounding levels coexist in practice:
- To the nearest minute: maximum precision, but payslips become difficult to read and rounding discrepancies accumulate over seconds.
- To the quarter hour (0.25 h): a common standard in construction and catering. A duration of 2h08 is rounded to 2h00 or 2h15 depending on the chosen rule (rounding down, up, or to the nearest).
- To the half hour (0.50 h): used in some consulting firms for client billing.
The choice of the rounding rule must be documented and consistent within the same organization, as a change during the period can create discrepancies in billing or remuneration that are difficult to justify.
Conversion of seconds to hours: a technical use case
Beyond payroll, converting seconds into h:m:s format also concerns developers analyzing server logs or measuring application performance. A raw timestamp in seconds (for example, 9,472 seconds) is hard to read without conversion.
The method follows the same logic in reverse. First, divide by 3,600 to get the hours, then take the remainder (modulo 3,600) and divide by 60 for the minutes. The final remainder gives the seconds. Rounding functions (round, floor) are recommended in scripts to avoid spurious values like 59.999 minutes due to floating-point errors.
Converting a number into hours involves a simple mechanism (multiplying or dividing by 60), but errors almost always occur in the same place: confusion between decimal and sexagesimal, inappropriate cell format, or undefined rounding rule. Setting these three parameters before starting a calculation eliminates the vast majority of problems.