Date abbreviations / extensions

[Keyrookie]

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 change
from the 1st to the 2nd will Excel display the extensions? Or, i
there a way automatically to spell out the dates, (First, Third
Sixteenth, etc.) using the NOW() or some other formula?

Thanks,

 

[Gord Dibben]

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

 

[Squeaky]

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]

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

 

[Roger Govier]

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
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

 

[Gord Dibben]

Thank for the assist Roger.

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

I would guess the latter<g>


Gord

 

[Keyrookie]

Thank for the assist Roger.

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

I would guess the latter<g>


Gord

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

Thanks Gord & Roger,

I copied the function into a module and it worked fine. One las
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 alread
using and the new one that will reflect the extensions? For this mont
cell Q154 would show 14th. For next month it would show 11th.

Thanks again,