Need help with "WEEKNUMBER" and "TODAY" formulas

ilene_healy68056

Hi Community,

I am having a hard time getting my "WEEKNUMBER" and "TODAY" formulas to work and I am hoping someone can point out what my mistake is.  

I have created a metrics sheet to count the number of action items due over then next 6 weeks. I'd like the sheet to show rolling weeks ahead. For example: Current Week, Current Week +1, Current Week +2, Current Week +3, Current Week +4, Current Week +5.  

I'd like to get a count the total action items for where the "Due Date" falls within the current Weeknumber (ie, 01-June is Week #23). When I filter my action log, I should get a count of 8. But my formula is returning 0.

=COUNTIFS({Action Log - Digital Logistics Status}, <>"Full", {Action Log - Digital Logistics Due Date}, WEEKNUMBER(TODAY()))

Next, I'd like to count the total action items for where the "Due Date" falls into next week's Weeknumber (ie, 12-June is Week #24). And so on and so on. Again, my formula is returning a big fat 0.

=COUNTIFS({Action Log - Digital Logistics Status}, <>"Full", {Action Log - Digital Logistics Due Date}, WEEKNUMBER(TODAY()) + 1)

Once I get these grand total formulas to work, I will add in the people for "Assigned To" so I can see who has how many action items due for current week, plus the next 5 weeks out.

example:

Count of actions due in the Current week for Axel:  

=COUNTIFS({Action Log - Digital Logistics Status}, <>"Full", {Action Log - Digital Logistics Due Date}, WEEKNUMBER(TODAY()), {Action Log - Digital Logistics Assigned To}, FIND("Axel Hartman", @cell) > 0)

Count of actions due in the Next week for Axel:

=COUNTIFS({Action Log - Digital Logistics Status}, <>"Full", {Action Log - Digital Logistics Due Date}, WEEKNUMBER(TODAY() + 1), {Action Log - Digital Logistics Assigned To}, FIND("Axel Hartman", @cell) > 0)

Please can anyone see what I have done wrong? Thank you so much for your help...I really appreciate it. 

Ilene

Paul Newcome

Try changing your date criteria to

WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())

=COUNTIFS({Action Log - Digital Logistics Status}, <>"Full", {Action Log - Digital Logistics Due Date}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))

ilene_healy68056

That worked perfectly! Thank you!! 😁

My formula for the future weeks looks like this and it is matching perfectly to the referenced action log worksheet

Current week count:

=COUNTIFS({Action Log - Digital Logistics Status}, <>"Full", {Action Log - Digital Logistics Due Date}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))

Future Week Counts:

=COUNTIFS({Action Log - Digital Logistics Status}, <>"Full", {Action Log - Digital Logistics Due Date}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) + 1)

Thanks

Ilene

Paul Newcome

Happy to help! 👍️

Nick Embry

@Paul Newcome Hey Paul, I have been scouring these posts looking for an answer for a formula. I am trying to calculate how many times a criteria appears by week.

Basically, I want to know weekly how many times the initial status was Category change and submitted to the clinician in a week. I need to track this data in a dashboard weekly, going back to 2/19/2020.

I hope you can help me!

Nick