1. ## Adding th, st, rd to date (2003)

I have a list of dates in column A. In column B, I would like to add text for example: Scheduled date of Service - Thursday, 25th January, 2006. I would like to add the th, st, rd etc to the day number. I have the following formula for that but I don't know how to incorporate the text etc.
If I have a list of numbers in column A, the following formula will add the ending to that number but I want to do this to a date. Can I do this?
=A1&IF(AND(MOD(A1,100)>=10,MOD(A1,100)<=14),"th",
CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th"))

2. ## Re: Adding th, st, rd to date (2003)

Try this:
<code>="Scheduled date of service - "&TEXT(A1,"dddd, ")&TEXT(A1,"d")&IF(AND(DAY(A1)>=10,DAY(A1)<=14),"t h",
CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th", "th","th","th","th","th"))&TEXT(A1," mmmm, yyyy")</code>

HTH

3. ## Re: Adding th, st, rd to date (2003)

I think this will work: (all one line)
=TEXT(A1,"dddd, d")&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,"mmmm, yyyy")

Steve

4. ## Re: Adding th, st, rd to date (2003)

Try
<code>
=TEXT(A1,"dddd, d")&IF(AND(MOD(A1,100)>=10,MOD(A1,100)<=14),"th",C HOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th"," th","th","th","th"))&TEXT(A1," mmmm yyyy")
</code>
Note: the browser may break the formula, but it should be one line.

5. ## Re: Adding th, st, rd to date (2003)

Rory's solution worked. Thank you so much. Thanks to everyone for helping.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•