Forum Discussion

dferry's avatar
dferry
Icon for Helper I rankHelper I
3 years ago
Solved

Using DateTime.LocalNow IF Else

I am trying to use today's date to subtract from a column in power query that is a Due Date.  There could be a null value in the Due Date column. My formula is if Date.From(DateTime.LocalNow()) - [D...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi dferry ,

     

    Try this instead:

    if [DueDate] = null then null
else if Date.From(DateTime.LocalNow()) < [DueDate] then "OnTime"
else Text.From(
    Number.From(
        Duration.Days(Date.From(DateTime.LocalNow()) - [DueDate])
    )
)

     

    You may need to fiddle with the final escape clause to get the exact output format you want, but the first two conditions should solve your isue.

     

    Pete

  • dferry's avatar
    dferry
    3 years ago

    Kudos Pete.  That worked.  Thank you very much.

    Doug

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
3 years ago

Hi dferry ,

 

Try this instead:

if [DueDate] = null then null
else if Date.From(DateTime.LocalNow()) < [DueDate] then "OnTime"
else Text.From(
    Number.From(
        Duration.Days(Date.From(DateTime.LocalNow()) - [DueDate])
    )
)

 

You may need to fiddle with the final escape clause to get the exact output format you want, but the first two conditions should solve your isue.

 

Pete