Microsoft Office Tutorials and References

In Depth Information

You may want to express the day portion of a date as an ordinal number. For example, you can display 4/16/

2013 as
April 16th, 2013.
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 ex-

ample, 4/16/2013 would appear as
16th April, 2013.
Again, the result of this formula represents text, not an ac-

tual 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”)

This book's website 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. Independ-

ence 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)

Other holidays are defined in terms of a particular occurrence on a particular weekday in a particular month. For

example, Labor Day in the United States falls on the first Monday in September.

Figure 6-6 shows a workbook with formulas to calculate the date for 11 U.S. holidays. The formulas reference

the year in cell A1. Notice that because New Year's Day, Independence Day, Veterans Day, and Christmas Day

all fall on the same days each year, their dates can be calculated by using the simple DATE function.