Remember Me?

#1
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 1
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.

--
japorms
------------------------------------------------------------------------
japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544

#2
Posted to microsoft.public.excel.worksheet.functions
 Posts: n/a
How do I convert a text date into a real date format

Hi

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

Andy.

"japorms" wrote in
message ...

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

#3
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 1,998
Help: How do I convert a text date into a real date format

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

#4
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 10
Help: How do I convert a text date into a real date format

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

#5
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 1
Help: How do I convert a text date into a real date format

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

--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post noel Excel Discussion (Misc queries) 1 March 26th 06 11:57 PM Dave76Mizzou Excel Worksheet Functions 1 March 9th 06 01:32 AM Russell-stanely Excel Discussion (Misc queries) 5 December 19th 05 02:02 AM JJMCDD02 Excel Worksheet Functions 4 November 25th 05 12:35 PM Nortos Excel Worksheet Functions 2 May 11th 05 10:42 AM

All times are GMT +1. The time now is 09:00 PM.