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. :-)
