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 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 > 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 >> 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 ErrorNext: how do I turn the screen off during macro execution