Re: date format

Janet Forbes wrote:
> For reasons not understood by myself I have never (with the odd 
> unpredictable exception) been able to get the date format to work 
> properly. Regardless of numerous attempts at formating and the reading 
> of all the help explanations I typically get the date reverting to 
> 31-dec-1999 or the like and usually end up entering it as a label; this 
> also occurs when on occasion I have tried to cut and paste data from 
> another sheet or when I open a sheet saved in a different format eg csv ..
> 
> Would really appreciate to have an explanation as to what I should be 
> doing to get this to work properly for once and for all.

Make sure "Compatible formula entry" under Tools-Settings-General is
NOT 
checked.  Basically, none of the date formats recognized by QP for the purpose 
of date entry will work if that setting is enabled -- which is not to say that 
date entry is entirely impossible if that setting is checked, but it will be 
substantially restricted.

QP has a defined set of date formats it will recognize when you ENTER a date; 
this format is separate and distinct from the format you select to DISPLAY a 
date within a cell.  You can format the date displayed in a cell to anything you

want, but when entering a date you must conform to the few formats which QP will

recognize as dates.

The set of recognized date formats for the purpose of date ENTRY depends in part

on your Windows setting (Regional Options) and your International date setting 
in QP (Tools-Settings-International).  The safest date format to use for date 
entry is DD-MMM-YYYY, as well as its permutations (DD-MMM-YY, DD-MMM, MMM-YYYY,

MMM-YY).  This format is the safest because it is unambiguous in terms of 
day/month vs. month/day, month/year vs. year/month, as well as the fact that 
this format is independent of the Windows regional and the QP International date

settings.  Basically, this date format will work on any computer regardless of 
system and QP settings (as long as you keep "compatible formula entry"

unchecked).  The short and long international format, which is also recognized 
by QP as a date, will depend on your particular system settings.  Note that your

QP International setting can either adopt your Windows setting (the default 
configuration) or use an alternate format from the list presented in the 
International date dialog box.

Cheers,
Uli

Dear Folks

For reasons not understood by myself I have never (with the odd 
unpredictable exception) been able to get the date format to work 
properly. Regardless of numerous attempts at formating and the reading 
of all the help explanations I typically get the date reverting to 
31-dec-1999 or the like and usually end up entering it as a label; this 
also occurs when on occasion I have tried to cut and paste data from 
another sheet or when I open a sheet saved in a different format eg csv ..

Would really appreciate to have an explanation as to what I should be 
doing to get this to work properly for once and for all.

Thanks

Janet

Uli

Thanks

You got me working in the right direction but the as the commands didn't 
work out exactly as you suggested I thought that FYI I would let you 
know what I did  and sorry I didn't tell you I was using WindowsXP sp2.. 
not sure that that made any difference.

When I did the Tools > Settings > Application > General  I did not find

a "compatible formula" entry tick box.

I did have a Compatibility keys (QP-DOS) tick box and  a Mathematical 
Formula Entry tick box - the first was unticked and the second was 
ticked.  I unticked the Mathematical Formula Entry box.

This did not seem to correct the problem.

I checked to make sure that I had the Windows setting at the right 
configuration ( I did) and then went back to
Tools > Settings> Application > International > and went to the Date

Settings and reselected the Date Format.  This seems to work.  I'm not 
sure if I ever tried this before.. but don't know how I could have 
missed, I have tried so many things in the past. I must say that this 
last page of configurations is a bit strange; intuitively (for me at 
least) it feels like you only have one selection and I now wonder having 
picked the date configuration option.. if Currency or Punctuation will 
will still be ok..

I can't believe how often I have had a go at this with no luck. 
Fortunately I don't have much need to use spreadsheets.

Thanks again

Janet


