Formula to count active projects if dates are not equal?

KathrynEK

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

dojones

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.

dojones

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.

KathrynEK

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.

dojones

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