Forum Discussion
Power query If submit date between start date and end date, then "on time" else "late"
Hi, I was wondering how you would input a string of text into a new column if submission date is between two dates (or greater than start date and smaller than end date). I've heard ab...
Hey,
please be aware that the Submitted On datetime is greater than the End date.
Where 3/03/2019 10:27:55 PM is close to the 4th of March
the end date 3/03/2019 12:00:00 AM marks midnight between the 2nd and 3rd of march.
If the time is not relevant you might consider to extract the date part from all of your columns or just add 1 day to the End column.
Regards,
Tom
Anonymous
As TomMartens also suggested, in your case you can do the calculation using date part only
so as a custom column try
=if Date.From([Submitted On]) >= Date.From([Start]) and Date.From([Submitted On]) <= Date.From([End]) then "on time" else "late"
Zubair_Muhammad
Community Champion
Community ChampionAnonymous
As TomMartens also suggested, in your case you can do the calculation using date part only
so as a custom column try
=if Date.From([Submitted On]) >= Date.From([Start]) and Date.From([Submitted On]) <= Date.From([End]) then "on time" else "late"
Thanks for your help guys! Works perfectly.