text to date..!

[via135]

hi all..!

i am having dates in A1:A10
as numbers in text format like

12112009
19082008
16062008
21112005
04072006
18072007
18072007
14052007
22062007
09022009

in B1 =DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))
in C1 =TEXT(B1,"mm/dd/yyyy")

and thus getting the dated in A1 getting converted as 11/12/2009 in
C1.
its ok. but the problem i am facing is that while i am copying down
the formulae in B1 & C1 though B10 to C10 i am getting incorrect
dates in COL 'C' for the numbers starting with 0 (zero).
given the above example i am getting the dates as 01/09/2012 &
12/29/2010 instead of 07/04/2006 & 02/09/2009 in C5 & C10
respectively..!

what amendment i need to do in the formulae to reflect the correct
dates in Col "C"..?

any hlp pl..?

-via135

 

[Pete_UK]

I suspect that the leading zero for the value in A5 is being put there
by formatting, and that the underlying value is really the number
4072006. You formula will now take 40 days, 72 months and a year of
2006, which explains how you end up with 9th January 2012. Similarly,
the real value in A10 is 9022009, and your formula takes this as 90
days, 22 months added on to a year of 2009.

You can overcome it in many ways, but this ties in with other
functions you have used:

B1: =DATE(RIGHT(A1,4),MID(text(A1,"00000000"),
3,2),LEFT(text(A1,"00000000"),2))

Hope this helps.

Pete

 

[Ron Rosenfeld]

hi all..!

i am having dates in A1:A10
as numbers in text format like

12112009
19082008
16062008
21112005
04072006
18072007
18072007
14052007
22062007
09022009

in B1 =DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))
in C1 =TEXT(B1,"mm/dd/yyyy")

and thus getting the dated in A1 getting converted as 11/12/2009 in
C1.
its ok. but the problem i am facing is that while i am copying down
the formulae in B1 & C1 though B10 to C10 i am getting incorrect
dates in COL 'C' for the numbers starting with 0 (zero).
given the above example i am getting the dates as 01/09/2012 &
12/29/2010 instead of 07/04/2006 & 02/09/2009 in C5 & C10
respectively..!

what amendment i need to do in the formulae to reflect the correct
dates in Col "C"..?

any hlp pl..?

-via135

B1:
=DATE(RIGHT(A1,4),MID(A1,2+(A1>10^7),2),LEFT(A1,1+(A1>10^7)))

 

[Don Guillett Excel MVP]

hi all..!

i am having dates in A1:A10
as numbers in text format like

12112009
19082008
16062008
21112005
04072006
18072007
18072007
14052007
22062007
09022009

in B1 =DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))
in C1 =TEXT(B1,"mm/dd/yyyy")

and thus getting the dated in A1 getting converted as 11/12/2009 in
C1.
its ok. but the problem i am facing is that while i am copying down
the formulae in B1 & C1 though B10 to C10  i am getting incorrect
dates in COL 'C' for the numbers starting with 0 (zero).
given the above example i am getting the dates as 01/09/2012 &
12/29/2010 instead of 07/04/2006 & 02/09/2009 in C5 & C10
respectively..!

what amendment i need to do in the formulae to reflect the correct
dates in Col "C"..?

any hlp pl..?

-via135

=TEXT(DATE(RIGHT(IF(LEN(H1)=7,"0","")&H1,4),MID(IF(LEN(H1)=7,"0","")&H1,3,2),IF(LEN(IF(LEN(H1)=7,"0","")&H1=7),
0,"")&LEFT(IF(LEN(H1)=7,"0","")&H1,2)),"mm/dd/yyyy")

 

[via135]

=TEXT(DATE(RIGHT(IF(LEN(H1)=7,"0","")&H1,4),MID(IF(LEN(H1)=7,"0","")&H1,3,2 ),IF(LEN(IF(LEN(H1)=7,"0","")&H1=7),
0,"")&LEFT(IF(LEN(H1)=7,"0","")&H1,2)),"mm/dd/yyyy")

thanks for all the three excel masters..!
it all works perfectly and giving the exact results i wanted..!
thanks again..!

-via135