Records 30 days or Older

Options
Sherry Fox
Sherry Fox ✭✭✭✭✭✭
edited 10/26/22 in Formulas and Functions

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


Sherry Fox

Business System Data Analyst

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Best Answer

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @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

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    edited 10/26/22
    Options

    @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.


    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @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

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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.

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @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.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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")

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

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

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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!

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!