Prev: VLookUp Error
Next: how do I turn the screen off during macro execution
From: Keyrookie on 4 Mar 2010 11:49

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
From: Gord Dibben on 4 Mar 2010 15:22
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
<Keyrookie.5d5c3db(a)excelbanter.com> 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

From: Squeaky on 4 Mar 2010 15:54
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
> .
>
From: Keyrookie on 5 Mar 2010 15:15

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
> <Keyrookie.5d5c3db(a)excelbanter.com> 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
From: Roger Govier on 6 Mar 2010 04:47
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
--
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
>> <Keyrookie.5d5c3db(a)excelbanter.com> 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-
>
>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: VLookUp Error
Next: how do I turn the screen off during macro execution