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