First, add your day numbers as a mapping table:
Mapping Load * inline
You will need 31 rows in this table. Then, you can format the date by using e.g.
dual(WeekDay(DateField) &' '& applymap('DayNumbers', Day(DateField)) &' '& Date(DateField, 'MMM YYYY'), DateField)
Thanks for the answer
bt my problem is how to add st or nd or rd.
for it i have used
if(Date(Temp,'dd')=01 or Date(Temp,'dd')=21 or Date(Temp,'dd')=31,'st',if(Date(Temp,'dd')=02 or Date(Temp,'dd')=22,'nd',if(Date(Temp,'dd')=03 or Date(Temp,'dd')=23,'rd','th')))
this statement and save it into a variable but it is not working.
I think that the best way to add 'st', 'nd' and 'rd' is to use applymap in the way described above. You could of course instead have a mapping table where 1 is mapped to 'st' only (instead of '1st') but the basic solution will be very similar to the above one.
A nested if-function will also work. But, you should not use the comparison Date(Temp,'dd')=01. The Date function will format the date to (the string) '01', but its numeric value will still be forty thousand something. Use instead: Date(Temp,'dd')='01' (string comparison), or better: Day(Temp)=1 .