Microsoft Office Tutorials and References
In Depth Information
Additional formulas use the SUM function to calculate the number of days per month (column J)
and the number of each weekday in the year (row 15).
The workbook shown in Figure 6-4, day of the week count.xlsx , is available on
the companion CD-ROM.
Expressing a date as an ordinal number
You may want to express the day portion of a date as an ordinal number. For example, you can
display 4/16/2010 as April 16th, 2010. The following formula expresses the date in cell A1 as an
=TEXT(A1,”mmmm “)&DAY(A1)&IF(INT(MOD(DAY(A1),100)/10)=1, “th”,IF(MOD(DAY(A1),10)=1,
“st”,IF(MOD(DAY(A1),10)=2,”nd”,IF(MOD(DAY(A1),10)=3, “rd”,”th”))))&TEXT(A1,”, yyyy”)
The result of this formula is text, not an actual date.
The following formula shows a variation that expresses the date in cell A1 in day-month-year
format. For example, 4/16/2010 would appear as 16th April, 2010. Again, the result of this formula
represents text, not an actual date.
IF(MOD(DAY(A1),10)=3, “rd”,”th”))))& “ “ &TEXT(A1,”mmmm, yyyy”)
The companion CD-ROM contains the workbook ordinal dates.xlsx that
demonstrates the formulas for expressing dates as ordinal numbers.
Calculating dates of holidays
Determining the date for a particular holiday can be tricky. Some, such as New Year’s Day and
U.S. Independence Day, are no-brainers because they always occur on the same date. For these
kinds of holidays, you can simply use the DATE function, which I covered earlier in this chapter.
To enter New Year’s Day (which always falls on January 1) for a specific year in cell A1, you can
enter this function: