Hello guys, (and ladies as well)
I want to use the date -which is in Table A- in my spreadsheet Table B. The format of the column in table A is unknown but the date number looks like YYYYMMDD, and I want it to import it in B as 01-jan-2018
Table A
Table B
How can I (re-) format the imported data?
Thanks a lot in advance
Hans
iMac 27″, macOS 10.13
Hi hanyvo,
here is one option.
C2=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))
These functions are used to create the date:
Format as standard date
Or you could go with a custom format for the date, Create Custom Format is at the very end of the format list.
Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.
Hope this will solve your question, please let me know if this worked for you or if something in unclear.
Regards Ralf
You can try something like this or its equivalent in your language.
Import the dates as YYYYMMDD strings (left-aligned by default) as shown in your screenshot.
Then add a column and use the DATE function in a formula to convert to a true Date & Time Date Format in Numbers, right-aligned by default.
=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))
In my region I don't have a format like 01-Jan-2018 but you can pick the closed one in yours.
Once you have the dates as you want them you can "remove" the formula by selecting the cells with it, typing command-c to copy, followed by Edit > Paste Formula Results. Then delete the column with the string dates if you want.
SG
This is great, Ralf! Thank you so much for your time and effort and for explaining your solution. And it works as a breeze. Exactly what I was looking for and once again I'm not disappointed in this community. To "abuse" a part of a Beatles song: I should have known better ...
Again: thank you
Hans
You are very welcome.
Glad that I could help with your request.
Ralf
Dat & Time formatting with Numbers
