Need help with "WEEKNUMBER" and "TODAY" formulas

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!