Forum Discussion

dferry's avatar
dferry
Helper 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()) - [DueDate] <=0 then "OnTime" else Date.From(DateTime.LocalNow()) - [DueDate]

It says No Syntax Errors have been detected but the results in the column are errors.  The error says Expression.Error: We cannot apply operator < to types Number and Duration.
Details:
Operator=<
Left=0
Right=847.00:00:00

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

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super 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

    • dferry's avatar
      dferry
      Helper I
      3 years ago

      Kudos Pete.  That worked.  Thank you very much.

      Doug