Welcome!

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?

Find more posts tagged with

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there an issue with using reports on a dashboard to create a graph?
I've done this a million times - create a report, separated by campus with a total $$. Then add it to a dashboard and make a graph. NOTHING is working right now when I try to do this. I get an error that the data cannot be charted. What the what? Help anyone? Is this a issue since they apparently changed something in the…
Selected Data Cannot be charted
It does not matter which data I select, I am getting the error of Selected data cannot be charted. I even tried reusing a report that is producing a chart on a different dashboard, but I am unable to create new charts in a dashboard. Anyone have any ideas as to why?
Upgrade user mismatch
I have inly one user in my trial account. When I am trying to upgrade to PRO plan it is defaulting for 2 users and not allowing me to upgrade the plan for only one user. Please help.