Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have column temp which contain date in 'mm/dd/yyyy' formate and i want to change it in this 'Monday1st January 2011' formate.
I have done every thing apart from this st or rd or th .
How can i apply this string with date.
Thanks in advance
First, add your day numbers as a mapping table:
DayNumbers:
Mapping Load * inline
[Day, DayNumber
1, 1st
2, 2nd
3, 3rd
...
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 .