Forum Discussion
Power query If submit date between start date and end date, then "on time" else "late"
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"
Community ChampionAnonymous
Just make this small amendment to your formula
=if [submitted on] >[Start] and [submitted on] <[End]
Zubair_Muhammad Thanks for the quick reply! I've tried that, and "late" and "on time" do appear - except not in the right way. I know for a fact that in this set of data, no one submitted it late:
- Zubair_Muhammad
Anonymous
I am little confused.
The yellow highlighted "Submitted on" figures in your picture above appear to be between Start and End dates..So the formula should return late. Isn't it?
Zubair_Muhammad My apologies, I accidentally switched the two - but I do have another follow-up question about inclusivity - adding the equal signs to make it "greater / less than or equal to" doesn't seem to include submissions as on time if they submitted on the last day of the date range?