Help: How do I convert a text date into a real date format

Hi Guys,

Does anyone know how to convert a date that's inputted in a text format?

Example: The date is entered as 62606, this means as 6/26/06. I just can't change the format into date because it will be different.

I'm thinking if there is some sort of formula to add a hyphen and turn it to 6-26-06. Like extracting, the last two digit, then the two middle, then the first

Hope someone can help me. Thanks in advance.

Hi

What if the 'date' is 10606? Is that 1/06/06 or is it 10/6/06?

Andy.

"japorms" <japorms.2bx5g4_1154532618.1006@excelforum-nospam.com> wrote in
message news:japorms.2bx5g4_1154532618.1006@excelforum-nospam.com...
>
> Hi Guys,
>
> Does anyone know how to convert a date that's inputted in a text
> format?
>
> Example: The date is entered as 62606, this means as 6/26/06. I just
> can't change the format into date because it will be different.
>
> I'm thinking if there is some sort of formula to add a hyphen and turn
> it to 6-26-06. Like extracting, the last two digit, then the two
> middle, then the first
>
> Hope someone can help me. Thanks in advance.
>
>
> --
> japorms
> ------------------------------------------------------------------------
> japorms's Profile:
> http://www.excelforum.com/member.php...fo&userid=6544
> View this thread: http://www.excelforum.com/showthread...hreadid=567496
>

Maybe this............

=IF(RIGHT(A1,2)*1<=6,DATE(RIGHT(A1,2)*1+2000,LEFT(A1,LEN(A1)-4)*1,MID(A1,LEN(A1)-3,2)*1),DATE(RIGHT(A1,2)*1+1900,LEFT(A1,LEN(A1)-4)*1,MID(A1,LEN(A1)-3,2)*1))

All on one line, watch out for wordwrap......

Vaya con Dios,
Chuck, CABGx3



"japorms" wrote:

>
> Hi Guys,
>
> Does anyone know how to convert a date that's inputted in a text
> format?
>
> Example: The date is entered as 62606, this means as 6/26/06. I just
> can't change the format into date because it will be different.
>
> I'm thinking if there is some sort of formula to add a hyphen and turn
> it to 6-26-06. Like extracting, the last two digit, then the two
> middle, then the first
>
> Hope someone can help me. Thanks in advance.
>
>
> --
> japorms
> ------------------------------------------------------------------------
> japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544
> View this thread: http://www.excelforum.com/showthread...hreadid=567496
>
>

Well do you need the date to be read as a date by Excel? or only by the
user?

If you don't need Excel to read the number as a date, then there is a
very simple way to solve your problem.

Right click in the cell where the date is to go.
Choose Format Cells from the dropdown.
Click the Number Tab
Category: Custom

In the Type: Box where it says "General" type
00-00-00
or
00/00/00
depending on whether you'd prefer hyphens or slashes.

And then Click OK.

You can now type in 062606 or 62606 and it will be changed to 06/26/06
or 06-26-06.
Excel won't read it as date, but it will look like a date.

It depends on what else you need the cell to do (i.e. do any other
cells refer to this cell in a formula?) If that is the case then this
won't work for you, but if you only need it to "look" like a date for
printed forms or whatever, this should work.



japorms wrote:
> Hi Guys,
>
> Does anyone know how to convert a date that's inputted in a text
> format?
>
> Example: The date is entered as 62606, this means as 6/26/06. I just
> can't change the format into date because it will be different.
>
> I'm thinking if there is some sort of formula to add a hyphen and turn
> it to 6-26-06. Like extracting, the last two digit, then the two
> middle, then the first
>
> Hope someone can help me. Thanks in advance.
>
>
> --
> japorms
> ------------------------------------------------------------------------
> japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544
> View this thread: http://www.excelforum.com/showthread...hreadid=567496

You could use this if your day value is always 2 digits (1st = 01, 2nd=02 etc..) So October 10 is 101006 and January 1 is 10106

=TEXT(A1,"00-00-00")*1

Format cells as mm/dd/yy.



HTH

Steve