Forum Discussion
Nested IF with Dates
Anonymous
No problem, try this instead with the conditions declared more explicitly:
if [ClosedDate] = null then "Open" else if [OpenDate] < Date.Today then "Open" else if [HireDate] > Date.Today then "Open" else "Closed"As before, if you didn't declare the Date.Today variable, you can swap 'Date.Today' in the above for 'Date.From(DateTime.LocalNow())'.
Pete
Hi Anonymous ,
To have today's date easily accessible to your query, you can just add a line within your query (using Advanced Editor) like this:
Date.Today = Date.From(DateTime.LocalNow()),
Once you have this, you would create your new column like this:
if [ClosedDate] = null or [OpenDate] < Date.Today or [HireDate] > Date.Today then "Open"
else "Closed"
You could, of course, not bother declaring the Date.Today line, and just replace it in the above with Date.From(DateTime.LocalNow()).
Pete
Thanks Pete, I am following, I may need help with logic because it shows when my opendate is after today it shows open but I need it to say closed
Anonymous
No problem, try this instead with the conditions declared more explicitly:
if [ClosedDate] = null then "Open" else if [OpenDate] < Date.Today then "Open" else if [HireDate] > Date.Today then "Open" else "Closed"As before, if you didn't declare the Date.Today variable, you can swap 'Date.Today' in the above for 'Date.From(DateTime.LocalNow())'.
Pete