Forum Discussion
DateTime with If condition
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"
not([Date] is datetime)
9 Replies
if not([Date] is date) then "N/A" else if [Date]< DateTime.LocalNow() then [Date] else "Est. "&DateTime.ToText([Date])
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.
not([Date] is datetime)
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])wdx223_Daniel your formula is working fine. can you please advise what was the mistake in my formula?
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.
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.
=if not([Date] is datetime) then "N/A" else (if [Date]< DateTime.LocalNow() then "" else "Est. ")&DateTime.ToText([Date],"dd/MM/yyyy")