Whys is TODAY(-90) returning values for future dates

Jchadwick

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!

Kelly Moore

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

Jchadwick

This gives me an "Invalid Argument" Error.

Admittedly I very new to Smartsheet and the way formulas work with in.

Kelly Moore

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)