Jun 3, 2024
SUM duration being calculated incorrectly
I've tried ctrl and selecting cells one at a time to see where the problem is. Starting from D2 and working down, it calculates it correctly until D18 (21:31) but at D19 the 'total' drops to 1:01. Starting from D25 and working upwards, it calculates correctly until D14 (20:10) but at D13 the SUM is calculated as 1:15.
As for the Pivot table calculating total duration as 16:46... don't even get me started!
Apologies in advance if I'm being a massive idiot and there's a really simple answer to this. I'm totally stumped, though, and getting really frustrated.
Apologies in advance if I'm being a massive idiot and there's a really simple answer to this. I'm totally stumped, though, and getting really frustrated.
Many thanks,
Here's the sheet: https://docs.google.com/spreadsheets/d/1hIzuD-_fdBD63RQYqPDGA_2ecyG_3QP47iCl4I7VtnQ/edit#gid=0
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
All Replies (2)
Jun 3, 2024
Hi Dan Hobson 4044,
When i use =SUM(D:D) and format as duration i get the value of 36:56:00
I cannot see where in your file you are doing the SUM which gives incorrect result.
For further help, please give a clear description of where (which sheet and which cells) are giving the iccorect SUM result, and set the Share settings so that Anyone with the link has Editor rights.
Steps to share a file:
- Open the files you need help with.
- Go to the menu File > 🗗 Make a copy.
- Open the new copy of the files and replace sensitive information with similar placeholder data.
- Either, go to the menu File > Share > Share with others or click the Share button at the top right of the files.
- Once the sharing window opens, under General access, switch Restricted to Anyone with the link.
- Once the setting refreshes, change Viewer to Editor.
- Click Done.
- Share the sample file/s links, right here in your post in the forum.
Please let us know once you've done this, then we'll take another look at the issue.
Jun 3, 2024
Here are some screenshots which hopefully demonstrate what I was saying above.
Many thanks,
1 = all duration cells highlighted and giving wrong sum
2 = D2 - D18 correct sum
3 = D2 - D19 wrong sum
Last edited Jun 3, 2024
Jun 3, 2024
The documentation makes no mention of any such limitation.
I have escalated this to Google for investigation.
For collective management, other enquiries describing the same type of issue will be merged to this one.
⚠️ ATTENTION (For anyone who has encountered this issue) ⚠️
The volunteers in this forum are not always able to help with troubleshooting issues like this, so this issue has been escalated to Google, and the Google development/engineering team is very aware of the situation.
- Please click I have the same question, and 👍 Upvote.
- Please, also click Subscribe if you want to be notified of updates.
- Google developers/engineers do NOT read these forums, so posting a"me too" or "+1" reply only result in a waste of time.
- The issues reported in the OPENING ENQUIRY post are tracked by views and upvotes, not by the number of "me too" or "+1" replies.
- Please only post a reply that provides some detailed information that can help identify the cause and lead to a solution to the problem. (eg. device brand/type, type of operating system, operating system version numbers, browser/software version numbers, solutions you have discovered, etc).
- Updates will be posted AFTER Google developers/engineers report the issue is fixed or that a fix is being rolled out.
Jun 3, 2024
=map(B2:B,C2:C,lambda(b,c,if(or(b="",c=""),,query(c-b,"format Col1 '[h]:mm:ss'"))))
Your pivot table range is set to
Last edited Jun 3, 2024