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

Hi @jg124
Have a look at the method I developed in this post. i think that could work for you usecase as well.
What do you think?
Would that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
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.
Answers

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

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

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.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

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

Hi @jg124
Have a look at the method I developed in this post. i think that could work for you usecase as well.
What do you think?
Would that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
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.

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

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
Categories
Check out the Formula Handbook template!