text() date format - superscript "12th" etc

[dmj120]

I don't think this is an available option, but after updating from 2010 to 2019, I'm wondering if there's a new date format for the below examples:
Apr 20th
Mar 2nd
Jan 1st
Jan 3rd

I'm thinking of redoing a a cell ="blah blah"&.......text(today(),"mmm d")&" blah blah" ----- where the date is Apr 22nd. It's currently "mmm d) .......[Apr 28]....which works fine, just looking for a little extra pzazz, especially if the "rd, nd,st, etc" could be superscript.

 

[Fluff]

How about

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

 

[dmj120]

How about

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

AWESOME!!! Thanks.

Why does this work if the number isn't listed in the array? ie 15th. I tried days not listed (9,11,15,30...), which all work perfectly; I'm just wondering why. Is it because lookup() looks to the next number, the the array "st,"nd" etc., and use the preceeding - similar to vlookup's relative function in a numbered list?

 

[Rick Rothstein]

I note that your thread title seems to indicate you want the ordinal suffix (st, nd, rd, th) to be superscripted. If that is your desire, then you will need to use a VBA macro as formulas have parts of their displayed text formatted differently than the rest of the text in the cell. Here is a macro that lets you set the date and the text to display before and after the date and which will output the full text with the date ordinal superscripted. Note that in VBA, the Date function is equivalent to Excel's TODAY() function, but you can assign any valid date to the DisplayedDate variable and the code will work correctly with it. You can, of course, set the TextBeforeDate variable to any text that does not include a leading space and the TextAfterDate variable to any text you want that does not include a trailing space. Also, as written, the code places the date text into the active cell (this can be changed if needed).

Sub SuperscriptDateOrdinal()
  Dim TextBeforeDate As String, TheDate As String, TextAfterDate As String, DisplayedDate As Date

  DisplayedDate = Date
  TextBeforeDate = "The date is "
  TextAfterDate = " which is a " & Format(DisplayedDate, "dddd.")

  TheDate = Format(DisplayedDate, "mmm ") & Evaluate(Replace("@&MID(""thstndrdth"",MIN(9,2*RIGHT(@)*(MOD(@-11,100)>2)+1),2)", "@", Day(DisplayedDate)))
  ActiveCell = Trim(TextBeforeDate & TheDate & TextAfterDate)
  ActiveCell.Characters(Len(ActiveCell.Text) - Len(TextAfterDate) - 1, 2).Font.Superscript = True
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (SuperscriptDateOrdinal) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[dmj120]

I note that your thread title seems to indicate you want the ordinal suffix (st, nd, rd, th) to be superscripted. If that is your desire, then you will need to use a VBA macro as formulas have parts of their displayed text formatted differently than the rest of the text in the cell. Here is a macro that lets you set the date and the text to display before and after the date and which will output the full text with the date ordinal superscripted. Note that in VBA, the Date function is equivalent to Excel's TODAY() function, but you can assign any valid date to the DisplayedDate variable and the code will work correctly with it. You can, of course, set the TextBeforeDate variable to any text that does not include a leading space and the TextAfterDate variable to any text you want that does not include a trailing space. Also, as written, the code places the date text into the active cell (this can be changed if needed).

Thanks Rick! Yes, that would be the end goal.

Here's a basic (kinda know the answer), but best to confirm. I will be starting a MS Access project. It has to be at least 20 years since I've used access, so I found a couple cheap udemy courses - one of which is VBA.

If I go through and learn "Access VBA" will that transfer to Excel (or at least 75+%)? I'm sure there's caveats, just wondering if it'll help with Excel VBA.

 

[Rick Rothstein]

I know nothing about Access (never used it) so I cannot answer your question specifically. However, the Visual Basic (VB) part of VBA (Visual Basic for Applications) will be the same BUT each program extends VB with its own underlying object model (these maybe more than 50% of each program's VBA) and I am pretty sure those differ greatly from each other. So my guess is that learning Access VBA would be of minimal use to you in Excel VBA.

 

[Fluff]

Is it because lookup() looks to the next number, the the array "st,"nd" etc., and use the preceeding - similar to vlookup's relative function in a numbered list?

The lookup will find the nearest match that is less than or equal to the number you lookup.

 

[FormR]

especially if the "rd, nd,st, etc" could be superscript.

You could replace the rd, nd and st characters in fluffs suggestion with the equivalent Unicode superscript characters. For example.

=TEXT(TODAY(),"mmm d")&LOOKUP(DAY(TODAY()),{1,2,3,4,21,22,23,24,31;"ˢᵗ","ⁿᵈ","ʳᵈ","ᵗʰ","ˢᵗ","ⁿᵈ","ʳᵈ","ᵗʰ","ˢᵗ"})

 

[dmj120]

You could replace the rd, nd and st characters in fluffs suggestion with the equivalent Unicode superscript characters. For example.

=TEXT(TODAY(),"mmm d")&LOOKUP(DAY(TODAY()),{1,2,3,4,21,22,23,24,31;"ˢᵗ","ⁿᵈ","ʳᵈ","ᵗʰ","ˢᵗ","ⁿᵈ","ʳᵈ","ᵗʰ","ˢᵗ"})

How did you do the unicode superscript?

 

[FormR]

How did you do the unicode superscript?

Hi, I searched for "unicode superscript characters" on the internet and copied them from the first webpage that showed up - I think it was wikipedia.