DateTime with If condition

[Anonymous]

I have shared a simple dummy chart.  I want to convert the date with the below 03 conditions-

1. If the Date does not exist, then it will return "N/A"

2. If the Date is less than today's date, then it will return Date

3. If the date is higher than today's date, then it will return "Est.  Date".

I using the below code, but this is not working.

if Value.Is(Number.From(DateTime.FromText([Date]) ), type number)
then
if DateTime.FromText([Date]) < DateTime.LocalNow() then
[Date]
else "Est. " & [Date]
else "N/A"

[Anonymous]

Magic 😄

[Anonymous]

@wdx223_Daniel I have facing a Date format issue. your formula is giving DateTime format whereas I am looking for Date format only as in below picture.

[wdx223_Daniel]

=if not([Date] is datetime) then "N/A" else (if [Date]< DateTime.LocalNow() then "" else "Est. ")&DateTime.ToText([Date],"dd/MM/yyyy")

[Anonymous]

@wdx223_Daniel your formula is working fine. can you please advise what was the mistake in my formula?

[wdx223_Daniel]

there are many data type in your data. DateTime.FromText is only used to convert Text to DateTime,  So, your first two conditions will get errors  when the value is a datetime already. And, Text and DateTime can not be concatenated directly, that will give a error too.

[Anonymous]

@wdx223_Daniel is working fine. you are great.

=if not([Date] is datetime) then "N/A" else if [Date]< DateTime.LocalNow() then [Date] else "Est. "&DateTime.ToText([Date])

[Anonymous]

@wdx223_Daniel  Thanks for your quick response. But I am getting only "N/A". Please see the below snapshot.  I do not change my data to Date format, Please see the below mark in the snapshot.  I dont want to change the data format to DATE. I want to solve it with a formula. Please help.

[wdx223_Daniel · Accepted solution]

not([Date] is datetime)

[wdx223_Daniel]

if not([Date] is date) then "N/A" else if  [Date]< DateTime.LocalNow() then [Date] else "Est. "&DateTime.ToText([Date])