Re: Date Format: "*st" "*nd" "*rd&q
It is not clear whether you want to display your date this way in another cell, or in the cell the date exists. If you want it displayed in the same cell, it is not clear whether the date is manually entered, or rather is returned from a formula in that cell. You did not give any cell address or range of cells where you want this to take place. There is a lot of information missing from your post.
Two possibilities playing the most likely odds - -
Formula approach:
Your date is in cell A1 and you want to display it in another cell with that special format:
=TEXT(A1,"ddd d"""&VLOOKUP(DAY(A1),{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";4,"th";31,"st"},2)&""" mmm yy")
VBA approach:
You want the format to take place in that same cell, not involving other cells:
Again, assuming A1, right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Not IsDate(Target.Value) Then Exit Sub
Dim DateSuffix As String
Select Case Day(Target.Value)
Case 1, 21, 31
DateSuffix = "st"
Case 2, 22
DateSuffix = "nd"
Case 3, 23
DateSuffix = "rd"
Case 4 To 30
DateSuffix = "th"
End Select
Application.EnableEvents = False
Target.Value = Format(Target.Value, "ddd d") & DateSuffix & Format(Target.Value, " mmm yy")
Application.EnableEvents = True
End Sub
Be advised, in either case, you may lose functionality with those cells for calculating any date math operations you were planning on, which is something else you did not mention, but which you should be aware of because the cell values are essentially text and no longer dates.
