Date Function

[davids]

Is there a function I could use that would change a column of 1,2,3,4 etc
into a column of 1st,2nd,3rd,4th etc?

 

[davesexcel]

http://tinyurl.com/z6qpd
Here's a post I hope will help yo

 

[daddylonglegs]

Perhaps you only need up to 31 but this formula will convert an intege
in A1 to an ordinal number

=A1&IF(OR(MOD(A1,100)={11,12,13}),"th",CHOOSE(MIN(MOD(A1,10)+1,5),"th","st","nd","rd","th")

 

[Gord Dibben]

davids

Copy this UDF to a general module in your workbook.

Function OrdinalNumber(ByVal Num As Long) As String
'You can call this directly from a worksheet cell, as follows:
'=OrdinalNumber(A1)
Dim n As Long
Const cSfx = "stndrdthththththth" ' 2 char suffixes
n = Num Mod 100
If ((Abs(n) >= 10) And (Abs(n) <= 19)) _
Or ((Abs(n) Mod 10) = 0) Then
OrdinalNumber = Format(Num) & "th"
Else
OrdinalNumber = Format(Num) & Mid(cSfx, _
((Abs(n) Mod 10) * 2) - 1, 2)
End If
End Function


Gord Dibben MS Excel MVP

 

[Theo]

Is there a function I could use that would change a column of 1,2,3,4
etc into a column of 1st,2nd,3rd,4th etc?

Try this one:

A1: 1st
B1: 2nd


- select A1+B1.

- Klick south-east corner and drag to C3, D3, E3....

Should result in:

row A1, B1, C1, etc...
1st 2nd, 3rd,etc..

 

[davesexcel]

Is there a function I could use that would change a column of 1,2,3,4
etc
into a column of 1st,2nd,3rd,4th etc?

It helps if you acknowlege the replies