Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •