Problems with date when using =CONCATENATE formula

[EdGilmore]

I am trying to use =CONCATENATE formula to string together a large amount of information in a text string, but the date is returning as a number. For example:

01/03/2011 returns 40603
02/03/2011 returns 40604
03/03/2011 returns 40605
And so on

Is there any way that this can be corrected? I have tried amending the cell format to date format dd/mm/yyyy, but this still does not work.

Any ideas?

 

[delaneyjm]

Dates are actually numbers in Excel. You'll need to convert it to a text string first.

Appears your dates are in Day/Month/Year format so try this formula, assuming that data starts in cell A1,

=TEXT(A1, "mm/dd/yyyy")

You can then use the CONCATENATE formula, or you could use the & character which also serves as the concatenation operator.

 

[EdGilmore]

Thanks! Worked a treat

 

[WynandS]

Hi Delaney - i had a similar problem and this resolved it Thanx