Date Format Within Text

[ExcelRoy]

Hi Guys,

I need a date format with some text

my formula is

="( Registration from "&Date_P1_From&", Start Of Season )"

"Date_P1_From being a defined name with a date

The formula shows 40040 instead of the date?

Any help appreciated

 

[VoG]

Try

="( Registration from "&TEXT(Date_P1_From,"dd/mm/yyyy")&", Start Of Season )"

 

[ExcelRoy]

Thanks VoG

 

[ExcelRoy]

Is there a way to add "th" and "st" or "nd" into the date

ie "20th August"

Thanks

 

[VoG]

See if you can adapt this http://www.excelforum.com/excel-general/677702-date-format-of-just-st-nd-rd-and-th.html

 

[VoG]

This seems to work

="( Registration from "&TEXT(Date_P1_From,"d")&MID("stndrdthstndrdthst",MATCH(DAY(Date_P1_From),{1,2,3,4,21,22,23,24,31},1)*2-1,2)&" "&TEXT(Date_P1_From,"mmmm yyyy")&", Start Of Season )"