OfficeCommunity.com
The official WordPerfect Office Community.

Quatro Pro X5 Date Format Question.

rated by 0 users
This post has 29 Replies | 2 Followers

Top 50 Contributor
Posts 21
RDouglass1 Posted: Tue, Mar 22 2011 8:48 AM
Under your "Numeric Format" tab, the selection of "DATE", brings up a list of possible Date setting choices.
These are:
DD-MMM-YY
DD-MMM-YYYY
DD-MMM
MMM-YY
MMM-YYYY
Long Date Intl
Short Date Intl
None of these choices converts the date numbers of: +03-02-2011 into the US style of date: 03-02-2011. Instead, your formats Convert the date into a Numerical Calculation. I am not aware of any US conversions of dates that show the Date prior to the Month. I am used to Dates being shown as: MM (Numbers)-DD-YY, or MM (Numbers again)-DD-YYYY, or MMM (Letters)-DD-YY, or MMM (Letters again)-DD-YYYY.
I do not see any of your Date Choices that offer Month-Day-Year. Could you Please make a change that offers an AMERICAN Style of Date Text in your Spread Sheets?????
The Normal US Date Format should be: MM-DD-YY, or MM-DD-YYYY. It should already have been included in your Formatting Choices.
Is there an Up-Date to Add these types of Date Styles to the Format List? or know how to add it to the availbe Choices list?
I have already submitted this question to Corel, and they have referred me to this forum for assistance.
Top 10 Contributor
Posts 904
HeatherB replied on Tue, Mar 22 2011 9:44 AM

You can use Custom Numeric format option to create any date style you'd like; as well, if your computer date is formated the way you'd like it displayed in your spreadsheet, then set it to one of the last two options -- by default they pick up the format from your operating system (or whatever was customized in Tools > Settings > International > Date)

Top 50 Contributor
Posts 21
RDouglass1 replied on Tue, Mar 22 2011 10:14 AM

I did try one of the International date Formats when this first got started.

It took the Bank Displayed Date of 03-14-2011

and converted it into: 17-Jun-1894

That did not exactly resolve my problem.

I am not very handy at Creating Format items. Are there any Classes for Quatro Pro that teach Formatting?

I will look for the Custom Format options and see if I can progress with that. Thanks.

Top 10 Contributor
Posts 904
HeatherB replied on Tue, Mar 22 2011 10:57 AM

If it converted it to a date in the 1800s that usually means that Quattro Pro didn't understand you were entering a date.  Instead it thinks you've entered the equation  3 minus 14 minus 2011 and then taken the resulting number (-2011) and formatted it as a date.  Dates in spreadsheets are stored as Julian values, for example, today's date is represented in a spreadsheet as 40624 (check wikipedia.org for a complete explanation)

So you have two problems, 1st, you have to enter the date in such a way that QP will understand it's a date; and 2nd you need to format it the way you want.

Go to Tools > Settings, International, Date -> whichever date format you have selected here is the way QP wants it entered.  For example, mine is set to the operating system default which is 2011-03-22, so I enter dates in QP as year dash month dash day (no spaces).  I can then use Format > Numeric > Dates option to format it the way I want (or use the custom numeric format)

Top 50 Contributor
Posts 21
RDouglass1 replied on Tue, Mar 22 2011 11:44 AM

I formatted the A Collumn to LI Date from the Format list. From the conversation I had earlier with one of your agents, I understood that QP was seeing a Formula when I wanted it to see a date. the Agent did tell me to replace a "+" sign with an " ' " apostrophy to make the date show up as it should.

 

I then found out through personal effort, that I could select the entire collumn, and then do a Search and Replace function to replace all of the "+" signs with " ' " Apostrophies, and do the entire column at one time instead of doing each Date entry one at a time.

 

that does not however solve my problem of changing the date to read as it should without needing to jump through hoops.

How can I get QP to see the date as it comes from my Bank Web Site???

I do not want to violate forum policy by using other program names, but one Spread Sheet program came with my Computer when it was purchased. that Program sees the date as it should be, but QP does not, Why?

I want to be able to Copy my Bank Data to a Spread Sheet, and have the Data displayed as it should be.

Top 10 Contributor
Posts 904
HeatherB replied on Tue, Mar 22 2011 1:32 PM

Can you paste one line from your bank data (changing the dollar amounts of course) so I can play with it?

 

Top 50 Contributor
Posts 21

I will try, if this does not show correctly, I will link the last file I made.

Pasted Data:

3-22-2011    *BANK ATM WITHDRAWAL Mar22 09:15a     ABC# 00000 & XYZ# 999        $ 400.00

<edited>

The first Date above is where I changed the + to a ', and it makes the date read correctly.

If you need any more information, Please let me know what you need. Thanks for your help.

 

