Update Request - Accumulated hours formula

jg124

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!

Andrée Starå

Hi @jg124

Have a look at the method I developed in this post. i think that could work for you use-case as well.

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

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!

RossL

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

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

requests.png

SoS | Dan Palenchar

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.

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

Andrée Starå

Hi @jg124

Have a look at the method I developed in this post. i think that could work for you use-case as well.

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

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!

jg124

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

Andrée Starå

@jg124

Excellent!

You're more than welcome!