Date Formlulas for Dashboard Counter

Randy Van Winkle

Hello, Trying to set up formulas from a sheet to post to a dashboard, somehow came up short.  I have a number of projects listed in my sheet with a [Go Live] date. I would like to count the following:

• Number of projects that are within 30 days of Go Live (but not past go live)
• Number of projects that are within 7 days of Go Live (but not past go live)
• Number of projects that have already gone live.   

The last one seems to be working with the formula: =COUNTIF([Go Live]:[Go Live], >TODAY())

The other two, are not working as I could not figure the conditions correctly.  Thanks in advance for any help

 

Mike Wilday

Try these... I used Countifs and gave the conditions of less than or = to today, and greater than or equal to Today minus the number of days you were looking for.

Within 30 days of Go live

=Countifs([Go Live]:[Go Live], <=Today(), [Go Live]:[Go Live], >= Today(-30))

Within 7 days of Go Live 

=Countifs([Go Live]:[Go Live], <=Today(), [Go Live]:[Go Live], >= Today(-7))

Randy Van Winkle

The first one is calculating correctly, but the second one is giving a value of 1, but I have two values for 3/1/19 and 1 value for 3/4/19, so it should read a value of 3

Mike Wilday

Sorry!!! Those should be Today(30) and Today(7) respectively. Remove the minus signs and you should get a solid count. 

Randy Van Winkle

Both values changed to 0 when I do that.  Go Live within 30 should read 12 and within 7 should read 3

Mike Wilday

Oh, I see the problem. Try these. With the adjustment of the Today formula I also needed to swap the < and > symbols. 

Within 30 days of Go live

=Countifs([Go Live]:[Go Live], >=Today(), [Go Live]:[Go Live], <= Today(30))

Within 7 days of Go Live 

=Countifs([Go Live]:[Go Live], >=Today(), [Go Live]:[Go Live], <= Today(7))

Randy Van Winkle

That worked! Great, Thanks!

Mike Wilday

Glad I could help!