I am searching for a way to convert a column of European formatted dates to a US format in another column
Eksample:
5 Apr 2017 converted into Apr 5 2017
I have tried with custom date formating, but numbers is always reading 5/4/2017 as May 4 2017 no matter how I try to format the cells.
Any help will be much appreciated.
iMac, macOS Sierra (10.12.3)
I was thinking along somewhat similar lines as Ian—getting Text involved in the process.
Start by formatting all cells in the column to initially receive the EU formatted 'dates' as TEXT.
Paste the 'dates' into that column. As text, they should automatically align to the left of the column.
In the column to the right (add one it necessary) add the formula below, which reads the text string, and extracts the values specifying the year, month, and day, then passes those on to DATE, which constructs the date part of a Date&Time value. Numbers automatically adds the required time part, set to 00:00:00, and returns the d&t value to the cell with the formula, where it can be formatted as desired.
Formula: Copy all after the = sign. Click on cell B2, then press = to open Formula Editor. Paste.
=DATE(RIGHT(A2,4),MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)−FIND("/",A2) −1),LEFT(A2,FIND("/",A2)−1))
Fill down the rest of column B.
Regards,
Barry
Hi Walentin,
Numbers has trouble converting dates to display in a 'foreign' numbers only format (where 'foreign' here means 'outside the bounds of the Language and Region settings in System Preferences'.
You could try a 'Custom format' setting. Here's one for a numbers only date ordered as month/day/year.
It's made by dragging the three tokens, placing them in the order desired and typing in the separators, then setting each token to display as a number, unsing the menu accessed through the v visible on the token.
The custom format above is the one used in column of the table below. All cells in a row have the same date displayed. all except the first column are copied by formula from the cell to the left, then set to a different date part format.
Cells in the last full row have been copied, then pasted back into their cells using Paste Formula Results. The box below the table shows the actual content of the selected call.
As noted, I've found the numbers only date formats unstable. That may have been in a previous version of Numbers, though, as recent experience has been that dates in Numbers 3.6.2 have held their order quite well.
Regards,
Barry
Hi Walentin,
When all else fails, cheat.
This method may work for your USA friends on the other side of the pond.
My Language & Region settings are for Australia and I have a limited number of options for USA dates
Enter your dates in column A as EU format and use those to sort, calculate or whatever.
Then cheat by displaying USA date format in column D (hide the intermediate columns).
Formula in B2 (and Fill Down)
=YEAR(A2)
Formula in C2 (and Fill Down)
=A2 (with cells formatted as Month Day as per our USA friends).
Formula in D2 (the result will be Text, not a Date)
=C2&" "&B2
Use the real dates in column A for calculations.
Use the Text results in column D for your "Display" table.
Regards,
Ian.
P.S.
https://en.wikipedia.org/wiki/Date_format_by_country
Blue = Day Month Year
I tried to edit my previous post (above) but the system would not let me.
Her's my latest on the subject.
I still seem to have lot's of trouble with dates where the day value is above 12.
With much fiddling back and for the with formatting , typing and pasting, I was able to get some cells to work, but others, containing the same data, format etc, it for some reason will not work ?
Seems that cell A2 will for some reason not accept same formating as A1
Only by copying and pasting A1 into A2, will it work, but that is not a useful solution.
Thank you very much for the reply Barry.
I have made custom formats, and also had luck with the process you describe in the latter part of your reply.
Where I seem to be running into problems though, is when the day of the date is higher than 12.
F.ex. 23 jan 2017
Dates displaying days below 12 I have been able to successfully convert.
Thank you Yellowbox, but - again, just as in the suggestion by Barry, I am having troubles when the day value goes above 12.
I see that you have luck converting higher date values, but this is not working for me, no matter how I format column A
In my choice of formats, I only have US formats, and there seem no way that I can format column A (even with custom formating), without haven column B displaying an error. (again for day values above 12)
PS I am an European, but working in the US on a US OS,
importing (pasting) EU format dates into a US format numbers spreadsheet.
That worked beautifully. Thank you very much Barry.
It did solve my question, but having clicked "Helpful" there does not seem to be a way to go back og add "solved".
Now I will have to study your formula.
Thanks again
- Janusz
how to convert EU formatted date to US format
