Date Format

[ssraghunathan]

Team,


Need some clarifications on Date Format. I have the Date format in "dd-mmm-yyyy" and I would like to use certain datas for mail merge option. When I pull the above date format in the Word document, it shows me the numerical value of the date (i.e. some 33397, etc). I want the text in "dd month, yyyy" format in the word document (i.e. 11-mar-2011 should be read as 11th March, 2011).


What should I do in order to get the above format in word document. Should i require to alter my Excel sheet or word document itself.


Need your help.


Looking forward to your reply.


Thanks


Raghunathan S

 

[Hui]

I would add another field to your data for the mail merge date like MM_Date

In the field, use a Text formula like

=Text(A1,"dd-mmm-yyyy") where A1 is the normal date field

This will enable Word to import the date as a Text value not as a number.

 

[ssraghunathan]

Dear Hui,


Thanks. Its working fine now. I have one more query on this. Is there any way to add date suffix (viz. date followed by st for 1, nd for 2, rd for 3, th for 4 and so on) in the format ?


Thanks btw


Regards


Raghunathan

 

[Luke M]

Taking the ordinal function from CPearson's site (link below), slicing it into Hui's formula, you get:

=TEXT(A1,"dd")&IF(AND(MOD(DAY(A1),100)>=10,MOD(DAY(A1),100)<=14),"th",

CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&TEXT(A1," mmm, yyyy")


Link:

http://www.cpearson.com/excel/ordinal.htm