The problem is that the data has been entered as month/day/year, whereas your system default is day/month/year.
As you say, it's a bother, and every time I download my Paypal history, I have this problem.
Just did some research ... seems that Excel has a useful "Text to Columns" feature in the Tools menu, that allows dates to be read the right way.
Numbers does not have an equivalent of this feature.
There are several threads on this:
- Re: How to "text to column" (feature of Excel) in Numbers
- Text to column function
- "TEXT TO COLUMN" formula for numbers
- Re: Converting "text to column" command
- Is there a Numbers equivalent to Excel's "Text to Columns"?
- "Text to Column" 's excel equivalent in Numbers
One workaround is to set up a couple of tables that already have the required formulae for converting the date.
That way, you just have to plug the data into the first table, then read it from the converted table.
The conversion for the dates would involve 4 columns (for clarity)
Year = 2000+RIGHT($A2,2)
Month = LEFT(A2, FIND("/",A2)-1 )
Day = MID(A2, FIND("/",A2)+1, FIND("/",$A2,FIND("/",$A2)+1)-FIND("/",A2)-1)
DMYY = DATE(Year,Month,Day)
2. convert (formulae above)
3. proper values, where the date column reads from the converted table.
These could be set up in a template, so that you don't need to keep fussing each time.
Then just use Edit > Paste Values before proceeding with any other work.
And put in a request to Apple to include the "Text to Columns" feature in future releases
Patrick Forbes wrote:
Local is Canada: Day Month Year...
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.
Currently Being ModeratedJul 9, 2013 11:47 PM (in response to Patrick Forbes)
"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.
PS: Hope you weren't caught in the floding in TO today, and that your power was on through the day and tonight.
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?
Currently Being ModeratedJul 10, 2013 6:24 PM (in response to Patrick Forbes)
It is worth downloading and reading as needed, the free, searchable, Users Guide from here:
Currently Being ModeratedJul 10, 2013 11:12 PM (in response to Patrick Forbes)
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.