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


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.

  • I find it strange that it's not recognized / supported - according to Wikipedia, the format is in use in countries such as Belgium, Poland, Slovakia, most of the former USSR, Turkey, etc. But if that's the case (format not supported in Google Sheets), I guess I'll have to use the solution I was trying to avoid. Commented Jun 11, 2018 at 14:21

1 Answer 1


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.