Nested IF for a status column based on a date column

Lisa

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?

J. Craig Williams

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