MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Date + Superscripts


Posted by Jack on July 24, 2001 2:45 PM

OK i want a nice date with superscript to th, nd, st and rd on the correct number date????

Dont mind formula or VBA, tricky but not imposs im sure

Any ideas?


Posted by Mark W. on July 24, 2001 3:34 PM

Must the ordinal number symbol be formatted as a
superscript?

Posted by IML on July 24, 2001 3:54 PM

Re: Date + No Superscripts

For everything but the superscript, you could try this. Maybe not the best way, but it was fun trying.
I assume you have a date in A1. You also need to include the following table.
{1,"st";2,"nd";3,"rd";21,"st";22,"nd";23,"rd";31,"st"}
I have these in I1:J7. Now the lovely formula:
=TEXT(A1,"MMMM")&" "&TEXT(A1,"d")&IF(COUNTIF(I1:I7,TEXT(A1,"d")*1)=0,"th",VLOOKUP(TEXT(A1,"d")*1,I1:J7,2,FALSE))&", "&(TEXT(A1,"yyyy"))

Hopefully someone can present a more sane solution.

good luck

Posted by Mark W. on July 24, 2001 4:08 PM

My Solution... but no help on subscripting...

=SUBSTITUTE(TEXT(A1,"mmmm d, yyyy"),",",VLOOKUP(RIGHT(DAY(A1)),{"0","th,";"1","st,";"2","nd,";"3","rd,";"4","th,"},2))

Posted by IML on July 24, 2001 4:14 PM

Re: My Solution... but no help on subscripting...

I like yours a lot better in format, just need to the address the 11th, 12th, and 13th, though. Pesky exceptions. aargh.

Posted by Mark W. on July 24, 2001 4:16 PM

Re: My Solution... but no help on subscripting...

Darn it! I missed those... Hmmm, back to the
drawing board.

Posted by Mark W. on July 24, 2001 4:42 PM

Minor repair...

Posted by Aladin Akyurek on July 24, 2001 4:44 PM

Re: My Solution... but no help on subscripting...

A small matter I think:

=SUBSTITUTE(TEXT(A1,"mmmm d, yyyy"),",",VLOOKUP(DAY(A1),{0,"th,";1,"st,";2,"nd,";3,"rd,";4,"th,"},2))

Posted by Ivan F Moala on July 25, 2001 1:39 AM

If you want to do via VBA then select the range
and run this macro;
Note: The dates will be changed to text so.....
No calculations can be done on thes dates !

Sub con_Date()
Dim Newdate As String
Dim day
Dim rg As Range
Dim oCell As Range
Dim DRg As Range

Set DRg = Selection
For Each oCell In DRg
If IsDate(oCell) Then
Newdate = Format(oCell, "dd mmmm yyyy")
day = Val(Left(Newdate, 2))

Select Case day
Case 1, 21, 31 '1st
Newdate = day & "st " & Format(Newdate, "mmmm yyyy")
Case 2, 22 '2nd
Newdate = day & "nd " & Format(Newdate, "mmmm yyyy")
Case 3, 23 '3rd
Newdate = day & "rd " & Format(Newdate, "mmmm yyyy")
Case 4 To 20, 24 To 30 '4th to 20th
Newdate = day & "th " & Format(Newdate, "mmmm yyyy")
End Select

oCell = Newdate
With oCell.Characters(Start:=3, Length:=2).Font
.Superscript = True
End With
End If
Next

End Sub


Ivan

Posted by Ivan F Moala on July 25, 2001 1:43 AM

Woops...small change

Sub con_Date()
Dim Newdate As String
Dim day
Dim rg As Range
Dim oCell As Range
Dim DRg As Range

Set DRg = Selection
For Each oCell In DRg
If IsDate(oCell) Then
Newdate = Format(oCell, "dd mmmm yyyy")
day = (Left(Newdate, 2))

Select Case day
Case 1, 21, 31 '1st
Newdate = day & "st " & Format(Newdate, "mmmm yyyy")
Case 2, 22 '2nd
Newdate = day & "nd " & Format(Newdate, "mmmm yyyy")
Case 3, 23 '3rd
Newdate = day & "rd " & Format(Newdate, "mmmm yyyy")
Case 4 To 20, 24 To 30 '4th to 20th
Newdate = day & "th " & Format(Newdate, "mmmm yyyy")
End Select

oCell = Newdate
With oCell.Characters(Start:=3, Length:=2).Font
.Superscript = True
End With
End If
Next

End Sub

Posted by Mark W. on July 25, 2001 6:43 AM

Except...

VLOOKUP(DAY(A1),{0,"th,";1,"st,";2,"nd,";3,"rd,";4,"th,"},2))

...results in 21th, 22th and 23th.

Posted by Aladin Akyurek on July 25, 2001 7:55 AM

Repeating the original sin...

that's something. But see the post "Minor repair" above. :-)