Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to convert text into date format?

In cell a1, it contains text "2024--01-2007", and I would like to convert
into 24-01-2007 for date format. Through this command,
RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10) is successfully converted to
"24-01-2007", but this text cannot be recognized as a date format. Does
anyone have any suggestion on how to convert this text into date format?
Thank for any suggestion
Eric
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default How to convert text into date format?

There's nothing wrong with your formula that I can see. If the quotes are
appearing in the result cell then I'd check the formatting of that cell to
ensure that they aren't being put back by a format that's been set up.

"Eric" wrote:

In cell a1, it contains text "2024--01-2007", and I would like to convert
into 24-01-2007 for date format. Through this command,
RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10) is successfully converted to
"24-01-2007", but this text cannot be recognized as a date format. Does
anyone have any suggestion on how to convert this text into date format?
Thank for any suggestion
Eric

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 196
Default How to convert text into date format?

Add a 0 onto the end ie:

=RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10)+0

this takes advantage of Excel's automatic conversion abilities.

Best regards

Richard

On 24 Jan, 13:26, Eric wrote:
In cell a1, it contains text "2024--01-2007", and I would like to convert
into 24-01-2007 for date format. Through this command,
RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10) is successfully converted to
"24-01-2007", but this text cannot be recognized as a date format. Does
anyone have any suggestion on how to convert this text into date format?
Thank for any suggestion
Eric


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default How to convert text into date format?

Use VALUE Function before your formual
=VALUE(RIGHT(SUBS......

"Eric" wrote:

In cell a1, it contains text "2024--01-2007", and I would like to convert
into 24-01-2007 for date format. Through this command,
RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10) is successfully converted to
"24-01-2007", but this text cannot be recognized as a date format. Does
anyone have any suggestion on how to convert this text into date format?
Thank for any suggestion
Eric

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to convert text into date format?

Thank everyone for suggestions

I have tried
=RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10)+0
=VALUE(RIGHT(SUBS......
Both statements return #VALUE!

TYPE(RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10)) = 2
TYPE(TODAY()) = 1

Does anyone how to convert type from 2 to 1?
Thank for any suggestions
Eric



"Ashkan" wrote:

Use VALUE Function before your formual
=VALUE(RIGHT(SUBS......

"Eric" wrote:

In cell a1, it contains text "2024--01-2007", and I would like to convert
into 24-01-2007 for date format. Through this command,
RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10) is successfully converted to
"24-01-2007", but this text cannot be recognized as a date format. Does
anyone have any suggestion on how to convert this text into date format?
Thank for any suggestion
Eric



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default How to convert text into date format?

If it return #value! your date format in your computer is not like dd-mm-yyy.
so 24-01-2007 was not date on your computer. Change Date format on your
computer by control pannel Regional and Language Option
Or
You must change 24-01-2007 to your Date format. For example if regional is
yyyy/mm/dd you must change your date to 2007/01/24.

"Eric" wrote:

Thank everyone for suggestions

I have tried
=RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10)+0
=VALUE(RIGHT(SUBS......
Both statements return #VALUE!

TYPE(RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10)) = 2
TYPE(TODAY()) = 1

Does anyone how to convert type from 2 to 1?
Thank for any suggestions
Eric



"Ashkan" wrote:

Use VALUE Function before your formual
=VALUE(RIGHT(SUBS......

"Eric" wrote:

In cell a1, it contains text "2024--01-2007", and I would like to convert
into 24-01-2007 for date format. Through this command,
RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10) is successfully converted to
"24-01-2007", but this text cannot be recognized as a date format. Does
anyone have any suggestion on how to convert this text into date format?
Thank for any suggestion
Eric

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How to convert text into date format?

One mo

=DATE(MID(A1,10,4),MID(A1,7,2),MID(A1,3,2))

And give it a nice Date format (format|Cells|Number tab)

Eric wrote:

In cell a1, it contains text "2024--01-2007", and I would like to convert
into 24-01-2007 for date format. Through this command,
RIGHT(SUBSTITUTE("2024--01-2007","--","-"),10) is successfully converted to
"24-01-2007", but this text cannot be recognized as a date format. Does
anyone have any suggestion on how to convert this text into date format?
Thank for any suggestion
Eric


--

Dave Peterson
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
Mileage Claim Formula johndavies New Users to Excel 4 August 14th 06 09:24 AM
turn off convert text to date (i.e. 4-9 to April 9, 2006) [email protected] Excel Worksheet Functions 4 June 13th 06 04:25 PM
Convert a Date to Text? Dave76Mizzou Excel Worksheet Functions 1 March 9th 06 01:32 AM
Convert Text Entry to Date Format User Excel Discussion (Misc queries) 1 November 16th 05 08:40 PM
Excel enters date as a text format Kane Excel Discussion (Misc queries) 3 March 22nd 05 09:20 PM


All times are GMT +1. The time now is 08:56 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"