Convert 010105 mmddyy text to 01/01/06

S

supersonicf111

I need some help with this one. Tried everything I can think of.

Thanks,

Bret
 
R

Ron Rosenfeld

I need some help with this one. Tried everything I can think of.

Thanks,

Brett
Click to expand...

with 010105 in K1:

=DATE(1+MOD(K1,100),INT(K1/10^4),MOD(INT(K1/100),100))

will add a year to that date. It'll work whether the contents of K1 is a
number or text.

Format it as mm/dd/yy

If the date in K1 is 29 Feb (e.g: 022904), the result of the formula will be 1
Mar of the following year. If you want something different, psot back.


--ron
 
P

Peo Sjoblom

If you had a typo and want 01/01/05 you can use

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

if not a typo and you want to add one year

=DATE(YEAR(TEXT(A1,"00\-00\-00"))+1,MONTH(TEXT(A1,"00\-00\-00")),DAY(TEXT(A1,"00\-00\-00")))

--
Regards,

Peo Sjoblom

(No private emails please)


"supersonicf111"
 
B

Bob Phillips

Ron Rosenfeld said:
with 010105 in K1:

=DATE(1+MOD(K1,100),INT(K1/10^4),MOD(INT(K1/100),100))
Click to expand...

That gives 1906. I guess you mean something like

=DATE(100*(MOD(K1,100)<70)+MOD(K1,100),INT(K1/10^4),MOD(INT(K1/100),100))
 
S

supersonicf111

Pinmaster's formula gave me 1900's dates, so I just modified it to
=DATE(RIGHT(A2222,2)+2000,LEFT(A2222,2),MID(A2222,3,2)), and it worked
great. Thanks JG!

JoAnn, I had tried your idea before and got wierd dates like 8/20/2054
for 093005.

Thanks everyone else. I will give yours a try also!
 
P

pinmaster

This should give you an acurate date:

=ABS(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))
format cell as date.

Regards
JG
 
P

pinmaster

Ooops....should be:

=ABS(MID(A1,3,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))

sorry about that.

Regards
JG
 
R

Ron Rosenfeld

That gives 1906. I guess you mean something like

=DATE(100*(MOD(K1,100)<70)+MOD(K1,100),INT(K1/10^4),MOD(INT(K1/100),100))
Click to expand...

That's probably more in keeping with what the OP really wants. :))


--ron
 
R

Ron Rosenfeld

That gives 1906. I guess you mean something like

=DATE(100*(MOD(K1,100)<70)+MOD(K1,100),INT(K1/10^4),MOD(INT(K1/100),100))
Click to expand...

Oops, don't forget to add 1 to the year.

He wanted to convert 010105 --> 01/01/06

=DATE(100*(MOD(K1,100)<70)+MOD(K1,100)+1,INT(K1/10^4),MOD(INT(K1/100),100))


--ron
 
Top