You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I have received a table of data with dates in "mmm-yy" format - e.g. Jan-20, Aug-19. I simply can't get Numbers to recognise them as dates.
I've tried using the Create Custom Format feature under Cell, but it only seems to provide for 4-digit dates. If I use that in my format, it converts all the year values in the data to 2020.
Does someone have a solution please?
andykent99 wrote:
You can create a custom format with a 2 digit date
Can you tell me where that is please? Creating a custom date format only gives me the 4 digit option.
Clicking the "v" should give a dropdown where you can choose a two-digit year day, month etc.
Did you see my question about your data source?
SG
Hi Andy,
Try this in Numbers:
Table 1 column A is the text data that you are importing.
Formula in B2 =LEFT(A2,3)
Formula in C2 =MONTH(B2)
Formula in D2 =RIGHT(A2,2)
Formula in E2 ="20"&D2
Formula in F2 =DATE(E2,C2,1)
Columns F and G are formatted as yyyy-mm-dd (thanks, SG!) to avoid the confusion in the way that different countries format dates. See here: https://en.wikipedia.org/wiki/Date_format_by_country
Column G is a test to see if numbers will treat column F as a valid date.
Formula in G2 =F2+1
You can format F and G to a date that you are familiar with.
When all is working, hide columns B, C, D, E and G
Regards,
Ian.
It appears we are stuck with how Numbers interprets these dates. An AppleScript that attaches a "20" to the dates might be the best solution.
You can create a custom format with a 2 digit date, contrary to what you said in your first post.
Thank you, I missed that.
Unfortunately, specifying the year as 2-digit in this way still leaves Numbers assuming that part of the data is a day, and switching the year to 2020.
The data was pasted in from an Open Office (.ods) file. I guess I could change the format at that end, but if I do then it's probably easier to do my entire task in Open Office and ditch Numbers.
andykent99 wrote:
The data was pasted in from an Open Office (.ods) file. I guess I could change the format at that end, but if I do then it's probably easier to do my entire task in Open Office and ditch Numbers.
Changing the format on the source end should be a one-time thing, very quick and easy. Depending on what you are trying to do, Numbers with its "Duration" data format (which Excel doesn't have and I assume Open Office doesn't have) can be very convenient.
SG
I am trying to accomplish your task in Excel and having the same problem of the year being interpreted as a day in year 2020. I tried pre-formatting a cell as mmm-yy but when I type in Jan-19 it becomes Jan 19, 2020 (formatted as Jan-20). I tried starting with Jan-19 in a text cell and applying the format mmm-yy and it did the same.
These "dates" appear to be in text-formatted cells in the file you are importing, according to what you said in an earlier post. Or you are importing a CSV file, which, in the export to CSV process, would have converted the dates to whatever was displayed in the cell at the time, thus destroying them as dates. In either case, if you can change it at the source, that should solve the problem. Or a short Applescript to convert the text back into dates.
What is it you need to do with them that requires them to be interpreted as Month/Year vs text?
I didn't think Numbers imported .ods files. Does it? I am wondering if it is truly a .ods file. Maybe it is a csv file in disguise. Does it have any formulas in any cells when imported (assuming it should have had some)?
When you click a cell with a "date" what do you see in the lower left corner of the Numbers window?
SG
"Text" and the data (e.g. "Aug-19") before defining the custom date.
"Actual" and "19/08/2020 00:00:00" after.
Yep, it seems to have assumed the yy bit ("19") was a day.
andykent99 wrote:
"Actual" and "19/08/2020 00:00:00" after.
That seems to be a date-time. Is that not what you want?
(If Numbers can't find a year it assumes the current year).
SG
(If Numbers can't find a year it assumes the current year)
Precisely. Therein lies the problem. I want it to realise that the yy component is, erm, a year. Not a day.
It is inconsistent in my testing. Jan-05 correctly becomes Jan 1, 2005 but Jan-10 incorrectly becomes Jan 10, 2020. It works correctly for 01 through 09 but breaks at 10 and above.
Where are you getting the data? Often the solution lies in having the data source produce an unambiguous format for Numbers. yyyy-mm-dd usually works. Others do too.
SG
You can create a custom format with a 2 digit date
Can you tell me where that is please? Creating a custom date format only gives me the 4 digit option.
How to handle dates in mmm-yy format?
