What formulas to use in a timesheet solution to SUM up values
Hello, I have used one of the great solutions that has been posted by, I believe, @Paul Newcome relating to tracking and working out time/duration of hours in a HH:MM format. There are 2 columns to achieve this - the first uses a formula to work out the numerical duration: =((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)
The second column then converts the number in the first column into HH:MM format using this formula:
=INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60
It is currently 1 mastersheet timesheet, with the intention of either using filters or individual reports for each staff member and a webform to add in the hours of what they've been working on. The team is an editorial team, who need to track the length of time that they've been editing a particular piece of content.
I've added in an additional couple of columns to suit the workflow of the team. What they also need is to summarise the totals on a monthly basis for each editor: overall hours total, then broken down into "editing" overall hours total and "marketing" overall hours total.
Editing tasks are defined by 4 values in the Delivery column: Ad Sales, Distribution, Editorial and News Channel.
Marketing tasks are defined by these 3 values: GN Marketing, GN Marketing Affiliate and Yahoo.
The first bit I'm struggling with is how to calculate the totals for Editing tasks and Marketing tasks. I know how to achieve this with 1 value: =SUMIF(Delivery:Delivery, "Editorial", SUM:SUM), for example, but how do I get the formula to add up multiple values too?
The second bit I'm struggling with is how to achieve SUM totals - I used a totals/summary row (indicated in grey) and was able to do this through the SUM function button but because I need a breakdown into 2 different types of tasks, as mentioned above, I think it would be better to do this in the sheet summary.
I've done a screenshot of the sheet for reference below:
Then here's a screenshot of the sheet summary:
All advice and guidance would be much appreciated, as formulas are not my area of expertise!
Best Answers
-
Hi Paul, this worked spot on for the Overall Total Hours (HH:MM) field. The other 2 fields are slightly out:
For the Overall Total Editing Hours (HH:MM) field, I used the following formula: =INT([Editing Total Hours]#) + ":" + IF([Editing Total Hours]# - INT([Editing Total Hours]#) * 60 < 10, "0") + ([Editing Total Hours]# - INT([Editing Total Hours]#) * 60)
For the Overall Total Marketing Hours (HH:MM) field, I used the following formula: =INT([Marketing Total Hours]#) + ":" + IF([Marketing Total Hours]# - INT([Marketing Total Hours]#) * 60 < 10, "0") + ([Marketing Total Hours]# - INT([Marketing Total Hours]#) * 60)
Again, I may have mistyped here..
-
You have some misplaced parenthesis. Try copy/pasting the working formula and only update the sheet summary field references.
Answers
-
Just to add to this, I did originally use the Project Management settings to create a different proof of concept, where instead of having a Start Time and End Time column with HH:MM format, I used a Duration column. This allowed you to type in 4h, 2.5h, 2.25h, for example, which I could then easily use a SUMIf formula to calculate a numerical total. The downside to this is was it calculated the total in days rather than HH:MM. Would there be a formula to convert and calculate the durations into HH:MM?
Any ideas on if this would be a better solution than the first one I listed in my first post?
-
For the first bit you would want something like this...
=SUMIFS(SUM:SUM, Delivery:Delivery, OR(@cell = "Ad Sales", @cell = "Distribution", @cell = "Editorial", @cell = "News Channel"))
-
Thank you Paul! This has worked in the sheet summary perfectly for the Editing values but it doesn't seem to be adding up the dummy values properly for the Marketing values...
It's returning 8, when the total with those particular dummy values should be 9.43333 (8 + 1.43333).
-
Exactly what is the formula you are using to sum the Marketing values? Can you provide a screenshot of the sheet with a filter applied to show those rows that should be summed?
-
Paul ignore me.. user error!
I used the formula you provided for the Editorial values and then applied it to the Marketing values.. I'd clearly mistyped in my first attempt. I've just retyped in the values and it's giving me the correct value now :-) so problem 1 is all resolved.
If possible, I now need to convert the numerical total hours into its HH:MM value figure. The formula I used isn't quite right - I think I'm missing something. I need to do this in fields 2, 4 and 6.
-
Using your original formula for conversion
=INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60
You would replace each instance of "SUM@row" with a reference to the appropriate sheet summary field. For example:
=INT([SUM of Total Hours]#) + ":" + IF(([SUM of Total Hours]# - INT([SUM of Total Hours]#)) * 60 < 10, "0") + ([SUM of Total Hours]# - INT([SUM of Total Hours]#)) * 60
-
Hi Paul, this worked spot on for the Overall Total Hours (HH:MM) field. The other 2 fields are slightly out:
For the Overall Total Editing Hours (HH:MM) field, I used the following formula: =INT([Editing Total Hours]#) + ":" + IF([Editing Total Hours]# - INT([Editing Total Hours]#) * 60 < 10, "0") + ([Editing Total Hours]# - INT([Editing Total Hours]#) * 60)
For the Overall Total Marketing Hours (HH:MM) field, I used the following formula: =INT([Marketing Total Hours]#) + ":" + IF([Marketing Total Hours]# - INT([Marketing Total Hours]#) * 60 < 10, "0") + ([Marketing Total Hours]# - INT([Marketing Total Hours]#) * 60)
Again, I may have mistyped here..
-
You have some misplaced parenthesis. Try copy/pasting the working formula and only update the sheet summary field references.
-
Paul you have made my day with your help and support here! Cracked it and it's all now working thank you so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 422 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
