Microsoft Office Tutorials and References

In Depth Information

**Date-Related Functions**

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

ordinal date:

=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.

=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,”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:

=DATE(A1,1,1)