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

Find more posts tagged with

Accepted answers

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.

All comments

dojones

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.