Date Formlulas for Dashboard Counter
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
Comments
-
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))
-
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
-
Sorry!!! Those should be Today(30) and Today(7) respectively. Remove the minus signs and you should get a solid count.
-
Both values changed to 0 when I do that. Go Live within 30 should read 12 and within 7 should read 3
-
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))
-
That worked! Great, Thanks!
-
Glad I could help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!