MULTIPLE IF FORMULA AROUND DATES

Fialko66

Hi: Can anyone help with a Multiple If formula which i cannot get to work please?

I have a Valid To Culumn with a date in it and have a Status Column i want toi flag as CURRENT, EXPIRING or EXPIRED depending on the date. I had CURRENT & EXPIRED working but when i try to include the additional IF to show as EXPIRING if the Valid To date is within the next 30 days it all goes to pot. This is the formula i have messed around with

=IF([VALID TO DATE]@row > TODAY(30), "EXPIRING", IF([VALID TO DATE]@row >= TODAY(), "CURRENT", "EXPIRED"))

Thank you

Nick Korna

Hi @Fialko66,

Try this:

=IF(AND([Valid To Date]@row < TODAY(30), [Valid To Date]@row > TODAY()), "EXPIRING", IF([Valid To Date]@row >= TODAY(), "CURRENT", "EXPIRED"))

Sample:

Hope this helps - if I've misunderstood something or you've problems/questions then just post! 🙂

Nick Korna

Hi @Fialko66,

Try this:

=IF(AND([Valid To Date]@row < TODAY(30), [Valid To Date]@row > TODAY()), "EXPIRING", IF([Valid To Date]@row >= TODAY(), "CURRENT", "EXPIRED"))

Sample:

Hope this helps - if I've misunderstood something or you've problems/questions then just post! 🙂

Fialko66

Thank you so much - ive messed around with this for ages - works perfectly :-)

Fialko66

Hi - Could anyone help me again please?

I got help with the formula above and it worked fine but then i've tried to adapt it to cover another scenario in a different date column - so it first looks at the start date column and if that date is not yet due then the column says not yet valid - if the date is today or past due then the formula above kicks in.

I have formulas for bothn working but when i stick them together (as i would have worked them out in Excel) then i get an error message again

Below is both fomulas and how i have tried to join them.

=IF([VALID FROM DATE]@row > TODAY(), "NOT YET VALID", "ADD NEXT PART OF FORMULA HERE")

=IF(AND([VALID TO DATE]@row < TODAY(30), [VALID TO DATE]@row > TODAY()), "EXPIRING", IF([VALID TO DATE]@row >= TODAY(), "CURRENT", "EXPIRED"))

=IF([VALID FROM DATE]@row > TODAY(), "NOT YET VALID”, IF(AND([VALID TO DATE]@row < TODAY(30), [VALID TO DATE]@row > TODAY()), "EXPIRING", IF([VALID TO DATE]@row >= TODAY(), "CURRENT", "EXPIRED")))