0

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.

  • 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. – FiddlingAway Jun 11 '18 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.