Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
View this thread: http://www.excelforum.com/showthread...hreadid=567496

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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
View this thread: http://www.excelforum.com/showthread...hreadid=567496



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default 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
View this thread: http://www.excelforum.com/showthread...hreadid=567496


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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
View this thread: http://www.excelforum.com/showthread...hreadid=567496


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
View this thread: http://www.excelforum.com/showthread...hreadid=567496

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert date format noel Excel Discussion (Misc queries) 1 March 26th 06 11:57 PM
Convert a Date to Text? Dave76Mizzou Excel Worksheet Functions 1 March 9th 06 01:32 AM
Excel 2000 worksheet where I want to convert date format Russell-stanely Excel Discussion (Misc queries) 5 December 19th 05 02:02 AM
How do I convert a text string into a date? JJMCDD02 Excel Worksheet Functions 4 November 25th 05 12:35 PM
Macro to convert text to date Nortos Excel Worksheet Functions 2 May 11th 05 10:42 AM


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"