You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Hi -
Anyone out there bright enough to tell me how to alter the format of a date to, for example, today's date of 03-10-12 (I'm English) to 3rd October 2012, so that each of the dates has the appropriate suffix of -st, -nd, -rd, -th?
It's for a mail merge so there'll be loads of dates, not just today's.
Many thanks...
Adam
---
Hi Adam,
The Date formats offered in the Date and Time options in the Cell Format Inspector may be different for your localization, but I suspect from your question that the two letter suffixes aren't offered there either. (I'm in Canada.)
I don't think you can do it with formatting, but you can use a formula to construct a text string showing the date in the format you want, then use the column containing that format for your merge source.
Formula (entered in C2, filled down from there. Dates in column B)"
C2: =DAY(B)&CHOOSE(MOD(DAY(B),10)+1,"th","st","nd","rd","th","th","th","th","th","t h")&" "&MONTHNAME(MONTH(B))&" "&YEAR(B)
Functions used are described in the iWork Formulas and Functions User Guide, which you can download via the Help menu in Numbers '09.
Regards,
Barry
There is no format that will add "th", "nd" or "rd" etc.
You can make a new column which will take as input the dates and output the date like:
B2=DAY(A2)&CHOOSE(MOD(DAY(A2), 10)+1, "th", "st", "nd", "rd", "th", "th", "th", "th", "th", "th")&" "&MONTHNAME(MONTH(A2)) & " "& YEAR(A2)
select B2 and fill down
nice solution 🙂
so is yours 🙂
Thank you both so much. Unless I'm missing something, they're identical.
It falls over with 13rd and 23rd. How can that be corrected?
HI Adam,
I don't see an issue with 23rd. Do you mean 11th,12th and 13th?
Here's a revision to account for those three exceptions:
=DAY(B)&IF(AND(DAY(B)>10,DAY(B)<14),"th",CHOOSE(MOD(DAY(B),10)+1,"th","st","nd", "rd","th","th","th","th","th","th"))&" "&MONTHNAME(MONTH(B))&" "&YEAR(B)
Regards,
Barry
Fabulous. Thank you both so much.
An alternate approach that is less slick but easier to comprehend is to use a lookup table:
Date Format
