Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 72 Add Date Extensions

Excel's date formats consist
of many different formats that you can use to display a date.
However, one format that has always been lacking in Exceland
still does not existis the ability to display a date as 15th
October 2003. You can make Excel do this if you need it.

The use of the
"th" after the digits 15 is the one
format Excel does not have. To make matters even worse, as far as we
are aware, it is not possible to set a custom format to display the
date in this manner. Although most people simply accept that this is
not possible, here is a way you can accomplish it.

On a clean worksheet, starting in cell A1, make the following
entries: A1=st, A2=nd,
A3=rd, A4:A20=th,
A21=st, A22=nd,
A23=rd, A24:A30=th,
A31=st.

Name this range
Extensions, then select Insert Name
Define, and in the Names in Workbook: box, type
MyToday. In the Refers To: box, enter the
following formula:

=TEXT(TODAY( ),"dddd d")&INDEX(Extensions,DAY(TODAY( )),1) & TEXT(TODAY( )," 
mmmm yyyy")

Click Add, then OK.

Now, in any cell, simply enter =MyToday, and
the current date always will display with the format Thursday 16th
October 2003, or whatever date it happens to be.

If you would
rather not use cells on a worksheet to store date
extensionsthat is, th, st, rd, ndyou can use the
CHOOSE function to house them. To do this, select
Insert Name Define, and in the Names in
Workbook: box, enter the word Day.

In the Refers To: box, enter this formula:

=DAY(TODAY( ))

Click Add. Go back to the Names in Workbook: box and enter the word
MyToday.

In the Refers To: box, enter the following formula, and click Add:

=TEXT(TODAY( ),"dddd 
d")&IF(Day=31,"st",IF(Day=30,"th",CHOOSE(Day,"st","nd","rd","th","th","th","
th","th","th","th","th","th","th","th","th","th","th","th","th","th","st","n
d","rd","th","th","th","th","th","th")))& TEXT(TODAY( )," mmmm yyyy").

After you click OK, you can enter =MyToday
into any cell in which you want to display this format of
date.

The date returned by the use of either of these functions will not be a true numeric date as far as Excel is concerned; it will simply be a text entry, meaning you will not be able to reference the cell housing it in any formula that expects numeric data.