convert numbers to dates

  • Thread starter Thread starter jimbo693
  • Start date Start date
J

jimbo693

i need to convert large groups of numbers into dates automatically bu
im stumped, the numbers are all in the format of 10.01.99 so they rea
as dates but excel wont recognise them as dates is there a way t
convert them all quickly to actual date
 
select the column of dates, then do Edit=>Replace
Replace What . (period)
Replace With \
 
here is one way assuming your date is in cell a4

=DATE(RIGHT(A4,2),LEFT(A4,2),RIGHT(LEFT(A4,5),2))
 
Tom,

/ might work better. :)

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Tom Ogilvy said:
select the column of dates, then do Edit=>Replace
Replace What . (period)
Replace With \
Click to expand...
 
Another way would be to use data>text to columns, click next twice,
under column data format select Date and MDY (assuming it is MDY,
change to fit) and click finish

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
I'm having a simular problem but the format I have is yyyymmdd example
20011214
colum B:3 to B:439
Thanks
 
Try selecting your range and doing:
Data|Text to columns
specify ymd as the format for that field
And then format that column the way you want. (mm/dd/yyyy or whatever)
 
Thanks for your reply Dave but unfortunatly that didn't work I end up with
a #Value in the cells.
I guess I'll just have to do it by hand
Cheers and thanks again
 
Are you sure it's #value?

'Cause if it were ########, maybe all you have (or had) to do is widen the
column??
 
Yes it was a $Value?
I also when trying different things like "format, cells, number, date"
had the ########## and tried widening the colum made no difference
I did find a neat little trick when i started to do it manually
Changed the cell into the date dmy
Aimed the cursor right hand bottom corner till it went + drag down with
right mouse button depressed
let go the button at the end
another box popped up
went series = 7
then chaged the date format to mdy
That did the trick
But thanks for your help, it was really appreictive

PS (I did have a few gaps which i had to go back and repeated that proccess
but i was managing to get about 8 months done at a time.)
 
Glad it worked.

No_One said:
Yes it was a $Value?
I also when trying different things like "format, cells, number, date"
had the ########## and tried widening the colum made no difference
I did find a neat little trick when i started to do it manually
Changed the cell into the date dmy
Aimed the cursor right hand bottom corner till it went + drag down with
right mouse button depressed
let go the button at the end
another box popped up
went series = 7
then chaged the date format to mdy
That did the trick
But thanks for your help, it was really appreictive

PS (I did have a few gaps which i had to go back and repeated that proccess
but i was managing to get about 8 months done at a time.)
Click to expand...
 
Back
Top