Convert 010105 mmddyy text to 01/01/06
 

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

Thanks,

Brett


--
supersonicf111
------------------------------------------------------------------------
supersonicf111's Profile: http://www.excelforum.com/member.php...fo&userid=4330
View this thread: http://www.excelforum.com/showthread...hreadid=497274

Convert 010105 mmddyy text to 01/01/06
 

Do you mean 010106? If so, Format - Cells - Date - under Type scroll down to
the appropriate format.

--

JoAnn Paules
MVP Microsoft [Publisher]



"supersonicf111"
<supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.com wrote in
message news:supersonicf111.20z9ky_1136155500.7119@excelfo rum-nospam.com...

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

Thanks,

Brett


--
supersonicf111
------------------------------------------------------------------------
supersonicf111's Profile:
http://www.excelforum.com/member.php...fo&userid=4330
View this thread: http://www.excelforum.com/showthread...hreadid=497274

Convert 010105 mmddyy text to 01/01/06
 

One way:

=DATE(RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497274

Convert 010105 mmddyy text to 01/01/06
 

On Sun, 1 Jan 2006 16:44:21 -0600, supersonicf111
<supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.com wrote:


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

Thanks,

Brett

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

Convert 010105 mmddyy text to 01/01/06
 

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"
<supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.com wrote in
message news:supersonicf111.20z9ky_1136155500.7119@excelfo rum-nospam.com...

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

Thanks,

Brett


--
supersonicf111
------------------------------------------------------------------------
supersonicf111's Profile:
http://www.excelforum.com/member.php...fo&userid=4330
View this thread: http://www.excelforum.com/showthread...hreadid=497274

Convert 010105 mmddyy text to 01/01/06
 

"Ron Rosenfeld" wrote in message
...
On Sun, 1 Jan 2006 16:44:21 -0600, supersonicf111
<supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.com wrote:


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

Thanks,

Brett

with 010105 in K1:

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

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))

Convert 010105 mmddyy text to 01/01/06
 

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!


--
supersonicf111
------------------------------------------------------------------------
supersonicf111's Profile: http://www.excelforum.com/member.php...fo&userid=4330
View this thread: http://www.excelforum.com/showthread...hreadid=497274

Convert 010105 mmddyy text to 01/01/06
 

This should give you an acurate date:

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

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497274

Convert 010105 mmddyy text to 01/01/06
 

Ooops....should be:

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

sorry about that.

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497274

Convert 010105 mmddyy text to 01/01/06
 

On Sun, 1 Jan 2006 23:25:46 -0000, "Bob Phillips"
wrote:




"Ron Rosenfeld" wrote in message
.. .
On Sun, 1 Jan 2006 16:44:21 -0600, supersonicf111
<supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.com wrote:


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

Thanks,

Brett

with 010105 in K1:

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

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))


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


--ron

Convert 010105 mmddyy text to 01/01/06
 

On Sun, 1 Jan 2006 23:25:46 -0000, "Bob Phillips"
wrote:




"Ron Rosenfeld" wrote in message
.. .
On Sun, 1 Jan 2006 16:44:21 -0600, supersonicf111
<supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.com wrote:


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

Thanks,

Brett

with 010105 in K1:

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

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))


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