S
S S
I think you need to format the cell from text to date
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
Sandy Mann
Without any explanation in the body of the post I will assume form the
leading zero that the cell is formatted as text. If that is so then try the
formula:
=DATE(1900+RIGHT(E9,2)+(--RIGHT(E9,2)<=30)*100,MID(E9,3,2),LEFT(E9,2))
This is for British style dates. For American dates try:
=DATE(1900+RIGHT(E9,2)+(--RIGHT(E9,2)<=30)*100,LEFT(E9,2),MID(E9,3,2))
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
[email protected]
[email protected] with @tiscali.co.uk
leading zero that the cell is formatted as text. If that is so then try the
formula:
=DATE(1900+RIGHT(E9,2)+(--RIGHT(E9,2)<=30)*100,MID(E9,3,2),LEFT(E9,2))
This is for British style dates. For American dates try:
=DATE(1900+RIGHT(E9,2)+(--RIGHT(E9,2)<=30)*100,LEFT(E9,2),MID(E9,3,2))
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
[email protected]
[email protected] with @tiscali.co.uk
J
JMB
This seemed to work okay (U.S format). Change the cell format to date.
=--(LEFT(E9,2)&"/"&MID(E9,3,2)&"/"&RIGHT(E9,2))
=--(LEFT(E9,2)&"/"&MID(E9,3,2)&"/"&RIGHT(E9,2))
G
Gord Dibben
Data>Text to Columns>Next>Next>Column Data Format>Date>DMY and Finish.
Gord Dibben MS Excel MVP
Gord Dibben MS Excel MVP