Apple Event: May 7th at 7 am PT
As a new User of Numbers and very well versed in Excel, I'm having a hard time interacting with the two.
Just a few issues:
Can't set a single column to a Monetary value,
Can't 'Freeze Panes',
Correct date display changes half way down to a weird value after opening a .csv.
What gives here?
iMac, OS X Mountain Lion (10.8.3)
Patrick Forbes wrote:
Local is Canada: Day Month Year...
Hi Patrick,
You say that your System is set to Day Month Year and we can see that your import source data is Month Day Year. That's probably the cause of your problem. The first 6 dates make no sense to Numbers as dates (month greater than 12 no matter if you read it as Year Month Day or Day Month Year), so it leaves that data as text, as we can determine by its Left Justification. The last 7 dates can be interpreted as Year Month Day, so it does, sort of. It seems to guess that the Year is 0006. Two clues are the Right Justification and the adding of the leading zeros for Year to make the YYYY format. This is a common problem with importing data from one country's format to another's. Either Excel is much smarter, which may be true here, or it's not looking at your System Preferences and is simply set to the US format.
If you doubt that your first 6 dates are just Text, try calculating a duration by subtracting one from another.
Jerry
Hi Patrick,
"Local (sic) is Canada: Day Month Year
The rest of your suggestion is WAY too much work. Excel doesn't have that problem. Numbers shouldn't."
Although you may be using dd/mm/yy, your bank is using mm/dd/yy. Excel has interpreted all of the 'dates' as actual dates, in the mm/dd/yy order. This would indicate that either Excel has ignored your preference or localization setting, or that you have not made that setting on the machine running Excel.
Numbers has paid attention to your preference/localization setting. Because the maximum 'month' value is 12, the first six 'dates' in your sample, all of which have a number greater than 12 in the second ("mm") position, have been interpreted as text strings, as evidenced by their alignment in these cells.
The seventh value, and the six that follow it, do not contain values greater than 12 in the "month" position, so they are successfully interpreted as the date part of Date and Time values, evidenced by their alignment to the right edge of the cell. It's possible, however, they are not being interpreted as the correct date. The three leading zeroes on these 'dates' make it appear that Numbers is applying a ccyy/mm/dd format, and interpreting the last date (POS at the RC Superstore) as Aug 13, 0006.
"The Header freeze thing just selected a column. I need to play with that feature a bit more to figure out how to make it work properly…."
There are two 'Header freeze thing(s)' in the Table menu: Freeze Header Rows and Freeze Header Columns.
Obviously, you chose Freeze Header Columns, and you have one column defined as a Header Column.
You can find out more about Header Rows and Header columns in the Numbers '09 User Guide, available for download via the Help menu in Numbers. For anyone new to Numbers, I strongly recommend taking the time to read through the first four chapters of this guide. The rest can be left as a 'read it when you need it' volume.
Regards,
Barry
PS: Hope you weren't caught in the floding in TO today, and that your power was on through the day and tonight.
B
No, fortunately, I was sitting on my front porch enjoying a very nice vacation day and a few drops of water fell. I thought, that's nice, and the next day I learned of the deluge. Wow! My buddies @ work had their hands full, though.
Excel knows my Canadian Locale (spelled correctly!) thing and seems to handle it quite well as do my other apps. Shouldn't Numbers do so just as well?
Regarding the Header Freeze thing. I still can't figure out how to work it. I select the row and Freeze Row and nothing happens. Tried several variations , no joy. Help didn't help:
Belay that last Part!! Just found it:
If no header row, create one with right click.
If header row, Right click it and select 'Convert to Header
Row'
Patrick,
It is worth downloading and reading as needed, the free, searchable, Users Guide from here:
Hi Patrick,
Yes, it would have been a busy day for your colleagues; CBC radio reported that even one of the Ford brothers (Doug) was directing traffic in the heavy rain at one point.
"Excel knows my Canadian Locale (spelled correctly!) thing and seems to handle it quite well as do my other apps. Shouldn't Numbers do so just as well?"
Going by the two examples, and the dd/mm/yy spec you posted earlier, Excel may know your Canadian locale, but has ignored it in this instance.
Both the csv file from the bank:
and the Excel file screen shot:
show the dates in mm/dd/yy (or more accurately, in m/d/yy) format. Excel has simply copied the format used in the csv file, and ignored your preference/localisation setting.
Still, the results are more useful than Numbers's failed attempt to read these text strings as dates and convert them to actual Date and Time values.Might be worth a note to Apple (as opposed to a comment in this user to user forum, which Apple does not actively monito)r. Use the Feedback channel—Provide Numbers Feedback, via the menu item of that name in the Numbers menu in Numbers '09.
"If no header row, create one with right click.
If header row, Right click it and select 'Convert to Header"
You can also find this menu in the Row reference tab. Hover the mouse over the tab, then click the triangle that appears on the tab.
Note that "Header row" is not synonymous with "A row containing column heading labels," but refers to a row which has been specifically set to act as a Header row. A table may have up to five Header rows (and up to five Header columns), Header rows have properties that differ from thse of 'regular' rows. details available in the Numbers '09 User Guide.
Regards,
Barry
Numbers vs. Excel
