Nested IF with Dates

[Anonymous]

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. 

[BA_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

[Anonymous]

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

[BA_Pete · Accepted solution]

@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