How to Convert Total Time to Tenths of an Hour on a Spreadsheet
A professional service firm bills a client by multiplying the time each employee works on the client by the employee's hourly rate and summing the results. Most firms convert a fraction of an hour into tenths of an hour. For example, five hours and 36 minutes becomes 5.6 hours. If you have a spreadsheet with the total time in minutes that each employee spent on a client, you can convert it to tenths of an hour using a spreadsheet such as Excel.
Launch Excel and open the spreadsheet containing the total time worked. For example, the spreadsheet might have column headers in the first row of Client Name in A1, Employee Name in B1, Hourly Rate in C1, "Total Time (Minutes)" in D1, "Time (Hours and Tenths)" in E1 and "$ Charge" in F1.
Click in cell E2 and enter a formula to convert the total minutes into hours and tenths of an hour. Gradually build the formula one piece at a time by separating the logic into steps.
Use the MOD function to get the number of minutes remaining after you convert the minutes to hours. Type "=MOD(D1, 60)" (without quotes here and throughout).
Divide the fractional portion of an hour by 60 minutes to get a decimal representation. Type "=MOD(D1, 60) /60".
Use the ROUND function to express that number as a tenth of an hour. Type "=ROUND(MOD(D1, 60)/60, 1)".
Use the "FLOOR" function to get the number of full hours and add that to the fractional portion to calculate the final result. Type "=FLOOR(D1, 1)+ROUND(MOD(D1, 60)/60, 1)".
Complete the spreadsheet by computing the charge for each line. Use the ROUND function to round to the nearest $100. Click in cell F1 and type "=ROUND(C1 * E1, -2)".