Forum Discussion
Nested IF with Dates
Hi Trying to get quick answer my apologies. Have three dates, ClosedDate, HireDate., OpenDate
I need to create a column where if ClosedDate is null then "Open" or if Open date is before today then open or if hire date is after today then open else everything else "Closed".
Thanks in advance. And side note how do i put in today date in Power Query.
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
3 Replies
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
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