Convert Date to Text

[mpeters]

I have entered the first column to be automatic days of the year 1-Jan to 31-Dec automatically so when referencing them it turns to the number code, e.g. 39448 for Jan 1, 2008. Since I want to use the cancantnate function just on the text '1-Jan' this isn't working. Any ideas to fix this?

 

[Gerald Higgins]

Something like

=left(text(a1,"dd-mmm-yyyy"),6)

to return 01-Jan when a1 contains the date for 1st Jan 2008.

The result of this formula can be used in a concatenation formula.

 

[barry houdini]

When concatenating try using the TEXT function, e.g. if A1 contains a date and B1 some text

=B1&TEXT(A1,"d-mmm")

 

[Mike Blackman]

Hi Gerald,

Could you not avoid the left function and format the cell like this?

=TEXT(A1,"dd-mmm")

EDIT. Used wrong format, should be "d-mmm" like Barry's

 

[Gerald Higgins]

Yes Mikey, indeed you could. I deliberately posted a fuller version of the TEXT formula to show what can be done with it, in case the OP was not familiar with it.

 

[Mike Blackman]

Thats cool Gerald.