Reverse cell value and convert to date

P

PW

Hello newsgroup users and Gurus.

Your kind assistance please.

This is a challenge.

I have a column with a text string which is a date. i'e.

30121998= 30th December 1998.

I would like to convert the text string into a actual date
format.

A tricky one I know, but is it possible.

Regards
Paul
 
F

Frank Kabel

Hi
one way: use the following formula in an adjacent cell:
=DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))
and copy down
 
T

Thomas Ramel

Grüezi PW

PW schrieb am 10.06.2004
I have a column with a text string which is a date. i'e.

30121998= 30th December 1998.

I would like to convert the text string into a actual date
format.
Click to expand...

Try the following formula in an column:

=DATEVALUE(TEXT(A1,"00-00-0000"))

and format the cell(s) with 'dd.mmmm yyyy'

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]
 
F

Frank Kabel

Hi Thomas
this kind of function could return wrong results if the regional date
settings are for example
MM-DD-YYYY
and you have a date like
03042004
which should be
03-Apr-2004
but would be interpreted as
04-Mar-2004

Personally I'd trust DATEVALUE only for a format like
YYYY-MM-DD
 
T

Thomas Ramel

Grüezi Frank

Frank Kabel schrieb am 10.06.2004
this kind of function could return wrong results if the regional date
settings are for example
MM-DD-YYYY
Click to expand...

I was afraid so - but would the OP give it a try, because his notatin of
the date looked like the 'European-way'.

I'm aware of the date-problems and its international-issues.

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]
 
F

Frank Kabel

Thomas said:
Grüezi Frank

Frank Kabel schrieb am 10.06.2004


I was afraid so - but would the OP give it a try, because his notatin
of the date looked like the 'European-way'.
Click to expand...

ack. In this case it probably should work

I'm aware of the date-problems and its international-issues.
Click to expand...

Regards
Frank
 
P

PW

Thomas, Frank, thank you both for your time.
You have have been helpful in solving my problem and also
given me an insight into other issues that I may come
across.

Regards
P
 
Top