Calculate Days an item is Open

Options
HarryS
HarryS ✭✭
edited 12/07/23 in Formulas and Functions

Hi everyone!

We use a sheet to capture new request forms from end users. When a form is submitted, the 'date created' is captured on the main sheet. Is it possible for Smartsheet to calculate the days an item has been in the "open" state?

i.e. A new form submitted on 10/1/23(date created) and is still open. How many days has the request been open (as of today)?

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @HarryS,

    You can certainly do this with a simple formula:

    =IF(Status@row = "Open", (TODAY() - [Date Created]@row))

    If you had another column with the closed date then you can modify this to show how many days things were open for:

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

    Hope this helps or at least points you in the right direction, but if you've any problems/queries then just post! 🙂

  • HarryS
    HarryS ✭✭
    Options

    Thank you!

    I've been tinkering with the formula to fit my need but have been unsuccessful. Lol

    I've tried: =IF(Request Status@row = "In Progress", (TODAY() - [Created Date]@row)) but it just shows a blank cell.

    ('Request Status' is the column used to view the status of requests, and 'Created Date' is the name of the column where the form submission date is captured.)

    Any more ideas?

  • HarryS
    HarryS ✭✭
    Options

    Still wrestling with this one. Any other ideas?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @HarryS

    For the row where you tried

    =IF([Request Status]@row = "In Progress", (TODAY() - [Created Date]@row))

    is the status "In Progress"? If the status is not 'In Progress' then a blank cell is the correct value.

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!