Previous Week(s) formula

Steve Hendricks

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).

Andrée Starå

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

✅Did my post 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!

Steve Hendricks

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:

Andrée Starå

Happy to help!

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

Steve Hendricks

@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.

Andrée Starå

@Steve Hendricks

I missed your reply.

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)