> 
> Make sure "Compatible formula entry" under Tools-Settings-General
is NOT 
> checked.  Basically, none of the date formats recognized by QP for the 
> purpose of date entry will work if that setting is enabled -- which is 
> not to say that date entry is entirely impossible if that setting is 
> checked, but it will be substantially restricted.
> 
> QP has a defined set of date formats it will recognize when you ENTER a 
> date; this format is separate and distinct from the format you select to 
> DISPLAY a date within a cell.  You can format the date displayed in a 
> cell to anything you want, but when entering a date you must conform to 
> the few formats which QP will recognize as dates.
> 
> The set of recognized date formats for the purpose of date ENTRY depends 
> in part on your Windows setting (Regional Options) and your 
> International date setting in QP (Tools-Settings-International).  The 
> safest date format to use for date entry is DD-MMM-YYYY, as well as its 
> permutations (DD-MMM-YY, DD-MMM, MMM-YYYY, MMM-YY).  This format is the 
> safest because it is unambiguous in terms of day/month vs. month/day, 
> month/year vs. year/month, as well as the fact that this format is 
> independent of the Windows regional and the QP International date 
> settings.  Basically, this date format will work on any computer 
> regardless of system and QP settings (as long as you keep "compatible

> formula entry" unchecked).  The short and long international format, 
> which is also recognized by QP as a date, will depend on your particular 
> system settings.  Note that your QP International setting can either 
> adopt your Windows setting (the default configuration) or use an 
> alternate format from the list presented in the International date 
> dialog box.
> 
> Cheers,
> Uli
> 
Uli wrote:
> 
> Janet Forbes wrote:
>> For reasons not understood by myself I have never (with the odd 
>> unpredictable exception) been able to get the date format to work 
>> properly. Regardless of numerous attempts at formating and the reading

>> of all the help explanations I typically get the date reverting to 
>> 31-dec-1999 or the like and usually end up entering it as a label; 
>> this also occurs when on occasion I have tried to cut and paste data 
>> from another sheet or when I open a sheet saved in a different format 
>> eg csv ..
>>
>> Would really appreciate to have an explanation as to what I should be 
>> doing to get this to work properly for once and for all.
> 
> Make sure "Compatible formula entry" under Tools-Settings-General
is NOT 
> checked.  Basically, none of the date formats recognized by QP for the 
> purpose of date entry will work if that setting is enabled -- which is 
> not to say that date entry is entirely impossible if that setting is 
> checked, but it will be substantially restricted.
> 
> QP has a defined set of date formats it will recognize when you ENTER a 
> date; this format is separate and distinct from the format you select to 
> DISPLAY a date within a cell.  You can format the date displayed in a 
> cell to anything you want, but when entering a date you must conform to 
> the few formats which QP will recognize as dates.
> 
> The set of recognized date formats for the purpose of date ENTRY depends 
> in part on your Windows setting (Regional Options) and your 
> International date setting in QP (Tools-Settings-International).  The 
> safest date format to use for date entry is DD-MMM-YYYY, as well as its 
> permutations (DD-MMM-YY, DD-MMM, MMM-YYYY, MMM-YY).  This format is the 
> safest because it is unambiguous in terms of day/month vs. month/day, 
> month/year vs. year/month, as well as the fact that this format is 
> independent of the Windows regional and the QP International date 
> settings.  Basically, this date format will work on any computer 
> regardless of system and QP settings (as long as you keep "compatible

> formula entry" unchecked).  The short and long international format, 
> which is also recognized by QP as a date, will depend on your particular 
> system settings.  Note that your QP International setting can either 
> adopt your Windows setting (the default configuration) or use an 
> alternate format from the list presented in the International date 
> dialog box.
> 
> Cheers,
> Uli
> 

Janet Forbes wrote:
> I unticked the Mathematical Formula Entry box.
> This did not seem to correct the problem.

If indeed you cannot enter 13-mar-08 as a date in QP after unticking that box, 
then v12 handles date entry differently from v8 (my version).  What does QP show

after you enter this date?

> I checked to make sure that I had the Windows setting at the right 
> configuration 

In this context, there's no "right configuration" - you could, if you
wanted to, 
set your Windows regional settings to Greek for currency, Swiss for time, and 
Chinese for date.  For purposes of date entries in QP, the Windows setting (or 
more specifically, the short date format) simply indicates the type of 
international date format QP will recognize IF you select "Windows
Default" in 
QP under Tools-Settings-International.  Assuming, for example, your Windows 
setting is English(Canada), then QP will recognize a date if you enter, say 
13/03/08.  If instead of "Windows Default" you set your QP
International Date 
setting to DD.MM.YY, QP will accept your date entry as 13.03.08, but will not 
accept 13/03/08.

Still, regardless of your Windows settings and International QP settings, QP 
should always accept your date entry if you enter 13-mar-08.

Once you've entered a date, you can format the DISPLAY of that date to any 
format you wish, using one of the date formats under Numeric formats.

> I now wonder having 
> picked the date configuration option.. if Currency or Punctuation 
> will still be ok..

What is true for the Windows settings (as explained above) is also true for the

QP settings:  they are simply settings of how you want dates, times and currency

displayed.  There's no requirement for these settings to be consistent.  You can

choose any setting regardless of your actual location, and if you wanted to, any

mix of custom settings.

Cheers,
Uli

Don Codling wrote:
> Uli wrote:
>>
>> Janet Forbes wrote:
>>> I unticked the Mathematical Formula Entry box.
>>> This did not seem to correct the problem.
>>
>> If indeed you cannot enter 13-mar-08 as a date in QP after unticking 
>> that box, then v12 handles date entry differently from v8 (my 
>> version).  What does QP show after you enter this date?
>>
> My QP12 shows it as a date.

I'm guessing "the problem" was that Janet only tried to enter 13/03/08
(or 
whatever format corresponds to her system setting), rather than the more robust

DD-MMM-YY format.

Uli