Power Query Custom Column Error for DateTime

[dbollini]

Hello,

 I am trying to write below condition for custom column if datefirst email sent is less than 24 hrs green if its equal to more than 5 days then yellow and if its more than 5 its Red but i am getting the Error below:

if [DateFirstEmailSent] = (Date.From(DateTime.LocalNow()))then "Green"
else if [DateFirstEmailSent] <=Date.AddDays(Date.From(DateTime.LocalNow()),5) then "Yellow" else if [DateFirstEmailSent] >= Date.AddDays(Date.From(DateTime.LocalNow()),6) then "Red" else "null"

Expression.Error: We cannot apply operator < to types Date and DateTime

[olgad · Accepted solution]

Hi, your [DateFirstEmailSent] shall be date not date time, then it works, if it is a datetime, then it doesnt.

[dbollini]

Thank you and if [DateFirstEmailSent] = (Date.From(DateTime.LocalNow()))then "Green"
else if [DateFirstEmailSent] <=Date.AddDays(Date.From(DateTime.LocalNow()),5) then "Yellow" else if [DateFirstEmailSent] >= Date.AddDays(Date.From(DateTime.LocalNow()),6) then "Red" else "null"

It is looping in till second line but not looping in to the Third REd column not sure why

[dbollini]

It is looping to Yellow if its more that 5 days but its not looping to RED or adding RED in the column if the condition is met for more than 6 days else if [DateFirstEmailSent] >= Date.AddDays(Date.From(DateTime.LocalNow()),6) then "Red" else "null"

[dbollini]

Thank you this worked