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 No worries! This is a very active and supportive community. I'm glad you got it working! Ans your setup is spot on. :)
@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 ,
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.
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.
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 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 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!
ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.
