Студопедия
Случайная страница | ТОМ-1 | ТОМ-2 | ТОМ-3
АрхитектураБиологияГеографияДругоеИностранные языки
ИнформатикаИсторияКультураЛитератураМатематика
МедицинаМеханикаОбразованиеОхрана трудаПедагогика
ПолитикаПравоПрограммированиеПсихологияРелигия
СоциологияСпортСтроительствоФизикаФилософия
ФинансыХимияЭкологияЭкономикаЭлектроника

Date & Time : Calculating Dates of Holidays

Filling a multiplication chart | Spelling out numbers | Datapilot Revisited | DataPilot Revisited II | Ranking and sorting data I | Conditional summation revisited | Regression analysis I : Basic linear formulas | Regression Analysis II : Basic functions, charting | Regression Analysis III : LINEST | Data Consolidation 101 |


Читайте также:
  1. A Condor Book Souvenir Press [Educational) & Academic LTD
  2. A Condor Book Souvenir Press [Educational) & Academic LTD 1 страница
  3. A Condor Book Souvenir Press [Educational) & Academic LTD 10 страница
  4. A Condor Book Souvenir Press [Educational) & Academic LTD 11 страница
  5. A Condor Book Souvenir Press [Educational) & Academic LTD 12 страница
  6. A Condor Book Souvenir Press [Educational) & Academic LTD 2 страница
  7. A Condor Book Souvenir Press [Educational) & Academic LTD 3 страница

October 4th, 2004

This particular exercise in developing formulas for each of the major US holidays is a good way to develop expertise in OOo Calc date manipulation.

US holidays follow simple guidelines for the dates on which they fall. Some holidays fall on specific dates, such as New year’s day on January 1st and independence day on July 4th. Other holidays fall on specific days within the month. For example, Memorial Day is the last Monday in May and Thanksgiving Day is the fourth Thursday in November.

The OOo Calc functions that will feature prominently in this exercise are

· DATE(year; month; day) Returns formatted date corresponding to specified year, month, and day values.

· WEEKDAY(dateValue; type) Returns a decimal value corresponding to the day of the week for the input date.

Before we tackle the US holidays problem, let us become more familar with the above functions and how they are used in OOo Calc formulas.

The DATE function allows us to define and manipulate a dates components - year, month, and day - independently. We have already seen this in an earlier tip

The WEEKDAY function returns a decimal value between 1 & 7 corresponding to the day of the week for the specified date. By invoking the TEXT with the appropriate formmating, we can convert the output of the WEEKDAY function to something more meaningful. In the example below, both formats are shown. The last two formulas show one approach to determining the first day-of-week after a specified date.

The table below generates the 10 major US holidays for a specified year - in cell C2

· New Year’s Day
=DATE(C2;1;1)

· Martin Luther King Jr. Day This is the third Monday in January.
=DATE(C2;1;IF(2<WEEKDAY(DATE(C2;1;1));10-WEEKDAY(DATE(C2;1;1));3-WEEKDAY(DATE(C2;1;1)))+14)

· President’s Day This is the third Monday in February.

=DATE(C2;2;IF(2<WEEKDAY(DATE(C2;2;1));10-WEEKDAY(DATE(C2;2;1));3-WEEKDAY(DATE(C2;2;1)))+14)

· Memorial Day The last Monday in May, we subract 7 days from the first Monday in June.
=DATE(C2;6;IF(2<WEEKDAY(DATE(C2;6;1));10-WEEKDAY(DATE(C2;6;1));3-WEEKDAY(DATE(C2;6;1)))-7)

I leave the rest for you as an exercise!

Posted


Дата добавления: 2015-08-03; просмотров: 49 | Нарушение авторских прав


<== предыдущая страница | следующая страница ==>
Combining data in cells| Financial Functions 3 : Complex Accumulation

mybiblioteka.su - 2015-2024 год. (0.006 сек.)