Days Open if Task Status is Closed

I am new to Smartsheets and I am trying to show how long a task has been open since the project Start Date, but, if the task is marked as "closed", I want the "Days Open" column to record the Date Completed minus Start date, but if task is still Open or In progress, I want the "Days Open" column to record the number of days from start till today.

I have a formula which can calculate the days since the task starts, but of course it continues counting the days even after the task is marked closed. Please help.

=TODAY() - [Start Date]@row

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @Chanoya Ellis

The way I would do this is to set up a helper "Closed Date" column which would be a date-type of column. Then I'd set up a Workflow that automatically records the date when your task status changes to "Closed". (See: Set the Current Date with Record a Date Action).

Now that there's a date in the sheet which identifies when the task is closed, you can use this in your formula. You could say, check if there's a date in this column and if there is, calculate the time between this closed date and the Start Date. Otherwise, see how many days there are between Today and the start date.

Like so:

=IF([Closed Date]@row <> "", [Closed Date]@row - [Start Date]@row, TODAY() - [Start Date]@row)

Does this make sense? Let me know if you need help with the workflow set up!

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Chanoya Ellis

Like so:

=IF([Closed Date]@row <> "", [Closed Date]@row - [Start Date]@row, TODAY() - [Start Date]@row)

Does this make sense? Let me know if you need help with the workflow set up!

Cheers,

Genevieve

Chanoya Ellis

@Genevieve P

Thanks this worked. I had previously set up the work flow for the "Closed Date", so I simply plugged in your formula and it worked perfectly!

Genevieve P.

Hi @Chanoya Ellis

Great! I'm glad that worked for you. 🙂

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.