I tried to attach a copy of the file, but do not see an attach option.

Top 10 Contributor
Posts 904
HeatherB replied on Tue, Mar 22 2011 10:28 PM

Thanks, I'll play around with it to see if I can come up with a easier way to bring it in.

Two other things:

1) I edited the data you provided in your post down to just one line, just to be extra safe with your information

2) does your online banking website give you the option to export as a comma delimited file?  QP would work better with that then a copy/paste from a website.

Top 50 Contributor
Posts 21

Yes, it does, but then it also requires me to know exactly what dates to include in the DL. If I am trying to go back 1.5 to 2 years it gets more complicated. It would then be a down Load rather than a copy paste situation though.

 

When you edited the above post, did you get the Bad Date info first?? I see all of the bad Dates are gone now.

Top 10 Contributor
Posts 904
HeatherB replied on Wed, Mar 23 2011 8:36 AM

Yes, I copied them first.

Top 10 Contributor
Posts 775
lemoto replied on Thu, Mar 24 2011 3:31 PM

RDouglass1:

Under your "Numeric Format" tab, the selection of "DATE", brings up a list of possible Date setting choices.
......
None of these choices converts the date numbers of: +03-02-2011 into the US style of date:

 The essential point (and I cannot tell from these posts whether is has been satisfied) is that what looks like a date to you may well not be a date to QP.

I just created a Custom Format that you could use. I entered a date in my local dmy format: 23/6/11

Having applied the Custom Format -

 - In the Cell I see 6-23-2011 (mdy)

 - and here is the point: with the cursor in that Cell, in the Input Line (above the main edit pane, alongside @ [] X <checkmark>) I see: 40717.

40717 is the QP Date Code for 23 June 2011. If your "date" is not in that format, then a Custom Format that expects a QP Date will not work.

 

Please report what you see in similar circumstances..

Top 50 Contributor
Posts 21

lemoto:

RDouglass1:

Under your "Numeric Format" tab, the selection of "DATE", brings up a list of possible Date setting choices.
......
None of these choices converts the date numbers of: +03-02-2011 into the US style of date:

 The essential point (and I cannot tell from these posts whether is has been satisfied) is that what looks like a date to you may well not be a date to QP.

I just created a Custom Format that you could use. I entered a date in my local dmy format: 23/6/11

Having applied the Custom Format -

 - In the Cell I see 6-23-2011 (mdy)

 - and here is the point: with the cursor in that Cell, in the Input Line (above the main edit pane, alongside @ [] X <checkmark>) I see: 40717.

40717 is the QP Date Code for 23 June 2011. If your "date" is not in that format, then a Custom Format that expects a QP Date will not work.

 

Please report what you see in similar circumstances..

I think this just brings me back to the start of my original question. Since I depend on the SW that I use to give me most of my Format options, the date showing as 23-6-11, or 23rd- June, 11 does me no good. None of the Date formats offered in QP made the date show up as I have been accustomed to for some 60 years, 6-23-11, or 06-23-2011.

 

The string of Format that you show above: @ [] X <checkmark>) , does not mean much to me. I can do basic formulas, Add, Subtract percentages, and so forth, but formatting a different date style is beyond my abilities. can a file be pasted into QP that would allow for the style of date text that I am looking for?

 

I do not want to have to re-do this function each time I get info from my bank. I would however love for QP to see my info as it comes from the bank site.

Top 50 Contributor
Posts 21

I do see part of the problem. As I have been working with this, I see that the dividers from the Bank Info are Hyphens, and not Divission Signs. If the Hyphen is changed to a Divission Sign as a Long International Date, it shows up correctly.

Top 10 Contributor
Posts 775
lemoto replied on Fri, Mar 25 2011 8:00 AM

I think that this is one of the settings that QP picks up from Windows' Regional Settings (or similar). If so, then you could change the divider in Regional Settings and QP would do was you wish. But probably you would like to keep the /. Then a simple Find and Replace may do the trick. (Be sure that no unhelpful changes might be made.) You could have a macro that does it from a single click or a keystroke. If that sounds useful and you do not know how, please ask.

Top 50 Contributor
Posts 21
RDouglass1 replied on Fri, Mar 25 2011 10:42 AM

I have done Search and Replace, and that does work, but I do get concerned about changing some items that do not fall into the Date Collumn.

 

A Macro sounds like it might be useful, but I have not had much experience with setting those up.

Page 1 of 2 (30 items) 1 2 Next > | RSS
© Corel Corporation. The content herein is in the form of a personal web log ("Blog") or forum posting. As such, the views expressed in this site are the views of the participants and do not necessarily reflect the views of Corel Corporation, or its affiliates and their respective officers, directors, employees and agents. Terms of use.