This is probably a stupid question, but I still havent found out how to do this...
I have a column with numbers:
19990101
19990315
etc
Now I want to convert them to dates: 1 jan 1999, 15 mar 1999, etc.
How to do that?
I cannot convert them with cell properties to a custom format yyyymmdd, I get ########### then.
I also tried to make a new column, format it as yyyymmdd and then paste these values as numbers but that also doesn't work...
How to do?
Hi,
=VALUE(CONCATENATE(RIGHT(A1,2),"/",MID(A1,5,2),"/",LEFT(A1,4)))
will return a date serial number that you can format as per usual
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Thanks! This works.
However, I was wondering if a more simple method exists.
Like formatting a cell as yyyymmdd and just dropping a value onto it. Something like that....
Anyway, I just use your method now.
Thanks again.
Excel uses numbers as dates
If you enter a date into a cell then change its formatting to number you will see a date entered as january 1 2007 will now be displayed as 39083. I think this is the number of days since 1 january 1900
Please Read Forum Rules Before Posting
Wrap VBA code by selecting the code and clicking the # icon or Read This
How To Cross Post politely
Top Excel links for beginners to Experts
If you are pleased with a member's answer then use the Scales icon to rate it
If my reply has assistedor failed to assist you
I welcome your Feedback.
Hi,
If you don't ask you may never find out...
The display of #### can be because your column is not wide enough for the result but that doesn't seem to be the case here.
The below formula is slightly shorter than Sweep's (& still assumes that the yyyymmdd data is in cell A1):
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Rob
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Yes it does. You can convert the data "in situ"Originally Posted by MrBear
Select column of "dates". Data > Text to Columns > Next > Next > at step 3 select "Date" under "column data format" and "YMD" > Finish
Otherwise, with a formula,
=TEXT(A1,"0000-00-00")+0
format as date
Hey Daddy Longlegs,
Your in situ option is fantastic!
I've never seen it before but I will definitely be making use of it in the future &, even better, it also appears to create some valid & easily modifiable code when the macro recorder is used.
Thanks
Rob
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks