Count entries made the previous week

Christopher Pretty

Hi,

I am looking to create a sheet summary formula that will count all entries on a sheet that came from the previous week. I figure that it will have to be a countif formula and will incorporate weeknumber but I am lost on how to count from the previous week and have it keep updating for the next week.

Paul Newcome

Try something like this...

=COUNTIFS([Date Column]:[Date Column], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)

Paul Newcome

@MPath I'm not sure that will work when the current week is the first week.

If

WEEKNUMBER(TODAY()) = 1

then

WEEKNUMBER(TODAY()) - 1 = 0

There is no week number zero. Not to mention during that first week, the year would also need to be -1 (for part of the week). Yearly transitions with WEEKNUMBER can be very tricky to navigate because you could have week number 52 that has two different years in it, week number 53 that has two different years in it, and potentially week number 1 that has two different years in it depending on what day of the week the first of the year falls on.

Using my method above, we ignore all of that and just leverage Mondays. We output the Monday for each of the dates so that every row has a Monday date (of the same week as the original date). Then we count how many of those Mondays are "last week's Monday".

Paul Newcome

Try something like this...

=COUNTIFS([Date Column]:[Date Column], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)

MPath

https://community.smartsheet.com/discussion/comment/309984#Comment_309984

But then next year, won't that same entry pop-up in same weeknr.?

Paul Newcome

@MPath Yes. You would need to use one of the solutions here in the Community that outputs (for example) the Monday of the week of a date. You would drop this into a helper column on the sheet, and then you would adjust the above formula to look at the helper column and compare to "current Monday minus 7".

=[Date Column]@row + (2 - WEEKDAY([Date Column]@row))

=COUNTIFS([Helper Column]:[Helper Column], @cell = TODAY(-7)@row + (2 - WEEKDAY(TODAY(-7))))

MPath

https://community.smartsheet.com/discussion/comment/445044#Comment_445044

or:

Datum:Datum; AND(ISDATE(@cell); WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1; YEAR(@cell) = YEAR(TODAY())))

? Correct, seems to work for me, do nto have data of last year yet, but simulated it.

Paul Newcome

@MPath I'm not sure that will work when the current week is the first week.

If

WEEKNUMBER(TODAY()) = 1

then

WEEKNUMBER(TODAY()) - 1 = 0

There is no week number zero. Not to mention during that first week, the year would also need to be -1 (for part of the week). Yearly transitions with WEEKNUMBER can be very tricky to navigate because you could have week number 52 that has two different years in it, week number 53 that has two different years in it, and potentially week number 1 that has two different years in it depending on what day of the week the first of the year falls on.

Using my method above, we ignore all of that and just leverage Mondays. We output the Monday for each of the dates so that every row has a Monday date (of the same week as the original date). Then we count how many of those Mondays are "last week's Monday".

MPath

https://community.smartsheet.com/discussion/comment/445221#Comment_445221

Arghh! Did not think of that! Guess I'll be changing 50+ formulas again ;) Appreciate it!