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.