Date Format

[Mjoza]

Hello,

I have a spreadsheet with date in the following format 5-Jun-11 and I want to change the following format 5/06/11. I know I can just change the formating from the ribbon. Whats making this a challenge is the I have a concartenate in another cell thats pulling those dates and its giving the dates as numbers. To make is work I have to manually insert the ' on every cell with the date to bring the date in the format I want.

I have tried ="'"&A5. I'm using excel 2007.

Thanks.

 

[VoG]

Maybe

=A5&0

 

[Mjoza]

That just added a 0 to the end of the number. I have like 1000 rows that I need to change.

Thanks

 

[VoG]

Sorry, +0

 

[Rick Rothstein]

If I read your question correctly, your main problem is in the other cell where you are concatenating text with the date. If I am right, there is no need to change anything in the date cells. To retrieve the date for the concatentation, just use the TEXT function. Assuming the date is in A1...

="The date is "&TEXT(A1,"d/mm/yyyy")

 

[Mjoza]

That did exactly what I wanted thank you so much!