Customized Format

[iamnu]

At a minimum, I'd like to enter:
1st
2nd
3rd
4th
etc.

and have the cells formatted as:
1st of the month
2nd of the month
3rd of the month
4th of the month
etc.

Ideally, I'd like the cells formatted the same after entering only the
number.

Does someone have a suggestion?

 

[Bernie Deitrick]

To just enter numbers, you could use a formula in another cell - enter a
number in cell A2, and use

=A2 & IF(A2=31,"st",CHOOSE(MIN(MOD(A2,20),4)+1,"th","st","nd","rd","th"))& "
of the month"

Or enter 1st, 2nd etc, and use the custom format

@" of the month"

Another way would be to use the worksheet's change event and VBA code.

HTH,
Bernie
MS Excel MVP

 

[iamnu]

To just enter numbers, you could use a formula in another cell - enter a
number in cell A2, and use

=A2 & IF(A2=31,"st",CHOOSE(MIN(MOD(A2,20),4)+1,"th","st","nd","rd","th"))& "
of the month"

Or enter 1st, 2nd etc, and use the custom format

@" of the month"

Another way would be to use the worksheet's change event and VBA code.

HTH,
Bernie
MS Excel MVP

Thank you, sir!
I like the worksheet's change event and VBA code.

 

[Bernie Deitrick]

Copy the code below, right-click the sheet tab, select "View Code" and paste
the code into the window that appears. The code will change any single cell
entry in column A into the phrase desired. To change the column of date
entry, change the <> 1 to the column number desired, or use other control
limits.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myDay As Integer

If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
myDay = CInt(Target.Value)
If myDay > 31 Or myDay < 1 Then
Target.Value = "Invalid entry"
Exit Sub
End If
Target.Value = myDay & "th of the month"
If myDay = 31 Then Target.Value = "31st of the month"
If myDay Mod 20 = 1 Then Target.Value = myDay & "st of the month"
If myDay Mod 20 = 2 Then Target.Value = myDay & "nd of the month"
If myDay Mod 20 = 3 Then Target.Value = myDay & "rd of the month"
Application.EnableEvents = True

End Sub.

To just enter numbers, you could use a formula in another cell - enter a
number in cell A2, and use

=A2 & IF(A2=31,"st",CHOOSE(MIN(MOD(A2,20),4)+1,"th","st","nd","rd","th"))&
"
of the month"

Or enter 1st, 2nd etc, and use the custom format

@" of the month"

Another way would be to use the worksheet's change event and VBA code.

HTH,
Bernie
MS Excel MVP

Thank you, sir!
I like the worksheet's change event and VBA code.

 

[Bernie Deitrick]

Ooops. I turned off events too soon. Use this version.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myDay As Integer

If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
myDay = CInt(Target.Value)
If myDay > 31 Or myDay < 1 Then
Target.Value = "Invalid entry"
Exit Sub
End If
Application.EnableEvents = False
Target.Value = myDay & "th of the month"
If myDay = 31 Then Target.Value = "31st of the month"
If myDay Mod 20 = 1 Then Target.Value = myDay & "st of the month"
If myDay Mod 20 = 2 Then Target.Value = myDay & "nd of the month"
If myDay Mod 20 = 3 Then Target.Value = myDay & "rd of the month"
Application.EnableEvents = True

End Sub

 

[Gord Dibben]

That event code could be this which formats as you enter the numbers 1 to 31

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Suffix As String
Dim c As Range
Dim num As Variant
Dim AOI As Range

Set AOI = Range("A:A") 'area to custom format

If Not Intersect(Target, AOI) Is Nothing Then
For Each c In Intersect(Target, AOI)
num = c.Value

If IsNumeric(num) And c.Value < 32 Then
If num = Int(num) Then
Select Case Abs(num) Mod 10
Case Is = 1
Suffix = "st of the month"
Case Is = 2
Suffix = "nd of the month"
Case Is = 3
Suffix = "rd of the month"
Case Else
Suffix = "th of the month"
End Select
Select Case num Mod 100
Case 11 To 19
Suffix = "th of the month"
End Select
c.NumberFormat = "#,##0" & """" & Suffix & """"
End If
Else
c.NumberFormat = "General"
End If

Next c
End If
End Sub


Gord Dibben MS Excel MVP

 

[herochenna]

Visit www.ExcelGoodies.Com

 

[herochenna]

Visit www.ExcelGoodies.Com