Text to Date

  • Thread starter Thread starter webarama
  • Start date Start date
W

webarama

This is probably pretty simple, but I am a new user and having troubl
with this;

I have some data in this format;

Jul 17 2008 (text)

I want to change it to date format. I have tried simply formatting an
changing to date format, but it seems not to recognise the data as
date.

Any ideas
 
Try entering a 1 in a spare cell. Copy it and highlight the 'dates'. Now
go to edit>paste special>Values+Add. This should change them to a date
'number'. Now format via Format>Cells...Number

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
This is probably pretty simple, but I am a new user and having trouble
with this;

I have some data in this format;

Jul 17 2008 (text)

I want to change it to date format. I have tried simply formatting and
changing to date format, but it seems not to recognise the data as a
date.

Any ideas?
Click to expand...

If Nick's version doesn't work, and if =DATEVALUE(A1) doesn't work, you could
try this kludge:


=DATE(RIGHT(A10,4),MATCH(LEFT(A10,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),MID(A10,5,2))


--ron
 
Oooppsssss Should say Paste special...MULTIPLY...not ADD sorry! If you've
already done it, do the same again but subtract

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


Nick Hodge said:
Try entering a 1 in a spare cell. Copy it and highlight the 'dates'. Now
go to edit>paste special>Values+Add. This should change them to a date
'number'. Now format via Format>Cells...Number

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
Click to expand...
 
If Nick's version doesn't work, and if =DATEVALUE(A1) doesn't work, you could
try this kludge:


=DATE(RIGHT(A10,4),MATCH(LEFT(A10,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),MID(A10,5,2))
Click to expand...

Oh, in the formula I had the date in A10. You'll need to change that.

--ron
Click to expand...

--ron
 
Web,

Nicks suggestion makes Excel coerce the text to a number. But if your text
dates are missing the comma, you may have to provide it. Try the following
formula in another cell:

=--SUBSTITUTE(A2," ",", ",2)

The -- is double negation. It's another way to force coercion to a number.
A little more fanciful, eh? :) Now format the cell (Format - Cells -
Number - Date) for the date format desired.

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

Nick Hodge said:
Oooppsssss Should say Paste special...MULTIPLY...not ADD sorry! If you've
already done it, do the same again but subtract

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
Click to expand...
 
Back
Top