Incorrect comparison result with calculated date

Question

The initial date I have is on a bad format. So i break it down to find the day, month and year. afterwards I concatenate it. the concatenated cell has date format. then I compare this date to another one. I get incorrect result. When I substistute the calculated value of the date by typing it manually, I get the correct result. what can I do?

I tried a workaround by typing the date manually and the comparison worked correctly

Answer 1

To get the date as the DateTime value, use the Date function:

DATE(year,month,day)

Then it will be comparable with another DateTime value.

When you extract and concatenate the year, the month, and the day, you get the date as string. You can compare DateTime as string following some rules.

Here you can find more hints how to process your data.

For your sample, use:

=LET(d;TEXTSPLIT(INDEX(TEXTSPLIT(M9;" ");1;1);"/");DATE(INDEX(d;1;3);INDEX(d;1;1);INDEX(d;1;2)))