Change to formula
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?
Comments
-
=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.
-
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives