Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Nested IF for a status column based on a date column
Hello,
I have a list of employee certifications where the certification name is in one column, the certification expiration date is in another and the status if this the 3rd. I have a column called NETDAYS where I calculate the number of dates in between today's date and the expiration date.
The formula I can currently using calculates whether a certification is:
Active (not expired and not expiring in within the next 30 days)
Expiriing!!! (expiring within the next thirty days)
Pending (expiration date is blank and we're pursuing getting the employee certified)
I want to add a 4th status called "N/A" by using a bogus date in the expiration date column but keeping getting unparseable.
Here's the current formula -
=IF(ISBLANK([Expiration Date]41), "Pending", IF(NETDAYS41 < 0, "Expired", IF(NETDAYS41 < 31, "Expiring in the next 30 days", "Active")))
Can anyone help me?
Comments
-
Lisa,
NETDAYS is a Smartsheet function.
I would suggest changing it (perhaps NET_DAYS) to avoid any confusion (by user or Smartsheet)
To clarify, when you say "bogus date" you mean a non-date - like "bob".
I suspect the error is not in the Status column but in the NETDAYS column.
To fix that, you need to verify the Expiration Date holds a date value.
Use the ISDATE function
Something like
=IF(ISDATE([Expiration Date]41), NETDAYS([Expiration Date]41, TODAY()))
will work
NETDAYS (the column) will be blank if the date column does not have a date in it.
With that change, you should be getting a "Expired" result for "bob".
So let's wrap that with an ISDATE too.
=IF(ISDATE([Expiration Date]4), IF(ISBLANK([Expiration Date]4), "Pending", IF(NETDAYS4 < 0, "Expired", IF(NETDAYS4 < 31, "Expiring in the next 30 days", "Active"))), "N/A")
If there is a date, do what was done before, otherwise, "N/A"
Hope that helps.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives