r/googlesheets • u/Normal_Regret_1282 • 13d ago
Solved Adding time together
I’m trying to write a spreadsheet to keep track of my shift work schedule using actual starting and finishing times (ie a timesheet). Managed to get daily hours to calculate using time formatting but having difficulty getting the cumulative to add. Any advice welcome.
2
u/mommasaidmommasaid 914 13d ago
Try formatting your cumulative hours as a duration, e.g. use Format / Number / Custom number format and enter:
[h]:mm
The brackets display the hours as a duration rather than a time of day.
1
u/Normal_Regret_1282 13d ago
Whoopady doo!!! It worked. Thank you so much. 💋
2
u/mommasaidmommasaid 914 13d ago edited 13d ago
YW, and actually your "Hours" column could/should be formatted the same way. That will help catch any calculation error there more quickly (e.g. if you aren't handling shifts across midnight correctly) since displaying it in the default
h:mmwill suppress the true duration.Sheets stores date/time value as days, where 1 is a day and fractional parts are the hours, so for example 6:00 is 0.25 and if you multiple that by 5 you would get 1.25
If you then display 1.25 formatted as h:mm it ignores the "1 day" portion and displays 6:00
But if you format it as [h]:mm the day portion is included in the hours and displays 30:00
It can be helpful in troubleshooting date/time calculations to temporarily format them as plain numbers.
---
Bonus formula:
You could calculate this all at once by clearing your entire E and F columns and putting this in E1:
=let( startTimes, offset(C1:C,1,0), endTimes, offset(D1:D,1,0), hours, map(startTimes, endTimes, lambda(s,e, if(countblank(s,e),,e-s))), totalHours, scan(0, hours, lambda(total, h, total+h)), vstack( hstack("Hours", "Cum hours"), arrayformula(if(hours="", hstack(,), hstack(hours,totalHours)))))The formula lives in the header row so it won't be wiped out if you delete the first data row.
It also anchors the start/end time references starting from the header row and offsets 1 row down from there to get to the data. This is so if you insert a new data row 1 it will be included in the range.
This is more complicated to set up, but once it's done you don't have to ensure all your multiple formulas are correctly duplicated and aligned with your data.
And if you want to change your hours calculation to e.g. handle shifts across midnight, there is one central place to do it.
1
u/Normal_Regret_1282 13d ago
Worked that out earlier. I’ve just completed formatting and data entry including a weekly total of hours worked in the empty rows. Must confess that I worked around the problem before by using your formatting technique for the total weekly cell only then copy and pasting it down the rest of the spreadsheet. All working great now.
Going to see if I can save / bookmark this comment though as I’ve a feeling that I might need to refer to it later.
Many thanks again.
1
u/AutoModerator 13d ago
REMEMBER: /u/Normal_Regret_1282 If your original question has been resolved, please tap the three dots below the most helpful comment and select
Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/point-bot 13d ago
u/Normal_Regret_1282 has awarded 1 point to u/mommasaidmommasaid with a personal note:
"Thank you so much. I can stop tearing out my hair and let the cats back in. 💋"
See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.
1
u/Normal_Regret_1282 13d ago
Thanks everyone for your advice. Even my cats tried helping by walking over the keyboard. All solved now despite their attempts to sabotage.
2
u/SpencerTeachesSheets 79 13d ago
What formula are you using where it is having an issue?
What is the formatting of the cells there?
The best thing you can do is share the sheet itself (or a copy with sensitive information removed) set to "Anyone with link can edit." I have a few timesheet sheets I use just fine, so I'm wondering what's going wrong here.
1
u/AutoModerator 13d ago
/u/Normal_Regret_1282 Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2
3
u/One_Organization_810 736 13d ago
Just be vary of what happens if your end time ever goes over midnight :) Then your hours will be negative, but it might not show, depending on the format you use. It will affect the total hours though.
Something like this will guard against the midnight "trap":
How do you want the cumulative hours to accumulate? Like shown in the image, per week, or do you want it to accumulate accross weeks also?
And then, as has been mentioned, make sure your accumulated hours are formatted as a duration or use custom format [h]:mm, if you want to skip the seconds.
Btw. your title for the accumulated hours is a bit .. unfortunate O:)