Formatting Ordinal Numbers

[inarbeth]

Is there a way to format numbers (1 to 31 for the days of the month or more if possible) 1st, 2nd, 3rd, 4th, 5th etc. I can't get Excel to accept more than three
Custom formats:[=1]##"st";[=2]##"nd";##"th"

Can this be done with VBA?

 

[Dave Patton]

One example 1/1/02 in A4

=TEXT(A4,"mmmm d"&LOOKUP(DAY(A4),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})&", yyyy")

gives January 1st, 2002

Revise for your preferences

 

[NateO]

You can do this in VBA with a worksheet_change event.

Right click on the sheet in question, click on view code and paste the following code in the now opened worksheet module:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Application.IsNumber(Target.Value) And _
Not Target.HasFormula And Target< 32 Then
If Right(Target, 1) = 1 And Target<> 11 Then
Target.Value = Target & "st"
ElseIf Right(Target, 1) = 2 And Target<> 12 Then
Target.Value = Target & "nd"
ElseIf Right(Target, 1) = 3 And Target<> 13 Then
Target.Value = Target & "rd"
Else: Target.Value = Target & "th"
End If
End If
Application.EnableEvents = True
End Sub</pre>

Caution, the numbers are now formatted as text. Hope this helps.

Edit: This works with entering a number, not dates....
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-22 12:26

 

[NateO]

Might as well give a procedure for entering dates as well eh. Here's the code I came up with, again, these conversions will be formatted as text:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsDate(Target) And Not Target.HasFormula Then
If Right(Format(Target, "d"), 1) = 1 And _
Format(Target, "d")<> 11 Then
Target.Value = Format(Target, "d") & "st"
ElseIf Right(Format(Target, "d"), 1) = 2 And _
Format(Target, "d")<> 12 Then
Target.Value = Format(Target, "d") & "nd"
ElseIf Right(Format(Target, "d"), 1) = 3 And _
Format(Target, "d")<> 13 Then
Target.Value = Format(Target, "d") & "rd"
Else: Target.Value = Format(Target, "d") & "th"
End If
End If
Application.EnableEvents = True
End Sub</pre>

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-22 12:46

 

[inarbeth]

On 2002-06-22 12:45, NateO wrote:
Might as well give a procedure for entering dates as well eh. Here's the code I came up with, again, these conversions will be formatted as text:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsDate(Target) And Not Target.HasFormula Then
If Right(Format(Target, "d"), 1) = 1 And _
Format(Target, "d")<> 11 Then
Target.Value = Format(Target, "d") & "st"
ElseIf Right(Format(Target, "d"), 1) = 2 And _
Format(Target, "d")<> 12 Then
Target.Value = Format(Target, "d") & "nd"
ElseIf Right(Format(Target, "d"), 1) = 3 And _
Format(Target, "d")<> 13 Then
Target.Value = Format(Target, "d") & "rd"
Else: Target.Value = Format(Target, "d") & "th"
End If
End If
Application.EnableEvents = True
End Sub</pre>

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-22 12:46

Thanks Nate but this formats the date 23/6/02 as 23rd!

What I am looking for is the date to remain a number so that I can use it in formulas or to work out date difference.

 

[NateO]

Howdy inarbeth. A few thoughts, you can still work with the numbers that the vba cranks out with a straight-forward formula like below:

Book1
	A	B	C	D
1	21st	21
2	1st	1
3	27th	27
4	3rd	3	52
Sheet1

This formula is based of user defined function I wrote, you can use it by placing it in a standard/normal Excel module, the code is as follows:<pre>
Private Function nbrs(n As String)
Dim y As Integer
Application.Volatile
If InStr(n, "s") Then
y = InStr(n, "s")
nbrs = Mid(n, 1, y - 1) + 0
End If
If InStr(n, "n") Then
y = InStr(n, "n")
nbrs = Mid(n, 1, y - 1) + 0
End If
If InStr(n, "r") Then
y = InStr(n, "r")
nbrs = Mid(n, 1, y - 1) + 0
End If
If InStr(n, "h") Then
y = InStr(n, "h")
nbrs = Mid(n, 1, y - 2) + 0
End If
End Function</pre>


