Countifs with Today Function

Krista Coffman

Hi!

My team and I are having a hard time with some of our formulas. We need a count for how many projects that are ending in 30 days and as well as 90 days. Our currently formulas are not providing accurate numbers based off our what our filters are showing.

Complete next 30 days formula: =COUNTIFS({HR Projects - Complete Date}, >=TODAY(-1), {HR Projects - Complete Date}, <=TODAY(30))

Complete next 90 days formula: =COUNTIFS({HR Projects - Complete Date}, <=TODAY(1), {HR Projects - Complete Date},

Any help would be greatly appreciated!

Jeff Reisman

@Krista Coffman

Can you share screenshots of the filter criteria as well as what counts you are getting for the filters vs the formulas?

Krista Coffman

Hi Jeff,

Here you go.

There's 65 for 90 days and 37 for 30 days.

Jeff Reisman

@Krista Coffman The 30-day formula is counting everything from yesterday, today, and then 30 days past today. >= TODAY(-1) means greater than or equal to yesterday. If you want this to be comparable to your filter, you need to change it not to include today either, but only the next 30 days AFTER today:

=COUNTIFS({HR Projects - Complete Date}, >TODAY(0), {HR Projects - Complete Date}, <=TODAY(30))

The exact same formula is used for 90 days, just change the 30 to 90:

=COUNTIFS({HR Projects - Complete Date}, >TODAY(0), {HR Projects - Complete Date}, <=TODAY(90))

Krista Coffman

Thank you Jeff!