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.
Search JabSto ::




Custom Search