Google-sheets – Date comparison and DATEVALUE with dd.mm.YYYY. date format

google sheetsgoogle-sheets-dates

I'm using the custom date format for one of my columns, dd.mm.YYYY.. I want to compare that date to TODAY(), by using

+AND($D2 < TODAY())

where column D is the one with the dates in dd.mm.YYYY. format.

However, this comparison doesn't seem to be working the way I expect it to, because it returns FALSE for 07.06.2018 (the seventh of June).

Googling offered DATEVALUE in the way of solving my problem. So, I changed the test (spaces added here, for readability) to

+AND(DATEVALUE($D2) < TODAY())

This doesn't work as well, Google Sheets returns

#VALUE Error - DATEVALUE parameter '01.01.2018' cannot be parsed to date/time.

Finally, my question.

How can I compare the date in custom format with today's date? I want to do this without resorting to the use of substrings and casting parts of the date to integers.

Best Answer

Set the spreadsheet locale setting to Bulgaria or to another country that u use a dot as date separator.

Related