day/month/year in incorrect format for date format

Format as date and add -- in front of "31/1/2005" however if you have US
regional settings you will always get text since there is no month 23 in
that case you need to change the concatenate formula or the regional
settings

--
Regards,

Peo Sjoblom

(No private emails please)


"M&A_Jack" <[email protected]> wrote in message
news:[email protected]...
>I have a date format written as "31/1/2005", when I concatenate after I've
> separated text to columns I can't change format to 31-Jan-2005.

Suppose your 3 cells were A1, A2 & A3.
Use
=datevalue(concatenate(A1 & "/" & A2 & "/" & A3))
This turns the text into a date number. It can then be changed with Format -
Cell -Date and the date system you want to use.

Hope this helps

"M&A_Jack" wrote:

> I have a date format written as "31/1/2005", when I concatenate after I've
> separated text to columns I can't change format to 31-Jan-2005.

I have a date format written as "31/1/2005", when I concatenate after I've
separated text to columns I can't change format to 31-Jan-2005.

Format as date and add -- in front of "31/1/2005" however if you have US
regional settings you will always get text since there is no month 23 in
that case you need to change the concatenate formula or the regional
settings

--
Regards,

Peo Sjoblom

(No private emails please)


"M&A_Jack" <[email protected]> wrote in message
news:[email protected]...
>I have a date format written as "31/1/2005", when I concatenate after I've
> separated text to columns I can't change format to 31-Jan-2005.

Suppose your 3 cells were A1, A2 & A3.
Use
=datevalue(concatenate(A1 & "/" & A2 & "/" & A3))
This turns the text into a date number. It can then be changed with Format -
Cell -Date and the date system you want to use.

Hope this helps

"M&A_Jack" wrote:

> I have a date format written as "31/1/2005", when I concatenate after I've
> separated text to columns I can't change format to 31-Jan-2005.