Countif for last week during new year turnover

Caroline Olson

Hello, I have a formula running to calculate how many ride alongs our team completed last week. During the first week of the new year, the formula no longer works correctly and I am having trouble trying to fix it so that it pulls last week (last week of 2023). I have this formula running on a different sheet than where the data for the ride alongs lives so the formula I use references the date column on the ride along sheet.

The formula I am currently using is =COUNTIFS({Sales Ride date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)

I know this formula will start working again the 2nd week of the year, but any suggestions on how I can permanently fix this formula so that during the new year turnover I don't continue to have this issue?

Thank you!

StevenBlackburnMBA

The problem here is you are counting by week number. Did this system start functioning this year? If so, what day? Per the formula, you would've had the same problem last year unless this system started mid-year. You could also change the way you calculate this by day, and the utilize the to transition data and items for 2024 on the exact 366th day.

If you started week 1 on January 1, 2023, then your formula should be accurate from my understanding.