# Previous Week(s) formula

I have a running list of dates in one column and hours spent in another. I'm trying to capture the sum of hours spent over the past week, the week prior and so forth.

I'm using the following formula to sum the hours spent not over the previous 7 days, but 7 - 14 days ago and it appears to be working.

SUMIFS(Total:Total, Date:Date, @cell >= (TODAY(+7)) - 14)

I would expect then that changing the "Today" to ...(TODAY(+14)) - 21 would provide a sum of the hours spent during the days 14 - 21 ago from today. This is returning the same number as (TODAY(+7)) - 14).

• ✭✭✭✭✭✭
edited 04/16/20

Hi Steve,

Here's an example if you want to show the week. Is that what you need, or do you want to display days starting from today? You can change the -0 in at the end to minus one to show the previous week and so on.

Try something like this.

=SUMIFS(Total:Total; Date:Date; WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 0)

The same version but with the below changes for convenience.

=SUMIFS(Total:Total, Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 0)

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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.

• I really appreciate the help; I'm trying to sum of hours worked over a span of days.

Something like this:

Cell 1: return a sum of hours from 4/8 thru Today (4/15).

Cell 2: sum of hours from 4/1 - 4/8

Cell 3: sum of hours from 3/25 - 4/1

Here is a screenshot of my sheet:

• ✭✭✭✭✭✭

Happy to help!

Can you share the screenshots again because they aren't showing up for me?

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å Thank you for any assistance....really really simple sheet, just wanting to provide a snapshot of hours spent last week, and each previous week, not a running sum.

Thank you.

• ✭✭✭✭✭✭

Do you still need help?

If yes, Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

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!