+ Reply to Thread
Results 1 to 8 of 8

Long Date with 'st', 'nd', 'rd', 'th' in Superscript

  1. #1
    Registered User
    Join Date
    03-12-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    14

    Long Date with 'st', 'nd', 'rd', 'th' in Superscript

    Hi,

    Is it possible to set the date format in a cell to 'Monday 18th March 2013'? If so I would then like to be able to enter the date on a Monday and have it automatically inserted on separate sheet for Tuesday to Friday?

    Many thanks

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Long Date with 'st', 'nd', 'rd', 'th' in Superscript

    You cannot apply special formatting to PARTS of a text string in a cell where that string is being displayed as a result of a formula. Only cells where you manually enter text strings can you apply partial-string formatting.


    In short, if there's a formula, only whole-cell formatting can be used.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    03-12-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Long Date with 'st', 'nd', 'rd', 'th' in Superscript

    Many thanks, that's a shame :-(

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

    Re: Long Date with 'st', 'nd', 'rd', 'th' in Superscript

    In Excel 2007 and later you can use conditional formatting to display the ordinal date but I don't think you can format for super/subscript.

    The date would be displayed as Monday 18th March 2013.

    Is that acceptable?


    EDIT: I've been meaning to post a thread in the Tips/Tutorial forum on this subject!
    Last edited by Tony Valko; 03-13-2013 at 05:19 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    03-12-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Long Date with 'st', 'nd', 'rd', 'th' in Superscript

    It would be better than nothing. I was just wondering if the format for the whole cell has to be the same would it be possible the have the date stretch across three cells the first with 'Monday 18' right justified the second with 'th' left justified and the third with March 2013 left justified? Bit of a palaver I know but it is on a sheet that the learning disabled pupils will see everyday and I really want them to see the example of how they should write the date properly?

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

    Re: Long Date with 'st', 'nd', 'rd', 'th' in Superscript

    See attached file. Put a date into the yellow cell (or =TODAY()) to see how it changes.

    Hope this helps.

    Pete
    Attached Files Attached Files

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

    Re: Long Date with 'st', 'nd', 'rd', 'th' in Superscript

    Here's a shorter ordinals formula:

    =MID("stndrdthstndrdthst",MATCH(DAY(A4),{1,2,3,4,21,22,23,24,31})*2-1,2)

    Even shorter...

    Create these defined name...

    Name: Ordinals
    Refers to: ="stndrdthstndrdthst"

    Name: Days
    refers to: ={1,2,3,4,21,22,23,24,31}

    Then:

    =MID(Ordinals,MATCH(DAY(A4),Days)*2-1,2)

    These only work for dates with day numbers from 1 to 31.
    Last edited by Tony Valko; 03-14-2013 at 04:37 PM.

  8. #8
    Registered User
    Join Date
    08-02-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    1

    Cool Re: Long Date with 'st', 'nd', 'rd', 'th' in Superscript

    You can use the unicode characters in superscript
    ᵃᵇᶜᵈᵉᶠᵍʰⁱʲᵏˡᵐⁿᵒᵖʳˢᵗᵘᵛʷˣʸᶻ



    =MID("ˢᵗⁿᵈʳᵈᵗʰˢᵗⁿᵈʳᵈᵗʰˢᵗ",MATCH(DAY(A4),{1,2,3,4,21,22,23,24,31})*2-1,2)

    And if you use math to extract the day it's even shorter
    =MID("ᵗʰˢᵗⁿᵈʳᵈᵗʰ",MIN(MOD(MOD(DAY(A4),30),20),4)*2+1,2)

    Sorry for reviving the thread, but I found it in my googling

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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