Возвращение даты, отстоящей на заданное количество рабочих дней от заданной даты

Эта операция выполняется с помощью функции РАБДЕНЬ. Рабочими днями не считаются выходные дни и дни, определенные как праздничные. Функция РАБДЕНЬ используется, чтобы исключить выходные дни или праздники при вычислении дат платежей, ожидаемых дат доставки или количества фактически отработанных дней. Ее синтаксис:

РАБДЕНЬ (начальная_дата; количество_дней; праздники).

• количество_дней – количество невыходных и непраздничных дней до или после начальная_дата. Положительное значение аргумента количество_дней означает будущую дату; отрицательное – прошедшую;

• праздники – необязательный список из одной или нескольких дат, которые требуется исключить из рабочего календаря, например, государственные праздники. Список может представлять собой диапазон ячеек, содержащих даты.

Определение количества рабочих дней между двумя датами

Эта операция выполняется с помощью функции ЧИСТРАБДНИ (праздники и выходные в возвращаемое число не включаются). Ее синтаксис:

ЧИСТРАБДНИ (начальная_дата; конечная_дата; праздники),

• праздники – необязательный список из одной или нескольких дат, которые требуется исключить из рабочего календаря, например, государственные праздники.

Определение разницы между двумя датами в годах

Пусть известна дата зачисления некоторого сотрудника на предприятие. Требуется определить стаж его работы по состоянию на текущую дату. Традиционно это значение вычисляется точно с учетом лет, месяцев и дней.

В MS Excel расчет количества лет между двумя датами выполняется с помощью функции ДОЛЯГОДА, возвращающей значение вещественного типа, в том числе и часть года. Например, количество лет между датами 25.02.1934 и 1.01.2004, определенное с помощью функции ДОЛЯГОДА, составляет 69,90. Очевидно, что разница между этими датами составит 70 лет только 25.02.2004. Синтаксис функции ДОЛЯГОДА имеет вид:

ДОЛЯГОДА (начальная_дата; конечная_дата; 1),

Третий аргумент (базис) функции, равный 1, означает, что при определении результата в вычислениях будут использоваться фактические значения числа дней в полных годах и полных месяцах, заключенных между заданными датами. Возможны другие значения базиса, использующиеся в финансовых расчетах.

Функция доступна, если установлен Пакет анализа (Сервис  Надстройки).

Если требуется вычислить точно разницу в годах, месяцах и днях между двумя датами, необходимо воспользоваться следующей формулой:

= РАЗНДАТ (нач_дата; кон_дата; "y") & " лет " &

РАЗНДАТ (нач_дата; кон_дата; "ym") & " мес. " &

РАЗНДАТ (нач_дата; кон_дата; "md") & " дн."

Данная формула для дат 25.02.1934 и 1.01.2004 возвратит текстовую строку: 69 лет 10 мес. 7 дн.

Задачи Задача 5.1. Учет издания методической литературы

Предметная область: учет количества изданий методической литературы по месяцам первого полугодия текущего года. Элементы предметной области: автор, тип издания (1 – методические указания, 2 – программа практики, 3 – учебное пособие), тираж, дата сдачи в издательство (ввести не менее 20 записей с разными датами первого полугодия).

Отношения:

• брошюра или книга выходит в свет в точно установленный срок (в зависимости от категории) с момента подачи в печать:

  Тип издания	Срок издания
  1	15 дней
  2	30 дней
  3	50 дней

• издательство не работает по субботам и воскресеньям.

Требования:

• разработать и реализовать табличную модель учета поступления рукописей к печати и их издания;

• для идентификации дат, соответствующих выходным дням, использовать условное форматирование;

• определить объемы выпущенной литературы по месяцам заданного периода.

Методические указания к решению

1. Ввести исходные данные согласно рис. 31.

	A	B	C	D	E	F	G	Н	I
1	Учет издания методической литературы
2	Категория	Срок		Месяц	Тираж			?
3	1	15		Январь
4	2	30		…
5	3	50
…				Июнь
15
16	Автор	Категория	Тираж	Дата подачи	Срок исполнения	Дата выпуска	Точн. дата	Номер мес.	Название месяца
17					?	??	*	**	***

Рис. 31. Макет таблицы для задачи «Учет издания методической литературы»

2. Для ввода названий месяца, а также дат подачи рукописей целесообразно использовать средство Автозаполнение. Достаточно ввести слово Январь и с помощью маркера заполнения получить весь список месяцев. Для получения ряда дат воспользоваться командой Заполнение по месяцам контекстного меню.

3. После ввода дат выполнить проверку на наличие дат, приходящихся на выходные дни. Для этого в столбце J ввести формулу вида:

= ДЕНЬНЕД (D17;2)

4. Для быстрого ввода дат, которые нужно исправить, целесообразно использовать средство Условное форматирование.

• Инструкция по добавлению условного форматирования

1. выделить диапазон ячеек, подлежащих условному форматированию;

2. исполнить команды Формат  Условное форматирование;

3. в отобразившемся диалоговом окне Условное форматирование в раскрывающемся списке выбрать значение либо формула;

4. определить условие (или ввести формулу);

5. щелкнуть по кнопке Формат и определить форматирование, которое необходимо применить, если заданное условие удовлетворяется (например, изменить вид, выполнив заливку цветом);

6. для добавления дополнительных условий щелкнуть по кнопке А также и повторить пп. 3-5; щелкнуть по кнопке ОК.

На рис. 32 представлено диалоговое окно Условное форматирование с критериями для выявления ячеек с датами выходных дней. Если в списке дат есть выходные дни, исправить их.

5. Формула для определения срока исполнения имеет вид:

= ВЫБОР (B17;$B$3,$B$4;$B$5).

6. Если игнорировать условие, что издательство не работает в выходные дни, то формула определения даты выдачи готовой продукции будет иметь вид:

= D17 + E17.

Рис. 32. Условное форматирование с использованием значений в качестве критерия

7. Для выявления дат, соответствующих выходным дням, необходимо применить условное форматирование с использованием формул в качестве критериев. Они отображены на рис. 33. Обратите внимание, что обе формулы содержат ссылку на левую верхнюю ячейку выбранного диапазона. Для того, чтобы формула работала для всех ячеек диапазона, ссылка на эту ячейку должна быть относительной

Рис. 33. Условное форматирование с использованием формул в качестве критерия

8. В графе Точная дата записать формулу определения даты выдачи литературы с учетом выходных дней, используя функцию РАБДЕНЬ (праздники первого полугодия – 1 января, 7 января, 8 марта, 1 мая, 28 июня – День Конституции).

9. Используя функцию МЕСЯЦ, определить номер месяца уточненной даты.

10. Для определения названия месяца по его номеру использовать функцию ВЫБОР.

11. Определить объемы выпущенной литературы по месяцам данного периода, используя условное суммирование.