Help with Nested If Function

Jamesbond0864

Hey guys I am working with lease end dates and I am trying to create a nested if statement that says, If the extended lease end date is over 30 days away then "green". If the extended lease end date is less than 30 days away or today then turn "yellow". If the extended lease end date is past todays date then turn "Red". I have the first two correct I believe but I can't seem to get the last part to work. What am I doing wrong here? Any help would be appreciated.

=IF([Extended End Date]@row > today(30), "Green", IF([Extended End Date]@row <= today(30), "Yellow", IF([Extended End Date]@row < today(), "Red")))

I am also trying to include a contingent where the [End Date] shows "N/A" as well and can't get that to work either. I am not sure I have this correct.

=IF([Extended End Date]@row > TODAY(30), "Active", IF([Extended End Date]@row <= TODAY(30), "Almost Expired", IF(AND(Today() - [End Date]@row < "1d", [Extended End Date]@row = "N/A", "Expired")))

Genevieve P.

Hi @Jamesbond0864

IF statements will stop as soon as they find a statement that is "True". This means that in your first formula, you'll never have "Red" since your previous statement is already looking for something that's "Less than TODAY", so all of your ones in the past will be Yellow.

Instead, try putting that Red statement first. Then you can have the Yellow statement second, and then we don't even need to write a Green statement because anything left over will be greater than 30 days from now!

=IF([Extended End Date]@row < TODAY(), "Red", IF([Extended End Date]@row <= TODAY(30), "Yellow", "Green"))

For your next formula, there's a couple of things to note. When you subtract a date from a date you'll receive a number as the output. This means that TODAY() - [End Date]@row will never equal "1d", but instead will simply equal 1.

Today() - [End Date]@row < 1

You will also need to close off the AND() statement before you can tell it to say "Expired".

IF(AND(TODAY() - [End Date]@row < 1, [Extended End Date]@row = "N/A"), "Expired")

Try this, putting your "N/A" statement first:

=IF(AND(TODAY() - [End Date]@row < 1, [Extended End Date]@row = "N/A"), "Expired"), IF([Extended End Date]@row > TODAY(30), "Active", IF([Extended End Date]@row <= TODAY(30), "Almost Expired")))

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Jamesbond0864

IF statements will stop as soon as they find a statement that is "True". This means that in your first formula, you'll never have "Red" since your previous statement is already looking for something that's "Less than TODAY", so all of your ones in the past will be Yellow.

Instead, try putting that Red statement first. Then you can have the Yellow statement second, and then we don't even need to write a Green statement because anything left over will be greater than 30 days from now!

=IF([Extended End Date]@row < TODAY(), "Red", IF([Extended End Date]@row <= TODAY(30), "Yellow", "Green"))

For your next formula, there's a couple of things to note. When you subtract a date from a date you'll receive a number as the output. This means that TODAY() - [End Date]@row will never equal "1d", but instead will simply equal 1.

Today() - [End Date]@row < 1

You will also need to close off the AND() statement before you can tell it to say "Expired".

IF(AND(TODAY() - [End Date]@row < 1, [Extended End Date]@row = "N/A"), "Expired")

Try this, putting your "N/A" statement first:

=IF(AND(TODAY() - [End Date]@row < 1, [Extended End Date]@row = "N/A"), "Expired"), IF([Extended End Date]@row > TODAY(30), "Active", IF([Extended End Date]@row <= TODAY(30), "Almost Expired")))

Let me know if this works for you!

Cheers,

Genevieve

Jamesbond0864

Hey Genevieve,

That worked perfectly! You are a life saver! I messed with that formula for way to long. I guess I need to use them more often. Thank you so much!

Genevieve P.

No problem at all! I'm glad I could help. 🙂