If your date format is dd/mm/yyyy which is common in Europe and not mm/dd/yyyy which I think is the standard in the US, the expression
will not work.
TEXT(NOW()) return something like 25/05/2021 14:49:30.
DATETIME(25/05/2021 14:49:30) returns a blank as it expects the month to come first and there is no month 25.
So it appears that DATETIME() assumes that a date in text format follow the us convention regardless of local settings.
It gets even more interesting with EXTRACTDATETIMES().
ANY(EXTRACTDATETIMES(“13/5/2021”)) returns 13/05/2021 00:00:00
ANY(EXTRACTDATETIMES(“12/5/2021 x”)) return 05/12/2021 00:00:00
(the x is needed to make it a text input)
So ANY(EXTRACTDATETIMES(“13/5/2021 x”)) - ANY(EXTRACTDATETIMES(“12/5/2021 x”)) returns -4944:00:00
The number is negative because 5 December is 4944 hours after 13 May.
On the other hand
ANY(EXTRACTDATETIMES(“13.5.2021 x”)) - ANY(EXTRACTDATETIMES(“12.5.2021 x”)) returns 24:00:00. This is because 13 May is 24 hours after 12 May.
My guess is that the second example works as separating days and months with dots is a convention used in Germany and other places but not in the US. The problem with separating days and months with slashes is that it is done both in the US and in the UK but with the day and month in different orders.
So for a system based in the UK, the situation is that
- DATETIME(TEXT(NOW())) will only work on dates where the month and the day are the same number
- ANY(EXTRACTDATETIMES(TEXT(NOW()))) works from the 13th of each month and on the day where the date and day are the same number
I realise that different conventions for times and dates across the globe is a bit of a mess and that it is very hard to come up with a system that works everywhere.
Maybe it would make sense to consider adding an optional second parameter to some of the date and time functions where you can specify the date format?