software

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

Thanks,

Brett--
supersonicf111
------------------------------------------------------------------------
supersonicf111's Profile: www.excelforum.com/member.php...foamp;userid=4330
View this thread: www.excelforum.com/showthread...hreadid=497274Do you mean 010106? If so, Format - Cells - Date - under Type scroll down to
the appropriate format.

--

JoAnn Paules
MVP Microsoft [Publisher]
quot;supersonicf111quot;
lt;supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.comgt; wrote in
message news:supersonicf111.20z9ky_1136155500.7119@excelfo rum-nospam.com...
gt;
gt; I need some help with this one. Tried everything I can think of.
gt;
gt; Thanks,
gt;
gt; Brett
gt;
gt;
gt; --
gt; supersonicf111
gt; ------------------------------------------------------------------------
gt; supersonicf111's Profile:
gt; www.excelforum.com/member.php...foamp;userid=4330
gt; View this thread: www.excelforum.com/showthread...hreadid=497274
gt;

One way:

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

HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=497274On Sun, 1 Jan 2006 16:44:21 -0600, supersonicf111
lt;supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.comgt; wrote:

gt;
gt;I need some help with this one. Tried everything I can think of.
gt;
gt;Thanks,
gt;
gt;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

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

=--TEXT(A1,quot;00\-00\-00quot;)

if not a typo and you want to add one year

=DATE(YEAR(TEXT(A1,quot;00\-00\-00quot;)) 1,MONTH(TEXT(A1,quot;00\-00\-00quot;)),DAY(TEXT(A1,quot;00\-00\-00quot;)))

--
Regards,

Peo Sjoblom

(No private emails please)quot;supersonicf111quot;
lt;supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.comgt; wrote in
message news:supersonicf111.20z9ky_1136155500.7119@excelfo rum-nospam.com...
gt;
gt; I need some help with this one. Tried everything I can think of.
gt;
gt; Thanks,
gt;
gt; Brett
gt;
gt;
gt; --
gt; supersonicf111
gt; ------------------------------------------------------------------------
gt; supersonicf111's Profile:
gt; www.excelforum.com/member.php...foamp;userid=4330
gt; View this thread: www.excelforum.com/showthread...hreadid=497274
gt;quot;Ron Rosenfeldquot; gt; wrote in message
...
gt; On Sun, 1 Jan 2006 16:44:21 -0600, supersonicf111
gt; lt;supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.comgt; wrote:
gt;
gt; gt;
gt; gt;I need some help with this one. Tried everything I can think of.
gt; gt;
gt; gt;Thanks,
gt; gt;
gt; gt;Brett
gt;
gt; with 010105 in K1:
gt;
gt; =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)lt;70) MOD(K1,100),INT(K1/10^4),MOD(INT(K1/100),100))

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: www.excelforum.com/member.php...foamp;userid=4330
View this thread: www.excelforum.com/showthread...hreadid=497274
This should give you an acurate date:

=ABS(LEFT(A1,2)amp;quot;/quot;amp;MID(A1,3,2)amp;quot;/quot;amp;RIGHT(A1,2))
format cell as date.

Regards
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=497274
Ooops....should be:

=ABS(MID(A1,3,2)amp;quot;/quot;amp;LEFT(A1,2)amp;quot;/quot;amp;RIGHT(A1,2))

sorry about that.

Regards
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=497274On Sun, 1 Jan 2006 23:25:46 -0000, quot;Bob Phillipsquot;
gt; wrote:

gt;
gt;
gt;
gt;quot;Ron Rosenfeldquot; gt; wrote in message
.. .
gt;gt; On Sun, 1 Jan 2006 16:44:21 -0600, supersonicf111
gt;gt; lt;supersonicf111.20z9ky_1136155500.7119@excelforu m-nospam.comgt; wrote:
gt;gt;
gt;gt; gt;
gt;gt; gt;I need some help with this one. Tried everything I can think of.
gt;gt; gt;
gt;gt; gt;Thanks,
gt;gt; gt;
gt;gt; gt;Brett
gt;gt;
gt;gt; with 010105 in K1:
gt;gt;
gt;gt; =DATE(1 MOD(K1,100),INT(K1/10^4),MOD(INT(K1/100),100))
gt;
gt;That gives 1906. I guess you mean something like
gt;
gt;=DATE(100*(MOD(K1,100)lt;70) MOD(K1,100),INT(K1/10^4),MOD(INT(K1/100),100))
gt;

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