What formulas to use in a timesheet solution to SUM up values

SteCoxy ✭✭✭✭✭✭

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

  • SteCoxy
    SteCoxy ✭✭✭✭✭✭
    Answer ✓

    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..

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You have some misplaced parenthesis. Try copy/pasting the working formula and only update the sheet summary field references.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!