Records 30 days or Older
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 Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
Best Answer
-
@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. :)
Answers
-
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
-
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 Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @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
-
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 Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
-
@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.
-
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 Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
-
@Sherry Fox glad to hear it! I didn't realize you were working cross sheets. Enjoy!
-
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 Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
-
@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. :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!