Howdie,

I have the date that has been imported, and it is displayed as '20
January 2006

Now I know that the ' defines it as text

Question is how to a make it a number(ie a date format) ...? Find and
Replace ' doesnt work, nor does formatting cells....???

Thanks

D

Thanks


*** Sent via Developersdex http://www.developersdex.com ***

In VBA:

Activecell.Value = Activecell.Value

HTH

"Darin Kramer" wrote in message
...

Howdie,

I have the date that has been imported, and it is displayed as '20
January 2006

Now I know that the ' defines it as text

Question is how to a make it a number(ie a date format) ...? Find and
Replace ' doesnt work, nor does formatting cells....???

Thanks

D

Thanks


*** Sent via Developersdex http://www.developersdex.com ***

That changed one of my funny dates to a number, but no others.... any
idea why?

*** Sent via Developersdex http://www.developersdex.com ***

Smart Dates in excel are numbers;
Now Format the number as Date;


"Darin Kramer" wrote in message
...


That changed one of my funny dates to a number, but no others.... any
idea why?

*** Sent via Developersdex http://www.developersdex.com ***

Thanks, my question better phrased is why when I applied the VBA to 100
funny dates, it only changed 1 of those funny dates to a number...?


*** Sent via Developersdex http://www.developersdex.com ***

If it presented literally a number and not a date as most people view a date,
then it would be because the cell format was not set to some format that
would display a date. A specific example would be that the cell format was a
number and not a date. I don't think that the cell format must be a date
format, but it is an example where a number entered (i.e. 36784) would appear
as a date (9/15/2000). If you got the reverse, that only one cell showed up
as a date, and the rest remained as text like 20 January 2005, then that's a
separate issue to which I currently have no answer... Sorry. :)

"Darin Kramer" wrote:

Thanks, my question better phrased is why when I applied the VBA to 100
funny dates, it only changed 1 of those funny dates to a number...?


*** Sent via Developersdex http://www.developersdex.com ***

My example, using activecell - only performs the conversion on the active
cell, thus your-one-cell...
If you highlight the range of cells you wish to Convert and use
from the immediate window:
Selection.value = Selection.value (press enter key)

All highlighted cells will be converted to numbers, where you can format as
Dates
if excel doesn't do it for you..

HTH


"Darin Kramer" wrote:

Thanks, my question better phrased is why when I applied the VBA to 100
funny dates, it only changed 1 of those funny dates to a number...?


*** Sent via Developersdex http://www.developersdex.com ***

If you use the following functions to extract the day month and year:

=day(cell ref)
=month(cellref)
=year(cellref)

then use the date function selecting the above cell (E.G
date(daycellref,monthcellref,yearcellref).

That should convert it to a serial number.

Nic

--
nickysquawke
-----------------------------------------------------------------------
nickysquawkes's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=50286

Using day, month or year I get a #value error...?


*** Sent via Developersdex http://www.developersdex.com ***

With date in A1:

Range("b1") = CDate(Range("a1"))

"Darin Kramer" wrote:


Howdie,

I have the date that has been imported, and it is displayed as '20
January 2006

Now I know that the ' defines it as text

Question is how to a make it a number(ie a date format) ...? Find and
Replace ' doesnt work, nor does formatting cells....???

Thanks

D

Thanks


*** Sent via Developersdex http://www.developersdex.com ***

In the worksheet (not VBA) use the =DATEVALUE() function. It does a good job
of converting text into a real date (and doesn't care about leading
apostrophes)
--
Gary''s Student


"Darin Kramer" wrote:


Howdie,

I have the date that has been imported, and it is displayed as '20
January 2006

Now I know that the ' defines it as text

Question is how to a make it a number(ie a date format) ...? Find and
Replace ' doesnt work, nor does formatting cells....???

Thanks

D

Thanks


*** Sent via Developersdex http://www.developersdex.com ***