TODAY function NOT working.

Options
edited 05/16/23

Hi All,

Someone help me with this.

I have 4 columns, TODAY, THIS WEEK, LAST WEEK and THIS MONTH…

I am trying to count all the rows in a certain sheet that are THIS WEEK and marked "Ordered".

=COUNTIFS({INTAKE SHEET 1 DATE}, TODAY(7), {INTAKE SHEET 1 STATUS}, HAS(@cell, "Ordered"))

My issue - this formula (when changing the parameters) is working for ALL my other columns except this week. So for last week I have the -7 in the today column, in the today it is blank just the ().

Why is it NOT working for this week? It pulls 0 results in.. and I AM LOST. Please help..

• ✭✭✭✭✭✭
Options

Your current formula is only going to pull rows where the date is more than 7 days in the future. To get it to pull fr the next seven days, you would need something more like this:

=COUNTIFS({Minerva Doses - NAV Range 1}, AND(@cell >= TODAY(), @cell <= TODAY(7)), {Minerva Doses - NAV Range 3}, HAS(@cell, "Ordered"))

• ✭✭✭✭✭✭
Options

The formula you have is only look at 7 days ago and not the days in between. You would need to add a >= infront of TODAY(7)

• Options

Hi Holly Green,

While this helped me to get ALL the doses for my other formulas - it is NOT helping me get this 1 formula to work.

I have it as: =COUNTIFS({Minerva Doses - NAV Range 1}, >=TODAY(7), {Minerva Doses - NAV Range 3}, HAS(@cell, "Ordered"))

Can you help me figure out why ONLY this one isnt working? There are 13 values that meet the criteria of being within the next 7 days and have a order status of "Ordered".. All my Other formulas are working fine, but this one it is like willfully not finding the data.

Thanks!

• ✭✭✭✭✭✭
Options

You may need to do this where you have ">=TODAY(7)" it is looking for anything 7 days or more from today

To look between today and 7 days out try this:

AND(@cell >= TODAY(), @cell <= TODAY(+7))

Hope this helps!

• ✭✭✭✭✭✭
Options

Your current formula is only going to pull rows where the date is more than 7 days in the future. To get it to pull fr the next seven days, you would need something more like this:

=COUNTIFS({Minerva Doses - NAV Range 1}, AND(@cell >= TODAY(), @cell <= TODAY(7)), {Minerva Doses - NAV Range 3}, HAS(@cell, "Ordered"))

• ✭✭✭✭✭✭
Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!