The date( function defaults to something like dd/mm/yyyy:
However, my system is configured for something like mm/dd/yyyy. (Sorry, I have an image but your system limits me to one image per post.)
The documentation for date(text) does not specify the expected format of the text parameter, but from other documentation I got the idea that this was tied to the system date format - e.g., this example shows date( interpreting the text as mm/dd/yyyy:
I am importing an existing table from text, but dates such as “1/13/2017” (mm/dd/yyyy) are being silently discarded from Date fields. I believe that the root cause is that date( expects dd/mm/yyyy.
I finally solved the problem using this import formula (allowing for blank fields so that I did not get “//” as the result):
I am impressed that Panorama is capable enough to allow for this workaround, but that was a lot of work for something that I expected to work out-of-the-box. Is my expectation wrong? Is there a problem with the date( function? Is there some way to configure Panorama to get the expected result when importing dates?
This problem has been reported by one other person. However, he lives in Australia, where day, month, year is the proper order. As far as I know, you are the first American to have difficulty with the American date pattern.
There may be a problem with the date function recognizing which international setting to use, but with only two people experiencing it, and Jim Rea is neither of them, it could be very hard to track down.
Importing works the same way, it adjusts depending on what the region settings are.
Actually, that report was for the checkbook example in the tutorials. Panorama was always generating the text to be imported that format in U.S. format, so it didn’t import correctly in Australia (or anywhere that used the non-US format). So the problem was not the import, it was in the tutorial example code. That problem has since been corrected.
I think that restriction is because you are brand new to the forum. As your reputation increases you won’t have that restriction any longer (the forum system does all that automatically, I don’t know the exact rules).
The only way I know to set that is thru the System Preferences window, as shown in the screen shots above. You can either change the region, or press the Advanced button and then the Date tab to micro-manage the date settings.
That was helpful. My system was showing the Region as “United States (Custom)”. I reconfigured it to show just “United States” and now date( works as expected.
FYI: Here are the custom date formats before I reset them:
I’ve had the same issue, more or less. The root of the problem seems to be that the OS Date Pref Pane doesn’t like to be contradicted by Panorama, especially if any of the defaults have been changed. In my case the “short date” was showing as “custom” (I don’t recall exactly what it was, alas), and in Pan, in order to get a date that made sense, in fact a date that would stay in the entry field once I tabbed out of it, was to have no pref set for input and enter month (or mon)/dd/yy. Once I set the system short pref back to “Default”, left all the Pan prefs blank except for “Input”, which I set to __/__/__ (because after 25 years of not having to type a “/” I’m not going to start now), everything seemed to settle down, at least relatively… Jim Rea says he’s coded a fix for the next release/bug fix/whatever, but I just intend to see that OS prefs stay at “Default” and stay out of the OS/Pan code squabble, as I already have enough issues with converting 25 years of databases to X, thank you very much…
