Dates in Excel

N

Nick Hodge

T

Presuming data in column A, use a new empty column and enter

=DATEVALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4))

and format the resulting number as a sate, via Format>Cells...>Number

You can then copy these new dates and Paste Special...>Values and number
formats over the old ones

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
R

Ron Rosenfeld

If a date has been entered 20051103, is there a way to convert it to
11/03/2005?
Click to expand...

=DATE(INT(A1/10^4),INT(MOD(A1,10^4)/100),MOD(A1,100))

or

=--TEXT(A1,"0000\/00\/00")

The first will convert "illegal" dates, such as 20050132 --> 1 Feb 2005

The second will give an error for "illegal" dates.
--ron
 
R

RagDyer

Select the column containing the dates, then:
<Data> <TextToColumns> <Next> <Next>,
Click on "Date", and expand the date window,
And choose "YMD",
Then <Finish>

This will give you "legal" XL dates,
Which you can the format any way you wish, if they didn't happen to come up
with the exact display that you wanted.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Top