Change to formula

Cathy Fraser

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? 

 

 

Paul Newcome

=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)

Cathy Fraser

Thanks Paul, the formula worked. 

Paul Newcome

Happy to help! 

Cathy Fraser

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. 

 

 

 

 

Paul Newcome

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.

Cathy Fraser

Thanks again, works great. 

Paul Newcome

Happy to help.