Fix Excel Time Difference and Hourly Rate Errors in WPS Spreadsheets
Question details
The user needs to convert a time difference into decimal hours and calculate the correct payment using an hourly rate.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is calculating pay for a duration of 04:40 minus 02:00 at an hourly rate of £22.
- Observed behavior
- The expected result is approximately £58.67, but a result such as £58.74 indicates an incorrect input, formula, or rounding calculation.
Check that the start and end values are stored as time values and that the hourly rate is entered as a numeric value, not text. Confirm whether the result should display elapsed time or decimal hours before entering the formula.
Convert the time difference to decimal hours before calculating pay
Use this method when the hourly rate is based on decimal hours rather than hours-and-minutes formatting.
Spreadsheet time values are stored as fractions of a 24-hour day. Multiplying the time difference by 24 converts it to decimal hours. For example, 04:40 minus 02:00 equals 2.6667 hours, and 2.6667 multiplied by £22 equals £58.67 after rounding.
In WPS Spreadsheets, enter the start time in A1 as 02:00 and the end time in B1 as 04:40. Use the Home tab's number format controls to apply a Time format if necessary.
Select C1 and enter =(B1-A1)*24, then press Enter. The result should be approximately 2.6667.
Enter 22 in D1 as the hourly rate, or enter £22 and confirm the cell is recognized as a number or currency value.
Select E1 and enter =C1*D1, then press Enter. The unrounded result should be approximately £58.6667.
For a two-decimal payment result, enter =ROUND((B1-A1)*24*D1,2) in E1, then choose Currency from the Home tab's Number Format menu. The displayed result should be £58.67.
Check the source values and formula when the result is incorrect
Use this troubleshooting method when the spreadsheet returns £58.74 or another value that does not match the expected payment.
Calculate Accurate Time-Based Pay in WPS Spreadsheets
WPS Spreadsheets lets you calculate elapsed time, convert it to decimal hours, apply an hourly rate, and format the result as rounded currency. Its familiar spreadsheet interface supports Excel workbooks, so you can correct the formula without rebuilding your worksheet.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing the time and rate values.
- 2. Enter the decimal-hour formula: In an empty cell, enter =(end_time-start_time)*24, replacing the cell references with the cells that contain the end and start times.
- 3. Apply the hourly rate: Multiply the decimal-hour result by the rate cell, using a formula such as =C1*D1.
- 4. Round and format the payment: Use =ROUND((B1-A1)*24*D1,2), then select Currency from the Home tab to display the payment correctly.
Frequently Asked Questions
Why must a time difference be multiplied by 24?
WPS Spreadsheets stores one full day as 1, so one hour is 1/24. Multiplying =(B1-A1) by 24 converts the elapsed time from a fraction of a day into decimal hours.
What is the correct pay for 04:40 minus 02:00 at £22 per hour?
The difference is 2 hours 40 minutes, or 2.6667 decimal hours. Multiplying by £22 gives £58.6667, which rounds to £58.67.
Why does my WPS spreadsheet show £58.74 instead of £58.67?
Check that the time cells contain exactly 04:40 and 02:00, that the formula uses the end time minus the start time, and that the duration is multiplied by 24. Also verify that the hourly rate is exactly 22 and that no hidden decimals are present.
How can I display the result as currency in WPS Spreadsheets?
Select the result cell, open the Home tab, and choose Currency from the Number Format options. Use =ROUND((B1-A1)*24*D1,2) if you need exactly two decimal places.
Can this formula handle a shift that passes midnight?
Yes, use =MOD(B1-A1,1)*24 when the end time may be on the following day. This prevents a negative result when the end time is earlier on the clock than the start time.
