Parsing date with non-numeric month


#1

This is my problem:

date( function: Illegal date “29 Jun 2013 14:58” (month must be from 1 to 12).

Is there no inverse of datepattern(?


#2

when I enter date(“29 Jun 2013 14:58”) I do get the valid result of 2456473. But when I copied your entire quote I got an error due to the curly quote marks. Could that be the issue on your side?


#3

Panorama uses curly quotes in its error messages, which is what I copied.

Since your formula yields the exact same error on my system, I assume date( is locale-dependent. Is there no portable version?


#4

To be portable, replace the quoted date with either the field name or variable containing the date;

date(DateVariable)

or

date(DateField)


#5

I meant portable between locales. I do not want my database to break just because a user has macOS set to something other than English.


#6

This is the function you need:

To use this function you need to supply separate year, month and day examples. Suppose you had a variable that contained text in the date format specified in the original post:

let rawdate = "29 Jun 2013 14:58"

You’ll need to parse this yourself to use it with the datevalue( function:

datevalue(val(nthword(rawdate,3)),arraysearch("jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec",lower(nthword(rawdate,2)),1,","),val(firstword(rawdate)))

This formula will work no matter what the region settings of the computer are set to.


#7

Thank you. Feels a bit like 1998.