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

Options
Lisa
Lisa ✭✭✭
edited 12/09/19 in Archived 2016 Posts

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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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

     

     

     

     

     

This discussion has been closed.