Tracking changes to a Column within last week

davidmartin95826

Hello,

 

I'm trying to find a way to count the number of items which have been updated to a 'complete' status within the last week. I have 10 sheets and they all have a status column (not started, started, complete etc.).

I've tried using the 'modified date' column to determine if the status was updated, although I'm aware the 'modified date' system column updates whenever there is any change to the row, and not a specific column within a row.

I can create a baseline of the 'Complete' statuses for each sheet and then deduct previous weeks count of 'Complete' to calculate the variance, although I would like to find a way to automate, as the other data points in the dashboard are all automated.

Ideally there is a way of pulling an automated report which could tell me the amount of 'complete' status between a certain period.

Thanks for your help,

David

Andrée Starå

Hi David,

One way could be to use a third-party service like Zapier to automate it.

All rows that are marked completed could be copied to another sheet and then used for calculation.

Would that work? Is that an option?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

davidmartin95826

Hi Andree,

Thanks for your help.

I've set up a Zap on Zapier which is populating a Sheet on Smartsheet with any status that changes to 'Complete'.

I've created the Formula below to calculate the amount of 'Complete' statuses within the last 7 days.

=COUNTIFS({Sheet}, "Complete", {Sheet}, <TODAY(+7))

In the Dashboard I have a figure for 'Complete' in the last 7 days and 'previous week'. I'm struggling to create a formula for 7 days before last week.

Thanks,

David

Andrée Starå

Excellent!

Happy to help!

Try something like this.

=COUNTIFS({Sheet}; "Complete"; {Sheet}; WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 2)

The same version but with the below changes for your and others convenience.

=COUNTIFS({Sheet}, "Complete", {Sheet}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 2)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?



davidmartin95826

Hi Andree,

 

Thanks for the quick reply.

I'm not sure if the formula above will work as I want to be able to calculate the previous week and week prior to that automatically without adding a weeknumber / static cell. I hope I've interpreted your formula correctly.

I've been trying to update below, although I can't seem to get it to work.

 

=COUNTIFS({Sheet - Range 1}, "Complete", {Sheet - Range 2}, (TODAY() - 14 <=-7)

I'm trying to calculate the amount of completed tasks between 7 days and 14 days ago.

Thanks for your help,

David

Andrée Starå

Happy to help!

You won't need a weeknumber cell. The formula looks at the date and pulls the weeknumber.

Make sense?

davidmartin95826

It worked Thanks again for your help. I really appreciate it.

Andrée Starå

Excellent!

Happy to help!