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

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

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

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

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

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

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