Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
Connect with and learn from others in the QuickBooks Community.
Join nowHello,
I have noticed that when I export a Payroll Hours report from QB Pro Plus desktop 2022 to Excel, the hours sometimes are rounded incorrectly. It sometimes shaves off a minute.
I have even cleared out the formula, seen above, and just summed the columns and it is still incorrect. When viewing the report in QB, or exported as a PDF, I do not have this problem. Does anyone know how to fix this?
Solved! Go to Solution.
This issue is actually caused by rounding the time totals to 5 decimal places. QuickBooks shouldn't be adding the ROUND function in the case of time totals, because time is stored in Excel with far more fidelity than 5 decimal places, and since the formatting of the cells as time will hide any small fractions for display in any case.
Date/time values are actually stored as numbers in Excel, where one day = 1.0 and so one second = 0.0000115740740740741 (which is 1/86,400, 86,400 being the number of seconds in a day). In order for the expected totals to be right every time, that fidelity is required - down to the second.
You can see the issue if you add seconds to the time format of the cells. Because the two daily totals are rounded to 5 decimal places, their sum isn't 15 minutes of the hour, but instead 14 minutes and 59 seconds:
This can be seen in more detail if you re-format the cells as decimals and include a lot of fidelity. After doing that, and adding a second total row with no rounding, you can see how much time is lost in the process of rounding, in this case 0.00000666666666:
As it turns out, 0.00000666666666 is more than a 1/2 of a second, and so the sum of the time is one second short on your report from QuickBooks, because Excel can only represent time to the second. And so when formatting the results without seconds - as on the original report - it appears you're one minute short, when you're really only one second short.
Here's a table demonstrating how seconds, minutes, and even hours cannot be accurately represented in Excel using 5 decimal places. An hour, for example, requires 16 decimal places, a minute 17 and a second 18:
And so, the only ways to fix this are:
1) Remove all of the ROUND functions from the totals on the reports.
2) Change all of the ROUND functions to round to 18 decimal places.
Either way, then you will get the correct result when the totals are themselves summed.
Here's a straightforward example of the problem created when rounding time to 5 decimals. Here, the table contains 24 rows, each with one hour entered. In the first column, hours are entered precisely as an hour, and the second column uses the ROUND function on each row, rounding the value in the first column to 5 decimal places. The result on each row looks the same, but it is not. And, so, when the rows are totaled at the bottom the rounded column has an extra seven seconds. You can see why this happens in the second set of columns, where the same internal values are entered, but they're formatted as decimals. On each row, the time is rounded, in this case up, from 0.0416666666666667 to 0.04167, and so the total includes an extra .00008, which converts to seven seconds:
RE: Thank you for such a great explanation, illustrations and all.
You're welcome!
RE: I am now left trying to figure out a way to "Mass-Replace" all rounding functions in my spreadsheet. It would take entirely too long to do this manually.
Use Excel's Find & Replace feature to update the rounding formula's fidelity:
Welcome to the Community.
Let me guide you in the right direction to fix rounding errors when exporting payroll hours from QuickBooks Desktop (QBDT) to Excel.
Since the payroll hours appear to be exact when viewing from QBDT or exporting as a PDF, it's likely that the issue is with Excel itself. That said, I recommend consulting with an Excel specialist to help you overcome the rounding errors.
Additionally, you can have a more organized and comprehensive perspective of your companies' financial performance by merging reports using QBDT, a third-party program, or Excel.
You can always swing by the Community if you have follow-up questions about exporting reports from QBDT. I'll be more than glad to assist you further. Have a nice day ahead!
This issue is actually caused by rounding the time totals to 5 decimal places. QuickBooks shouldn't be adding the ROUND function in the case of time totals, because time is stored in Excel with far more fidelity than 5 decimal places, and since the formatting of the cells as time will hide any small fractions for display in any case.
Date/time values are actually stored as numbers in Excel, where one day = 1.0 and so one second = 0.0000115740740740741 (which is 1/86,400, 86,400 being the number of seconds in a day). In order for the expected totals to be right every time, that fidelity is required - down to the second.
You can see the issue if you add seconds to the time format of the cells. Because the two daily totals are rounded to 5 decimal places, their sum isn't 15 minutes of the hour, but instead 14 minutes and 59 seconds:
This can be seen in more detail if you re-format the cells as decimals and include a lot of fidelity. After doing that, and adding a second total row with no rounding, you can see how much time is lost in the process of rounding, in this case 0.00000666666666:
As it turns out, 0.00000666666666 is more than a 1/2 of a second, and so the sum of the time is one second short on your report from QuickBooks, because Excel can only represent time to the second. And so when formatting the results without seconds - as on the original report - it appears you're one minute short, when you're really only one second short.
Here's a table demonstrating how seconds, minutes, and even hours cannot be accurately represented in Excel using 5 decimal places. An hour, for example, requires 16 decimal places, a minute 17 and a second 18:
And so, the only ways to fix this are:
1) Remove all of the ROUND functions from the totals on the reports.
2) Change all of the ROUND functions to round to 18 decimal places.
Either way, then you will get the correct result when the totals are themselves summed.
Here's a straightforward example of the problem created when rounding time to 5 decimals. Here, the table contains 24 rows, each with one hour entered. In the first column, hours are entered precisely as an hour, and the second column uses the ROUND function on each row, rounding the value in the first column to 5 decimal places. The result on each row looks the same, but it is not. And, so, when the rows are totaled at the bottom the rounded column has an extra seven seconds. You can see why this happens in the second set of columns, where the same internal values are entered, but they're formatted as decimals. On each row, the time is rounded, in this case up, from 0.0416666666666667 to 0.04167, and so the total includes an extra .00008, which converts to seven seconds:
@JaeAnnC RE: Since the payroll hours appear to be exact when viewing from QBDT or exporting as a PDF, it's likely that the issue is with Excel itself.
No, it's with QuickBooks because QuickBooks is rather stupidly rounding the time totals, and so losing the fidelity required to sum those totals and get the correct answer.
RE: That said, I recommend consulting with an Excel specialist to help you overcome the rounding errors.
Good idea. Answer provided. You're welcome.
Now, how about you do your part and get Intuit to fix QuickBooks?
Wow! Thank you for such a great explanation, illustrations and all. I am now left trying to figure out a way to "Mass-Replace" all rounding functions in my spreadsheet. It would take entirely too long to do this manually.
I did look into changing this export setting in QB and, of course, this cannot be changed from QB end.
RE: Thank you for such a great explanation, illustrations and all.
You're welcome!
RE: I am now left trying to figure out a way to "Mass-Replace" all rounding functions in my spreadsheet. It would take entirely too long to do this manually.
Use Excel's Find & Replace feature to update the rounding formula's fidelity:
You have clicked a link to a site outside of the QuickBooks or ProFile Communities. By clicking "Continue", you will leave the community and be taken to that site instead.
For more information visit our Security Center or to report suspicious websites you can contact us here