Question on formatting dates as text in excel

Question

I have two sets of data. One has dates in the format Jan 10, 2020 which is stored as text. The other format is dd/mm/yyyy and is not text.

I'm trying to check if the dates are equal but I can't get it to work.

I've removed the , in the text stored date. I've tried using the Datevalue function but it just returns #value. The date function also doesn't work...

Any help appreciated.

Thanks.

Answer 1

Not all text strings are suitable for DateValue(). It works best when the day, month and year representation are in the order that your regional settings expect. If you are on UK settings, then you want DMY order, but the date text is in MDY order.

Therefore, you need to transform the text string into something that Datevalue() can process, for example

=DATEVALUE(MID(A1,5,FIND(",",A1)-5)&"-"&LEFT(A1,3)&"-"&RIGHT(A1,4))

This formula works for one- or two-digit days.

Format the result as date and use it for your comparison.