display dates with ordinal numbers in excel

[Guest]

I would like to display a date in the format 7th September 2006 in a cell. I
want to do this as a cell format so that I can refer to the cell and Excel
will recognise it as a date, not text.

Is there any way to show ordinal numbers in this way using cell formatting?

 

[Guest]

Use a Custom Format. Change it too dd 'mmmm yyyy' which will display
'07 September 2006'. Use 'd mmmm yyyy' for '7 September 2006'. Sorry, I
don't know how to put the 'th', 'st' or 'nd' in.

 

[Roger Govier]

Hi Tim

Not with formatting I'm afraid.
The nearest you can get would be Format>Cells>Number>Custom> d mmmm yyyy
to give you 7 September 2006.

Chip Pearson has good information on his site regarding Ordinals, both
by formula and VBA
http://www.cpearson.com/excel/ordinal.htm

You could perhaps use these methods for your display, and have the date
in ordinary format in another hidden column for use in your further data
manipulation.

 

[Ron Rosenfeld]

I would like to display a date in the format 7th September 2006 in a cell. I
want to do this as a cell format so that I can refer to the cell and Excel
will recognise it as a date, not text.

Is there any way to show ordinal numbers in this way using cell formatting?

You would have to set the cell format using an event-triggered VBA macro.

The macro below should work reliably for dates using the 1900 date system that
are on or after 1 Mar 1900. If you are using the 1904 date system, or dates
before 1 Mar 1900, it will require some additional lines due in part to the
"built-in" bug in Excel which allows 29 Feb 1900 as a valid date. (I'm told
that was done for compatibility with Lotus).

To enter this macro, right click the sheet tab and select View Code. Paste the
macro below into the window that opens.

You will have to "Set" AOI to the range that you wish to have formatted in this
manner.

Let me know if this does what you want:

=============================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Dim Suffix As String
Dim dt As Date

Set AOI = [A2:A10] 'set to range to be formatted

For Each c In AOI

If IsDate(c) Then
dt = c.Value

Select Case Day(dt)
Case Is = 1, 21, 31
Suffix = "st"
Case Is = 2, 22
Suffix = "nd"
Case Is = 3, 23
Suffix = "rd"
Case Else
Suffix = "th"
End Select
c.NumberFormat = "d""" & Suffix & """ mmm yyyy"
End If
Next c

End Sub
=======================================


--ron

 

[Bob Phillips]

See http://xldynamic.com/source/xld.RANK.html, might give you some help

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I would like to display a date in the format 7th September 2006 in a cell. I
want to do this as a cell format so that I can refer to the cell and Excel
will recognise it as a date, not text.

Is there any way to show ordinal numbers in this way using cell

formatting?

 

[Guest]

Thank you very much, that did the trick!

I would like to display a date in the format 7th September 2006 in a cell. I
want to do this as a cell format so that I can refer to the cell and Excel
will recognise it as a date, not text.

Is there any way to show ordinal numbers in this way using cell formatting?

You would have to set the cell format using an event-triggered VBA macro.

The macro below should work reliably for dates using the 1900 date system that
are on or after 1 Mar 1900. If you are using the 1904 date system, or dates
before 1 Mar 1900, it will require some additional lines due in part to the
"built-in" bug in Excel which allows 29 Feb 1900 as a valid date. (I'm told
that was done for compatibility with Lotus).

To enter this macro, right click the sheet tab and select View Code. Paste the
macro below into the window that opens.

You will have to "Set" AOI to the range that you wish to have formatted in this
manner.

Let me know if this does what you want:

=============================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Dim Suffix As String
Dim dt As Date

Set AOI = [A2:A10] 'set to range to be formatted

For Each c In AOI

If IsDate(c) Then
dt = c.Value

Select Case Day(dt)
Case Is = 1, 21, 31
Suffix = "st"
Case Is = 2, 22
Suffix = "nd"
Case Is = 3, 23
Suffix = "rd"
Case Else
Suffix = "th"
End Select
c.NumberFormat = "d""" & Suffix & """ mmm yyyy"
End If
Next c

End Sub
=======================================


--ron

 

[Ron Rosenfeld]

Thank you very much, that did the trick!

Glad to help. Thanks for the feedback.
--ron