Update Request - Accumulated hours formula

Hi All,


Currently I am setting up a tracker for tracking weekly hours spent on requests that come in via a form.



I would like to set up an update request that runs every friday (until task is marked as closed) to the assigned person who will enter the amount hours they spent on the task in the week just gone.



I would like them to enter just a single number for the week, and calculate a running total of the hours spent. However, I cannot think of a formula that would generate a running total of past weeks hours + the additional hours submitted via the update request without creating several columns.



Any help would be much appreciated as always!

Best Answer

Answers

  • RossL
    RossL ✭✭✭✭✭✭

    How is your sheet set up? Can you provide a screen shot?

  • jg124
    jg124 ✭✭✭✭✭

    Hi RossL,


    Sure - see attached. Ideally the 'Total Hours Spent' column will somehow accumulate the total hours worked on the ticket.



    Open to ideas regards adding in additional columns etc


  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 06/19/20

    Hello,

    First, I do not see a column that would take the hours spent for the week. I assume there is one, and I will call it "Hours Spent" for the purpose of this example.

    If you want to get a sum of the total hours spent for the current week and all previous weeks, in "Total Hours Spent" the formula =SUM([Hours Spent]$1:[Hours Spent]@row]. This assumes that you want to get a sum of "Hours Spent" from the 1st row to the current row.

    If you want to get the sum of Hours Spent for only the current week (new entry) and the previous week, this formula would be entered into row 2: =SUM([Hours Spent]1,[Hours Spent]@row)

    Is this what you were looking for? Keep in mind you will want to copy the proper formula into all applicable rows and not put it into your form so that it will autofill upon future entries.

    In the screenshot below "Total Hours (All Time)" is the first formula and "Total Hours (Last 2 Weeks)" is the second formula.



    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • jg124
    jg124 ✭✭✭✭✭

    Hey Dan,


    Apologies I may have explained myself poorly.


    What I was hoping to achieve is to somehow store the previous hours worked and (through an email update request) add on the additional hours worked so a sum value of hours is displayed beside the ticket number all on the one row.


    Your solution while excellent for tracking weekly hours take up a lot of space.


    Is there any potential way to store an old or initial value in Smartsheet and then increment it by means of a number submitted by update request? If not I may have to adopt your solution in another sheet and use cell linking to get a sum of hours.


    Thanks for your help

  • jg124
    jg124 ✭✭✭✭✭

    @Andrée Starå looks like that will work. Will have a crack at it Monday morning, you’re the man !

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @jg124

    Excellent!

    You're more than welcome!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!