+ Reply to Thread
Results 1 to 16 of 16

Date format of just "st" "nd" "rd" and "th"

  1. #1
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Date format of just "st" "nd" "rd" and "th"

    Hi,

    I have cell values of 1,2,3 etc that are used with other formula on a worksheet. However I would like these cells to display the format of 1st, 2nd, 3rd, 4th etc without any month or year values. Is this possible.

    Thank you
    Last edited by jpruffle; 03-28-2009 at 10:49 AM.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Date format of just "st" "nd" "rd" and "th"

    A1: (a whole number)

    This formula trans forms that value into an ordinal…eg 1st, 2nd, 11th, etc

    Please Login or Register  to view this content.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: Date format of just "st" "nd" "rd" and "th"

    That is fantastic works like a dream.

    Thank you

  4. #4
    Registered User
    Join Date
    04-25-2010
    Location
    Jerusalem, Israel
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Date format of just "st" "nd" "rd" and "th"

    Quote Originally Posted by Ron Coderre View Post
    A1: (a whole number)

    This formula trans forms that value into an ordinal…eg 1st, 2nd, 11th, etc

    Please Login or Register  to view this content.

    Is that something you can work with?
    I guess I am not doing it right. I got it to work 1st, 2nd, 3rd, But the format I want is;
    Sunday, April 11th. How can I get 4/11/2010 into that format for a whole list of dates?

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Date format of just "st" "nd" "rd" and "th"

    With A1 containing a date

    Try this formula:

    Please Login or Register  to view this content.

    Some sample A1, B1 values:
    04/01/10 Thursday, April 1st
    04/02/10 Friday, April 2nd
    04/03/10 Saturday, April 3rd
    04/04/10 Sunday, April 4th
    04/05/10 Monday, April 5th
    04/19/10 Monday, April 19th
    04/20/10 Tuesday, April 20th
    04/21/10 Wednesday, April 21st
    04/22/10 Thursday, April 22nd
    04/29/10 Thursday, April 29th
    04/30/10 Friday, April 30th
    05/01/10 Saturday, May 1st

    Does that help?

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Date format of just "st" "nd" "rd" and "th"

    I like to use LOOKUP, i.e.

    =TEXT(A1,"dddd, mmmm d")&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})

  7. #7
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Date format of just "st" "nd" "rd" and "th"

    Your less arcane approach got me to re-think mine.
    I came up with this:

    =TEXT(D1,"dddd, mmmm d")&MID("stndrdthstndrdthst",MATCH(DAY(D1),{1,2,3,4,21,22,23,24,31},1)*2-1,2)

  8. #8
    Registered User
    Join Date
    04-25-2010
    Location
    Jerusalem, Israel
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Date format of just "st" "nd" "rd" and "th"

    That works great!!!! Another friend of mine came up with:
    =TEXT(A1,"dddd, mmmm d")&MID("thstndrd",(MOD(DAY(A1),10)*AND(MOD(DAY(A1),10)<4,OR(DAY(A1)<11,DAY(A1)>20)))*2+1,2)

  9. #9
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Date format of just "st" "nd" "rd" and "th"

    Jpruffle,

    I had got this basic code somewhere in the forum...it does not belong to me..

    Just thought of adding to it though..

    Please Login or Register  to view this content.
    Regards
    E

  10. #10
    Registered User
    Join Date
    01-10-2013
    Location
    Nederland
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Date format of just "st" "nd" "rd" and "th"

    Hallo
    I have this code:
    Caption = format(Date, "mmmm") & ", " & format(Date, "dd") & Mid("thstndrdth", (DatePart("d", Date) Mod 10) * 2 + 1, 2) & ", " & format(Date, "yyyy")
    How get i the "thstndrdth" in Superscript?
    Regards
    Mark

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Date format of just "st" "nd" "rd" and "th"

    A formula cannot affect the format of a cell, so you can't have part of it in superscript (without VBA).

    Anyway, you shouldn't ask a question in someone else's thread - start your own (see Forum Rules at the top of the screen).

    Hope this helps.

    Pete

  12. #12
    Registered User
    Join Date
    07-16-2014
    Location
    saudi arabia
    MS-Off Ver
    2013
    Posts
    2

    Re: Date format of just "st" "nd" "rd" and "th"

    the simplest that i found is:
    =text(today();"dd")&"th"&text(today();"mmm yyyy")

  13. #13
    Registered User
    Join Date
    07-16-2014
    Location
    saudi arabia
    MS-Off Ver
    2013
    Posts
    2

    Re: Date format of just "st" "nd" "rd" and "th"

    the simplest that i found is:
    =text(today();"dd")&"th"&text(today();"mmm yyyy")

    you can change the "red color value" of your choice

  14. #14
    Registered User
    Join Date
    12-08-2008
    Location
    NYC
    MS-Off Ver
    2003 & XP
    Posts
    43

    Re: Date format of just "st" "nd" "rd" and "th"

    is there a way to use custom format to get the "thstndrdth"? I need to be able to calculate of the result but would prefer if the "rd" to auto fill based on a changing result. IE "When is the company profitable?" Answer:"3rd Quarter from IPO" where the cell contents are only a 3 so i can use array sumifs and the like off of that cell.

    Which is why i prefer "Custom Cell Format" for those types of issues

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Date format of just "st" "nd" "rd" and "th"

    coinbank Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Date format of just "st" "nd" "rd" and "th"

    @ coinbank...

    I can answer your question BUT you'll have to start your own thread as per forum rules.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1