# Calculate Days an item is Open

Options
✭✭
edited 12/07/23

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)?

• ✭✭✭✭✭✭
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! 🙂

• ✭✭
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?

• ✭✭
Options

Still wrestling with this one. Any other ideas?

• ✭✭✭✭✭✭
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!