Records 30 days or Older

Sherry Fox

I am new to Smartsheet stiill. But I need to write a simple formula that calculates the number of records that are

= or greater than 30 days old.

Based on the Initial Escalation Date​ and Today's date

Using Networkdays

The Status must be = Open

Ryan Sides

@Sherry Fox No worries! This is a very active and supportive community. I'm glad you got it working! Ans your setup is spot on. :)

Ryan Sides

@Sherry Fox

Hi Sherry, welcome to the community! See if this one works for you...

=COUNTIFS([Initial Escalation Date]:[Initial Escalation Date], <=WORKDAY(TODAY(), -30))

It doesn't include Networkdays, but it counts back to 30 workdays prior to today's date.

Is that what you were after?

Ryan

Sherry Fox

@Ryan Sides ,

I am not sure why, but when I entered your formula, it showed as Unparsable (I am so much better in Excel! LOL). Also, is there a reason the field is entered twice in the formula??? Plus you formula also doesn't reference the status of Open records.

Ryan Sides

@Sherry Fox

The reason the column name is mentioned twice is because it is a "range". You can tell that by the ":" in between them. Make sure your column name is named "Initial Escalation Date" and is a Date type column.

Then make sure you have a column named "Status" and the below should work.

=COUNTIFS([Initial Escalation Date]:[Initial Escalation Date], <=WORKDAY(TODAY(), -30), Status:Status, = "Open")

Please let me know.

Ryan

Sherry Fox

@Ryan Sides ,

I tried the revised formula, and still the same result of Unparsable. I revised my original post to include a screenshot of my Sheet. I just don't understand why this is happening.

Ryan Sides

@Sherry Fox Feel free to email me at ryan@workflowcreative.com

But there's no reason why it shouldn't be working for you. I set up my sheet exactly as yours and it works ok.

Otherwise, you might want to open a ticket with Smartsheet support to get them to take a look at it.

Sherry Fox

@Ryan Sides ,

It finally worked!!!! This is the formula I used. Thanks for all your help, it is greatly appreciated!!

=COUNTIFS({Initial Escalation Date}, <=WORKDAY(TODAY(), -30), {Status}, ="Open")

Ryan Sides

@Sherry Fox glad to hear it! I didn't realize you were working cross sheets. Enjoy!

Sherry Fox

@Ryan Sides ,

That was my fault for not mentioning it. Like I said, I am new to Smartsheet. FYI: I have all my data in the actual sheet, then I have a Metrics Sheet to handle all calculations. This makes it easier to create dashboards and charts. When I want to add a new chart, the metrics already exist, so it is easy to add the widgets. Thanks again!

Ryan Sides

@Sherry Fox No worries! This is a very active and supportive community. I'm glad you got it working! Ans your setup is spot on. :)