Date abbreviations / extensions

Is there a way to have dates show with extensions like, "st", "nd",
"rd", "th"
when using the NOW() formula? In other words, when the date changes
from the 1st to the 2nd will Excel display the extensions? Or, is
there a way automatically to spell out the dates, (First, Third,
Sixteenth, etc.) using the NOW() or some other formula?

Thanks,
K




--
Keyrookie

One method using a UDF

Function OrdinalNumber(ByVal Num As Long) As String
Dim N As Long
Const cSfx = "stndrdthththththth"
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

In a cell enter

=TEXT(NOW(),"mmmm ")&ordinalnumber(DAY(NOW())) & TEXT(NOW(),", yyyy")

Returns March 4th, 2010


Gord Dibben MS Excel MVP


On Thu, 4 Mar 2010 16:49:49 +0000, Keyrookie
wrote:


Is there a way to have dates show with extensions like, "st", "nd",
"rd", "th"
when using the NOW() formula? In other words, when the date changes
from the 1st to the 2nd will Excel display the extensions? Or, is
there a way automatically to spell out the dates, (First, Third,
Sixteenth, etc.) using the NOW() or some other formula?

Thanks,
K

Hi Keyrookie,

One way: Place 1 through 31 in a column (I used G1-g31). In column H put
1st, 2nd, 3rd, etc, or spell the words out if you wish.

In another cell put:

=VLOOKUP(DAY(NOW()),(F1:G31),2,FALSE)

You can hide G and H columns if you wish.

Squeaky


"Keyrookie" wrote:


Is there a way to have dates show with extensions like, "st", "nd",
"rd", "th"
when using the NOW() formula? In other words, when the date changes
from the 1st to the 2nd will Excel display the extensions? Or, is
there a way automatically to spell out the dates, (First, Third,
Sixteenth, etc.) using the NOW() or some other formula?

Thanks,
K




--
Keyrookie
.

Gord,

I tried your function but fell short. I'm assuming UDF stands for User
Defined Function? I copied your code and pasted it into the worksheet
code and then copied the formula into a cell. Nothing happened. What
did I do wrong?

K

Gord Dibben;933874 Wrote:
One method using a UDF

Function OrdinalNumber(ByVal Num As Long) As String
Dim N As Long
Const cSfx = "stndrdthththththth"
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

In a cell enter

=TEXT(NOW(),"mmmm ")&ordinalnumber(DAY(NOW())) & TEXT(NOW(),", yyyy")

Returns March 4th, 2010


Gord Dibben MS Excel MVP


On Thu, 4 Mar 2010 16:49:49 +0000, Keyrookie
wrote:
-

Is there a way to have dates show with extensions like, "st", "nd",
"rd", "th"
when using the NOW() formula? In other words, when the date changes
from the 1st to the 2nd will Excel display the extensions? Or, is
there a way automatically to spell out the dates, (First, Third,
Sixteenth, etc.) using the NOW() or some other formula?

Thanks,
K-




--
Keyrookie

HI

you need to copy Gord's function code to a standard module, not to the
Worksheet itself.

Alt+F11 to invoke the VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel
--
Regards
Roger Govier

Keyrookie wrote:
Gord,

I tried your function but fell short. I'm assuming UDF stands for User
Defined Function? I copied your code and pasted it into the worksheet
code and then copied the formula into a cell. Nothing happened. What
did I do wrong?

K

Gord Dibben;933874 Wrote:
One method using a UDF

Function OrdinalNumber(ByVal Num As Long) As String
Dim N As Long
Const cSfx = "stndrdthththththth"
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

In a cell enter

=TEXT(NOW(),"mmmm ")&ordinalnumber(DAY(NOW())) & TEXT(NOW(),", yyyy")

Returns March 4th, 2010


Gord Dibben MS Excel MVP


On Thu, 4 Mar 2010 16:49:49 +0000, Keyrookie
wrote:
-
Is there a way to have dates show with extensions like, "st", "nd",
"rd", "th"
when using the NOW() formula? In other words, when the date changes
from the 1st to the 2nd will Excel display the extensions? Or, is
there a way automatically to spell out the dates, (First, Third,
Sixteenth, etc.) using the NOW() or some other formula?

Thanks,
K-

Thank for the assist Roger.

I usually post the instructions but in a hurry or simply forgot.

I would guess the latterg


Gord

On Sat, 06 Mar 2010 09:47:19 +0000, Roger Govier
wrote:

HI

you need to copy Gord's function code to a standard module, not to the
Worksheet itself.

Alt+F11 to invoke the VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel

Gord Dibben;934452 Wrote:
Thank for the assist Roger.

I usually post the instructions but in a hurry or simply forgot.

I would guess the latterg


Gord

On Sat, 06 Mar 2010 09:47:19 +0000, Roger Govier
wrote:
-
HI

you need to copy Gord's function code to a standard module, not to the

Worksheet itself.

Alt+F11 to invoke the VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel-

Thanks Gord & Roger,

I copied the function into a module and it worked fine. One last
puzzle piece...

In cell O154 I have this formula:
=NOW()
In cell Q154 this formula:
=DATE(YEAR(O154),MONTH(O154),1+7*2)-WEEKDAY(DATE(YEAR(O154),MONTH(O154),8-1))

This gives me the 2nd Sunday of every month. I'm wanting this cell,
Q154, to show the extensions. How do I combine the formula I'm already
using and the new one that will reflect the extensions? For this month
cell Q154 would show 14th. For next month it would show 11th.

Thanks again,

K




--
Keyrookie