Whys is TODAY(-90) returning values for future dates
I am trying to sum the # of PTO hours an employee has used in the last 90 days for some reason my formula =SUMIFS({Missed Hours}, {TM}, $Name@row, {Type}, PTO$1, {Date}, >=TODAY(-90), {Cancel}, 0) is tallying all of the time off requested even for future dates. I would expect the value returned to be 56 however it is returning the total of 168.
Thanks In Advance for any help provided!
Answers
-
Hey @Jchadwick
When looking 'at the last' period of time, one has to cap the date range with TODAY. You are really saying, give me anything from timeX, in your case 90 days, up until today.
=SUMIFS({Missed Hours}, {TM}, $Name@row, {Type}, PTO$1, {Date}, AND(>=TODAY(-90), <=TODAY()),{Cancel}, 0)
Will this work for you?
Kelly
-
This gives me an "Invalid Argument" Error.
Admittedly I very new to Smartsheet and the way formulas work with in.
-
Hey @Jchadwick
My bad. Within an AND function the @cell must be used
=SUMIFS({Missed Hours}, {TM}, $Name@row, {Type}, PTO$1, {Date}, AND(@cell>=TODAY(-90), @cell<=TODAY()),{Cancel}, 0)
Help Article Resources
Categories
Check out the Formula Handbook template!