date format in excel

B

brian

Can anyone tell me if you can/how to format the date in
excel so that it will read eg 1st, 2nd, 3rd, 4th April
rather than just eg 1,2,3,4 April

(The standard fomats don't allow for "th" "st" etc and nor
can i get the custom format to do so)

Thanks in advance
 
B

Bufo Calvin

-----Original Message-----
Can anyone tell me if you can/how to format the date in
excel so that it will read eg 1st, 2nd, 3rd, 4th April
rather than just eg 1,2,3,4 April

(The standard fomats don't allow for "th" "st" etc and nor
can i get the custom format to do so)

Thanks in advance
.
Click to expand...

This should work, although watch the word-
wrapping...there is no return after "st .

I have it set up to return "4th of April", rather than
just "4th". If you want just "4th" and you need help
tweaking, please post again.

Public Function OrdinalDate(PlainDate)
'Created by Bufo Calvin
Select Case DatePart("d", PlainDate)
Case 1, 21, 31
OrdinalDate = DatePart("d", PlainDate) & "st
of " & MonthName(DatePart("m", PlainDate))
Case 2, 22
OrdinalDate = DatePart("d", PlainDate) & "nd
of " & MonthName(DatePart("m", PlainDate))
Case 3
OrdinalDate = DatePart("d", PlainDate) & "rd
of " & MonthName(DatePart("m", PlainDate))
Case Else
OrdinalDate = DatePart("d", PlainDate) & "th
of " & MonthName(DatePart("m", PlainDate))
End Select
End Function

Public Function MonthName(DatePartMonth As Integer) As
String
' Created by Bufo Calvin
Select Case DatePartMonth
Case 1
MonthName = "January"
Case 2
MonthName = "February"
Case 3
MonthName = "March"
Case 4
MonthName = "April"
Case 5
MonthName = "May"
Case 6
MonthName = "June"
Case 7
MonthName = "July"
Case 8
MonthName = "August"
Case 9
MonthName = "September"
Case 10
MonthName = "October"
Case 11
MonthName = "November"
Case 12
MonthName = "December"
Case Else
MonthName = "Invalid Date"
End Select
End Function
 
B

Bufo Calvin

Oops! I left out accounting for the 23rd of a month.

Oh, and Frank is right...this is not a format, it's
a "helper cell" solution.

The corrected version would read:

Public Function OrdinalDate(PlainDate)
'Created by Bufo Calvin
Select Case DatePart("d", PlainDate)
Case 1, 21, 31
OrdinalDate = DatePart("d", PlainDate) & "st
of " & MonthName(DatePart("m", PlainDate))
Case 2, 22
OrdinalDate = DatePart("d", PlainDate) & "nd
of " & MonthName(DatePart("m", PlainDate))
Case 3, 23
OrdinalDate = DatePart("d", PlainDate) & "rd
of " & MonthName(DatePart("m", PlainDate))
Case Else
OrdinalDate = DatePart("d", PlainDate) & "th
of " & MonthName(DatePart("m", PlainDate))
End Select
End Function

Public Function MonthName(DatePartMonth As Integer) As
String
' Created by Bufo Calvin
Select Case DatePartMonth
Case 1
MonthName = "January"
Case 2
MonthName = "February"
Case 3
MonthName = "March"
Case 4
MonthName = "April"
Case 5
MonthName = "May"
Case 6
MonthName = "June"
Case 7
MonthName = "July"
Case 8
MonthName = "August"
Case 9
MonthName = "September"
Case 10
MonthName = "October"
Case 11
MonthName = "November"
Case 12
MonthName = "December"
Case Else
MonthName = "Invalid Date"
End Select
End Function
 
Top