I am needing to convert a large number of records with European (day, month, yr) dates to a US format (month, day, yr), but have no been able to find a way to do this.
Is there a reasonable way to do this ?
iMac, macOS Sierra (10.12.3)
Maybe try opening the document that has the European dates in it, then select the menu item "File > Advanced > Language & Region…":
Then change the selection to US (if it is not).
I do not know if this will work, but it may be worth a try
Hi,
With the cells you want to convert selected, pull down the Data Format menu in the Inspector and choose Create Custom Format...:
Drag the elements around and add text characters until you get what you require. I've called the custom format US Dates:
Click OK and the selected dates will be formatted as US dates:
This format is now available in the Data Format menu and can be applied to other cells in this document. If you want to use it across multiple documents you'll have to save a copy as a template.
Hope it helps,
H
I'm sorry Waltenin, I can't offer much help as it works fine for me with all days of the month:
etc.
But the fact that you say 'below 13' may be a hint that your months and days have already been switched round. Without seeing a bit more detail of what you're hoping to achieve (screen shots etc) it's a bit hard to know what to suggest.
Cheers,
H
Hi Walentin,
What do see under Format Panel > Cell > Data Format > Automatic (or better, Date & Time)?
To trouble-shoot date formats, I find it best to choose a built-in format to show the full date.
This is NOW() formatted as above as a test:
Also, what do you see in the Custom Format editor when you apply HD's suggestion?
Regards,
Ian.
I have again been looking at this problem, and have come to the following conclusion / resolution:
No matter how i worked the custom formatting (or chose a pre-formated), Numbers kept switching months and day values (for day values below 13) and made no changes for dates with day values above 13.
The solution finally was to change language/region on another computer, open the document there, and make a format change so the numeric month values displayed as text and in a US format (IE , 12=Dec, Dec.14 2017) copying this into my file on my US region iMac, kept the correct formating intact.
My thanks to all who weighed in on this, and very much also to Wayne Contello Who suggested to Change the language/region.
Much appreciated HD, - Thank you.
I do find however, that this works fine for dates where the day value is below 13. Thus, an EU date such as: 4/02/2017 (4 feb, 2017), converts fine to : Feb. 4 , 2017 , whereas a date like 20/02/2017 (20/Feb, 2017) will not change at all.
Thanks Wayne, this helped. I did not change the language / region locally, since i wasn't sure if it would only affect the current document or all other doc's. Changing language / region on another mac, however, did the trick.
Hmm, is there really no solution to this task for day values 13 and above ?
Convert EU date formats to US date formats in Numbers