The other thing that I can think of is to use a procedure like below which will add the text to the adjacent column (have a blank column), this way you can store the entry as a date and format it to look like a day number. From here you can concatenate, etc...<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsDate(Target) And Not Target.HasFormula Then
If Right(Format(Target, "d"), 1) = 1 And _
Format(Target, "d")<> 11 Then
Target.NumberFormat = "d"
Target(, 2).Value = "st"
ElseIf Right(Format(Target, "d"), 1) = 2 And _
Format(Target, "d")<> 12 Then
Target.NumberFormat = "d"
Target(, 2).Value = "nd"
ElseIf Right(Format(Target, "d"), 1) = 3 And _
Format(Target, "d")<> 13 Then
Target.NumberFormat = "d"
Target(, 2).Value = "rd"
Else: Target.NumberFormat = "d"
Target(, 2).Value = "th"
End If
End If
Application.EnableEvents = True
End Sub</pre>

Here's a look at some things I did with it (you can now use networkdays, etc....)

Book1
	A	B	C	D
1	30	th	30th
2	21	st	21st
Sheet3

Questions or concerns, please post back.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-23 13:34

 

[inarbeth]

Thanks Nate. I am grateful for the time you have taken but I wanted a simple formatting to use on a sheet, especially to format dates. I think it's time for Microsoft to include this formatting as a standard. I can't be the only one who likes to write dates as 23rd June, 31st August etc! As my first post demonstrated a partial solution is obtainable using Custom format. Unfortunately there appears to be a limit on the number of conditions that can be applied using Custom formatting.

 

[NateO]

inarbeth, I hear you, MS isn't always considerate of what I want either!

But you and I may have differing opinions on what is easy. I think that building a fence in 40 degree c weather is hard, hiding columns, etc....is relatively easy.

Your question seems to change with every post. So, a new answer for a new question. Just enter the date, calc. off it normally and use the following to get your desired format.<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsDate(Target) And Not Target.HasFormula Then
If Right(Format(Target, "d"), 1) = 1 And _
Format(Target, "d")<> 11 Then
Target(, 2).Value = Format(Target, "d") & _
"st" & " " & Format(Target, "mmmm")
ElseIf Right(Format(Target, "d"), 1) = 2 And _
Format(Target, "d")<> 12 Then
Target(, 2).Value = Format(Target, "d") & _
"nd" & " " & Format(Target, "mmmm")
ElseIf Right(Format(Target, "d"), 1) = 3 And _
Format(Target, "d")<> 13 Then
Target(, 2).Value = Format(Target, "d") & _
"rd" & " " & Format(Target, "mmmm")
Else: Target(, 2).Value = Format(Target, "d") & _
"th" & " " & Format(Target, "mmmm")
End If
End If
Application.EnableEvents = True
End Sub</pre>

The issue is that your formatting set up can't process if statements, which are needed for something like this. You want a format with text, which the above generates and Excel only wants to compute against numeric data.

Depends how badly you want this...

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-23 14:53

 

[stevebausch]

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q160988

 

[Yogi Anand]

Hi inarbeth:

I am not sure if my proposed solution will work for you -- I have the following macro:

Private Sub CommandButton1_Click()

' created 6/23/02 by Yogi Anand

Range("A1").Select
If [A1]<= 31 Then Selection.NumberFormat = "[<32]##""th"";General"
If [A1] = 1 Then Selection.NumberFormat = "[=1]##""st"";General"
If [A1] = 2 Then Selection.NumberFormat = "[=2]##""nd"";General"
If [A1] = 3 Then Selection.NumberFormat = "[=3]##""rd"";General"
If [A1] = 21 Then Selection.NumberFormat = "[=21]##""st"";General"
If [A1] = 22 Then Selection.NumberFormat = "[=22]##""nd"";General"
If [A1] = 23 Then Selection.NumberFormat = "[=23]##""rd"";General"


End Sub

I created a CommandButton on a worksheet of a workbook and I assigned the above written macro to the command button.
I make my number entry in cell A1 (valid entries are 1 to 31)
Then I click on the CommandButton to see

1 formatted to 1st
2 formatted to 2nd
3 formatted to 3rd
.......

The numeric value of the entry stays intact, it is just formatted as 1st, 2nd, 3rd, ...

Hope This Helps

Regards!


Edit Note: I have not fully tested it -- some tweaking may be necessary -- you may also have to tweak it to adapt it to your needs. I would have gone strictly for CUSTOM formatting only without the benefit of a macro, but as you correctly pointed out, one cannot accommodate all the CUSTOM formats natively.
This message was edited by Yogi Anand on 2002-06-23 17:16