Formula to count active projects if dates are not equal?
For project tracking we have an original end date and an actual end date. At the beginning of a project the dates would be equal, if there is slippage and the date is different we would update actual end date to the new end date. We have a dashboard with a widget that shows the count of projects where the end date changed.
I have a the below formula, however I'm getting a return value of 0 and I can see end date changes for active projects.
=COUNTIFS({Status live}, "Active", {End Date Live}, <>{OG End Date Live})
I feel like maybe it is setting issue on my tracking sheet. Both of the columns are date values, set up as a date column and I believe restricted to dates only. I have my formula in a metric sheet so it can feed the dashboard
Thanks
Best Answer
-
Apparently Smartsheet doesn't compare a date column to another date column outside of the sheet. Here is a workaround. Put in another column (Date Difference) to calculate the difference between End Date Live and OG End Date Live. If they are the same, it will return 0. Then do a count on Date Difference. I would do it in the summary sheet to avoid using a separate sheet. Then pull to the dashboard from the summary sheet.
Answers
-
Apparently Smartsheet doesn't compare a date column to another date column outside of the sheet. Here is a workaround. Put in another column (Date Difference) to calculate the difference between End Date Live and OG End Date Live. If they are the same, it will return 0. Then do a count on Date Difference. I would do it in the summary sheet to avoid using a separate sheet. Then pull to the dashboard from the summary sheet.
-
hmmm ok. Trying to avoid adding additional columns to an already large sheet. I was testing a theory, so seems like we stick with the current way. Seems silly that you can't compare dates outside of the sheet.
-
KathrynEK
I agree. I tried several things and couldn't get it to work either. Even in the summary, it wouldn't yield a result. Other option is contact Smartsheet support if you are on a paid plan. Good Luck
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!