# Change to formula

✭✭✭✭✭
edited 12/09/19

Hi,

I currently have a formula that looks at a date range and outs in auto test in a different cell.

=IF(NETDAYS(TODAY(), [Expiry Date]2) < 0, "Due Now", IF(NETDAYS(TODAY(), [Expiry Date]2) < 30, "Due in Next Month", IF(NETDAYS(TODAY(), [Expiry Date]2) < 90, "Due in Next 3 Months", IF(NETDAYS(TODAY(), [Expiry Date]2) < 180, "Due in Next 6 Months", IF(NETDAYS(TODAY(), [Expiry Date]2) < 365, "Due in Next 12 Months", "Not Due or Other")))))

I would like to add to the above formula the following

if the date is 01/01/1900 "Periodic"

Can this be done?

• ✭✭✭✭✭✭

=IF([Expiry Date]2 = DATE(1900, 1, 1), "Periodic", IF(NETDAYS(TODAY(), [Expiry Date]2) < 0, "Due Now", IF(NETDAYS(TODAY(), [Expiry Date]2) < 30, "Due in Next Month", IF(NETDAYS(TODAY(), [Expiry Date]2) < 90, "Due in Next 3 Months", IF(NETDAYS(TODAY(), [Expiry Date]2) < 180, "Due in Next 6 Months", IF(NETDAYS(TODAY(), [Expiry Date]2) < 365, "Due in Next 12 Months", "Not Due or Other"))))))

Give the above a try. When referencing a specific date in a formula, you need to use the DATE function.

=DATE(yyyy, mm, dd)

• ✭✭✭✭✭

Thanks Paul, the formula worked.

• ✭✭✭✭✭✭
• ✭✭✭✭✭

Sorry Paul, can i ask one more question...what formula would I need to include if the expiry date is blank to say "other" as at the moment it says "#Invalid data type" which is correct but I would like to replace that with the above wording. I did try to apply a format that if "Lease Due" contained the above wording change font colour to white but that did not work.

• ✭✭✭✭✭✭

Sure thing!

=IF(ISBLANK([Expiry Date]2), "", IF([Expiry Date]2 = DATE(1900, 1, 1), "Periodic", IF(NETDAYS(TODAY(), [Expiry Date]2) < 0, "Due Now", IF(NETDAYS(TODAY(), [Expiry Date]2) < 30, "Due in Next Month", IF(NETDAYS(TODAY(), [Expiry Date]2) < 90, "Due in Next 3 Months", IF(NETDAYS(TODAY(), [Expiry Date]2) < 180, "Due in Next 6 Months", IF(NETDAYS(TODAY(), [Expiry Date]2) < 365, "Due in Next 12 Months", "Not Due or Other")))))))

The "" is a set of quotes with no space in the middle.

• ✭✭✭✭✭

Thanks again, works great.

• ✭✭✭✭✭✭