Calculating Days Remaining and Past Due

Hello everyone,

I am trying to calculate the days remaining and days past due for each row, however, I want it to stop count if the status is closed.

As you can see we have a "Days Remaining/Days Past Due" column and those closed (the grayed out rows) still calculate a number which is what I do not want.

I tried doing it myself but although it counts the days past due, it's not counting the days remaining.

We have various statuses other than open and in-progress. We have a total of 9 different statuses. Just for context.

Please help. Thank you.

Find more posts tagged with

Formulas

Accepted answers

Mike TV

@Syllica

Thanks for the info. Does this work for you then?

=IF(Status@row = "Closed", "", [Due Date]@row - TODAY())

All comments

Mike TV

@Syllica

What designates that a status is closed? At first I thought it was simply just having a date in the "Date of Closure" column but the first row has one and it's row isn't gray. If the first row is closed and not being gray is just an anomaly, then does this work?

=IF(ISBLANK([Date of Closure]@row), NETDAYS(TODAY(), [Due Date]@row), "")

Syllica

https://community.smartsheet.com/discussion/comment/330190#Comment_330190

Hi Mike :)

Thank you for your response. We manually change the status to "Closed". We conditionally formatted for a row to change to gray and green font when it's closed. The statuses are a drop-down list. As you can see one row we have as "Awaiting-Marketing" and this is one of 10 statuses you can choose from.

I also want to note that you can see that although we have a row as "Closed" it is still calculating days and I do not want that because counting days is no longer necessary for a closed request. I would like the days to stop being counted. I want it to keep count as long it is not "Closed" in the status column.

Currently the formula that sits in the days remaining/past due column is =[Due Date]@row - TODAY()

Hopefully that gives you more context.

Mike TV

@Syllica

Thanks for the info. Does this work for you then?

=IF(Status@row = "Closed", "", [Due Date]@row - TODAY())

Syllica

https://community.smartsheet.com/discussion/comment/330410#Comment_330410

😮 This is perfect and did the job! Thank you so much for your help :)

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.