Excel предлагает не так много функций, предназначенных для работы с датой. Эти функции представлены в раскрывающемся списке Формулы→Библиотека функций →Дата и время.
В таблице приведены функции, связанные с использованием даты, которые доступны в Excel.
Отображение текущей даты
Функция, приведенная ниже, отображает в ячейке текущую дату (учитывайте, что если установлен текстовый или общий формат ячейки, будет отображено порядковое число сегодняшней даты).
=СЕГОДНЯ()
Можно отобразить не только текущую дату, но и объединить ее с любым текстом. Например, следующая формула будет выводить на экран такой текст: Сегодня понедельник, 9 Апрель, 2010.
=“Сегодня ”&ТЕКСТ(СЕГОДНЯ();“дддд, д ММММ, гггг”)
Помните, что результат функции СЕГОДНЯ изменяется каждый раз при расчете рабочего листа. Например, если ввести в ячейку рабочего листа любую из рассмотренных формул, они будут отображать текущую дату. Но откройте этот же рабочий лист на следующий день, и вы увидите, что формулы отображают совсем другую текущую дату.
Чтобы ввести в ячейку штамп даты (значение даты, которое не будет изменяться каждый последующий день), нажмите клавиши <Ctrl+Shift+; (точка с запятой)>. Текущая дата будет введена в ячейку в текстовом формате без использования формулы, поэтому она останется неизменной.
Отображение произвольных дат
Дату можно добавить в ячейку, непосредственно введя ее в любом формате, поддерживаемом в приложении Excel. Дату также можно создать; для этого воспользуйтесь функцией ДАТА. Эта функция использует три аргумента: год, месяц и день. Например, приведенная ниже формула возвращает дату, состоящую из года, взятого из ячейки А1, месяца, взятого из ячейки В1, и дня – из ячейки С1.
=ДАТА(А1;В1;С1)
Примечание
Обратите внимание, что функция ДАТА принимает также недопустимые аргументы и приводит результат в соответствие ожидаемому. Например, следующая формула использует число 13 как аргумент месяца и возвращает 1 Январь, 2011. Аргумент месяца автоматически преобразуется в первый месяц следующего года.
=ДАТА(2010;13;1)
Достаточно часто в функции ДАТА в качестве аргументов используются другие функции. Например, в приведенной ниже формуле функции ГОД и СЕГОДНЯ, используемые как аргументы, возвращают День Независимости США (4 Июля) текущего года.
=ДАТА(ГОД(СЕГОДНЯ());7;4)
Функция ДАТАЗНАЧ преобразует текстовую строку, представляющую собой дату, в порядковое число даты. Следующая формула возвращает значение порядкового числа даты 22 Августа 2010 года – 40412:
=ДАТАЗНАЧ(“22.8.2010”)
Для того чтобы отобразить результат этой формулы в виде даты, достаточно применить к ячейке формат даты.
Предупреждение
Будьте особо внимательны при использовании функции ДАТАЗНАЧ. Текстовая строка, которая может быть использована как дата в одной стране, совершенно не подходит для ввода даты в другой стране. Предыдущий пример работает прекрасно в том случае, если система настроена для форматов даты России или Украины. Однако при использовании других региональных форматов даты (в частности, форматов США) формула может возвращать ошибку, поскольку приложение Excel будет искать восьмой день двадцать второго месяца.
Создание последовательности дат
В отдельных случаях может возникнуть необходимость вставить в рабочий лист целый ряд последовательных дат. Например, при еженедельном отслеживании продаж требуется ввести несколько последовательных дат с интервалом в семь дней. Эти даты будут служить основой для систематизации коммерческой информации.
Самый эффективный способ ввода ряда последовательных дат не требует никаких формул. Просто воспользуйтесь инструментом, встроенным в Excel, – автозаполнением. Для этого введите в ячейку первую дату, а затем, удерживая нажатой правую кнопку мыши, перетащите в необходимом направлении маркер автозаполнения (маленький черный квадрат в правом нижнем углу контура выделения). Отпустите правую кнопку мыши и выберите в контекстном меню команду Заполнить по дням.
Функция автозаполнения весьма гибкая. Например, можно создать последовательность дат, разделенных десятью днями. Для этого введите две первые даты в две ячейки, выделите эти ячейки, перетащите правой кнопкой мыши маркер автозаполнения и выберите в контекстном меню команду Заполнить по дням.
Использование формул для создания последовательности дат все же имеет свои преимущества. В этом случае достаточно указать первую дату, после чего остальные автоматически приводятся ей в соответствие. Вначале введите в ячейку начальную дату, а затем добавьте формулы, скопировав их вниз по столбцу, чтобы получить даты, следующие за исходной.
В представленном далее примере мы введем первую дату ряда в ячейку А1, а формулу – в ячейку А2. Скопируйте формулу в ячейке вниз по столбцу столько раз, сколько это необходимо.
Данная формула позволяет создать ряд последовательных дат, следующих одна за другой с интервалом в семь дней.
=А1+7
Чтобы создать ряд последовательных дат, отстоящих друг от друга на месяц, воспользуйтесь такой формулой:
=ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;ДЕНЬ(А1))
Формула, приведенная ниже, возвращает даты, следующие одна за другой с интервалом в год.
=ДАТА(ГОД(А1)+1;МЕСЯЦ(А1);ДЕНЬ(А1))
Чтобы создать ряд, который состоит из дат, относящихся только к рабочим дням (т.е. исключая субботы и воскресенья), воспользуйтесь приведенной ниже формулой. Имейте в виду: она предполагает, что дата, введенная в ячейку А1, – рабочий день.
=ЕСЛИ(ДЕНЬНЕД(А1)=6;Al+3;А1+1)
Преобразование строки в дату
Даты, импортируемые как текстовые строки, тоже можно преобразовать в дату. Предположим, в результате импорта из другого приложения получена приведенная ниже текстовая строка, которая в действительности представляет собой дату 21 августа 2010 года. Этот текст состоит из четырех знаков, относящихся к году, двух знаков месяца и двух знаков дня.
20100821
Чтобы преобразовать эту строку в фактическую дату, воспользуйтесь формулой, приведенной ниже. Эта формула предполагает, что исходные данные занесены в ячейку А1.
=ДАТА(ЛЕВСИМВ(А1;4);ПСТР(А1;5;2);ПРАВСИМВ(А1;2))
Представленная формула использует текстовые функции ЛЕВСИМВ, ПСТР и ПРАВСИМВ для извлечения из заданной строки определенных чисел. После этого она применяет их как аргументы функции ДАТА.
Вычисление количества дней между двумя датами
Самый распространенный тип вычислений, связанный с датами, – это определение количества дней между двумя заданными датами. Предположим, что на одном из листов рабочей книги вычисляется процент, получаемый по срочному вкладу. Размер полученного процента зависит от количества дней, на которое открыт счет. Если рабочий лист содержит дату открытия и дату закрытия счета, то можно легко определить срок в днях, на который был открыт счет.
Поскольку даты на рабочем листе, как правило, сохраняются в виде последовательных значений, то для вычисления количества дней можно использовать простое вычитание. Предположим, что ячейки А1 и В1 содержат некоторые даты. Следующая формула возвращает количество дней между этими датами:
=А1-В1
Вероятнее всего, Excel автоматически отформатирует результирующую ячейку как дату, а не как числовое значение. Вам потребуется вручную изменить формат этой ячейки на числовой. Если ячейка В1 содержит более позднюю дату, чем ячейка А1, то результат, возвращаемый формулой, будет отрицательным.
Примечание
Если приведенная выше формула отображает некорректное значение, убедитесь, что ячейки A1 и В1 действительно содержат даты, а не текстовую информацию, похожую на даты.
В некоторых случаях вычисление разницы между двумя датами может потребовать значительных усилий. Чтобы продемонстрировать это, проведем аналогию с “телеграфными столбами”. Например, если требуется определить количество телеграфных столбов, то задачу их подсчета можно решить двумя способами: непосредственно сосчитав количество столбов или определив количество пролетов между ними. При этом имейте в виду, что количество столбов всегда на один больше, чем пролетов.
Чтобы рассмотреть эту ситуацию применительно к некоторому диапазону дат, в качестве примера приведем кампанию по продвижению на рынок некоторого товара. Предположим, что начальная дата кампании по продвижению товара – 1 февраля, а завершающая дата – 9 февраля. Просто вычитая одну цифру из другой, мы получим ответ: восемь дней. Но в действительности срок продвижения товара – девять дней. В этом случае, чтобы получить правильный ответ, необходимо сосчитать дни “включительно”, а не определять количество дней между датами. Ниже приведена формула, позволяющая вычислить длительность кампании по продвижению товара (предположим, что у нас есть ячейки, соответствующие этим названиям).
=ПоследнийДень-НачальныйДень+1
Вычисление количества рабочих дней между двумя датами
Excel умеет не только вычислять количество дней между двумя датами. В случае необходимости можно исключать из числа этих дней выходные и праздничные дни. Например, вычислим, сколько рабочих дней выпадает на ноябрь месяц. Для этого необходимо исключить из расчета субботы, воскресенья и праздничные дни (если таковые имеются), воспользовавшись функцией ЧИСТРАБДНИ.
Примечание
Название функции ЧИСТРАБДНИ часто вводит в заблуждение. Она не имеет никакого отношения к чистоте или рабству, а, вместо этого, возвращает количество рабочих дней между двумя заданными датами.
Итак, функция ЧИСТРАБДНИ вычисляет разницу между двумя датами, исключая выходные дни (субботы и воскресенья). Кроме того, как дополнительное условие можно, определить диапазон ячеек, содержащий даты, на которые приходятся праздничные дни. Эти даты также могут быть исключены при расчете. Естественно, Excel не имеет никакой возможности самостоятельно определять праздничные дни, поэтому вы должны обеспечить программу этой информацией, указав соответствующий диапазон ячеек.
На рисунке показан рабочий лист, на котором вычисляется количество рабочих дней между определенными датами. В диапазоне А2:А8 содержится перечень дат, приходящихся на праздничные дни. Формулы, которые введены в столбце С, вычисляют количество рабочих дней, расположенных между датами в столбцах А и В. Например, формула в ячейке С11 будет иметь такой вид:
=ЧИСТРАБДНИ(A11;В11;А2:А8)
Формула возвращает значение 3. Это означает, что семидневный период, начинающийся с 1 января, содержит три рабочих дня. Другими словами, при расчете исключаются два праздничных и два выходных дня. Ячейка С12 содержит формулу, которая вычисляет общее количество рабочих дней в первом полугодии 2010 года.
Новинка
В Excel 2010 добавлена усовершенствованная версия функции ЧИСТРАБДНИ, которая называется ЧИСТРАБДНИ.МЕЖД. Она полезна, когда нужно считать выходными днями не субботу и воскресенье, а другие дни недели.
Вычисление даты по графику рабочих дней
Функция РАБДЕНЬ является прямой противоположностью функции ЧИСТРАБДНИ. Например, при запуске в работу определенного проекта, на выполнение которого требуется десять дней, функция поможет вычислить дату окончания проекта.
В приведенной ниже формуле с помощью функции РАБДЕНЬ определяется дата, следующая через десять рабочих дней после 8 января 2010 года. Рабочими днями считаются будние дни недели (с понедельника по пятницу).
=РАБДЕНЬ(“08.01.2010”;10)
Формула возвращает значение 20 января 2010 года (между 8 и 20 января выпадает два выходных дня).
Предупреждение
Имейте в виду, что результат, возвращаемый данной формулой, полностью зависит от региональных форматов даты. В других странах приведенная выше жестко закодированная дата может интерпретироваться как 1 августа 2010 года. Чтобы избежать этого, используйте следующую формулу:
=РАБДЕНЬ(ДАТА(2010;1;8);10)
Интересно, что второй аргумент функции РАБДЕНЬ может быть отрицательным. Как и в случае с функцией ЧИСТРАБДНИ, функция РАБДЕНЬ может использовать третий не обязательный аргумент, который определяет диапазон ячеек с датами, относящимися к праздничным дням и дням отпуска.
Вычисление количества лет между двумя датами
Следующая формула вычисляет количество лет между двумя датами. В данном случае предполагается, что ячейки А1 и В1 содержат даты.
=ГОД(А1)-ГОД(В1)
Для извлечения года из каждой заданной даты в этой формуле используется функция ГОД, после чего один год вычитается из другого. Имейте в виду следующее: если В1 содержит более близкий к текущей дате год, чем дата в ячейке А1, результат будет отрицательным.
Следует обратить внимание, что данная функция не вычисляет часть года. Например, если ячейка А1 содержит дату 31.12.2010, а ячейка В1 – дату 01.01.2011, то формула вернет разницу в один год, несмотря на то, что эти даты отличаются всего лишь на один день.
Для вычисления количества лет между двумя датами можно также использовать функцию ДОЛЯГОДА. Эта функция возвращает количество лет, включая неполные, например:
=ДОЛЯГОДА(А1;В1;1)
Так как функцию ДОЛЯГОДА часто используют в финансовых приложениях, в ней существует необязательный третий аргумент, представляющий “базис” года. Значение 1 этого аргумента указывает на текущий год.
Вычисление возраста человека
Возраст человека – это количество полных лет, которые человек уже прожил. Формула, приведенная для вычисления количества лет между двумя датами в предыдущем разделе, не сможет корректно определить это значение. В этом случае нужно использовать две другие формулы.
Следующая формула возвращает возраст человека, дата рождения которого содержится в ячейке А1. Эта формула использует функцию ДОЛЯГОДА.
=ЦЕЛОЕ(ДОЛЯГОДА(СЕГОДНЯ();A1;1))
Ниже приведена формула, которая применяет для вычисления возраста функцию РАЗНДАТ.
=РАЗНДАТ(А1;СЕГОДНЯ();“у”)
Где найти функцию РАЗНДАТ
В этом разделе неоднократно упоминалась функция РАЗНДАТ. Обратите внимание, что ее нет в числе доступных в диалоговом окне Мастер функций, также она не содержится в списке автозавершения формулы. Поэтому для того, чтобы использовать эту функцию, ее необходимо ввести вручную.
Функция РАЗНДАТ была известна еще со времен Lotus 1-2-3, и, очевидно, с целью обеспечения совместимости Excel поддерживает ее. По определенным причинам Microsoft старается сохранить эту функцию в секрете. Интересно, что в версиях программы ниже Excel 2000 функция РАЗНДАТ даже не упоминалась. Из интерактивной справки Excel 2002 были удалены все ссылки на нее, хотя сама функция в этой версии приложения все еще доступна.
Тем не менее, функция РАЗНДАТ очень удобна. Она позволяет вычислить количество дней, месяцев и лет, находящихся между двумя датами. В ней используется три аргумента: начальная_дата, конечная_дата и определенный код_единицы_измерения, с помощью которого задается единица времени. Код в функции всегда заключается в кавычки. Ниже приведена таблица, в которой представлены допустимые коды для третьего аргумента.
Имейте в виду, что аргумент начальная_дата должен находиться левее, чем аргумент конечная_дата, в противном случае функция возвращает ошибку. Кроме того, в русифицированной Excel коды единиц измерения должны вводиться английскими буквами.
Вычисление дня года
Итак, 1 января – это первый день года, а 31 декабря – последний. Как определить, какой по счету день приходится на произвольную дату года? Следующая формула возвращает порядковое число дня года для даты, содержащейся в ячейке А1:
=А1-ДАТА(ГОД(A1);1;0)
Если задать аргумент день равным нулю, функция ДАТА интерпретирует его как последний день предыдущего месяца. Можно задавать отрицательные значения, они интерпретируются аналогично.
Приведенная ниже формула возвращает день года текущей даты:
=СЕГОДНЯ()-ДАТА(ГОД(СЕГОДНЯ());1;0)
Следующая формула возвращает количество дней в году, которые остались после наступления даты, заданной в ячейке А1:
=ДАТА(ГОД(А1);12;31)-А1
Обратите внимание, что при вводе любой из этих формул Excel применяет к ячейке формат даты, и чтобы увидеть полученный результат, необходимо применить к ячейке числовой формат.
Следующую формулу рекомендуется использовать для того, чтобы преобразовать заданное порядковое число дня года (например, девяностый день) в определенную дату и год, которые соответствуют этому дню. В этой формуле предполагается, что содержимое ячейки А1 – это год, а содержимое ячейки В1 – это день года.
=ДАТА(A1;1;В1)
Вычисление дня недели
Для вычисления дня недели часто используют функцию ДЕНЬНЕД. Она принимает в качестве аргумента дату и возвращает целое число, соответствующее дню недели, в диапазоне между 1 и 7. Следует отметить, что в данном случае отсчет дней начинается с воскресенья, как это принято в англоязычных программных продуктах. Например, следующая формула возвращает значение 5, поскольку первый день 2010 года выпадает на пятницу:
=ДЕНЬНЕД(ДАТА(2010;1;1))
Функция ДЕНЬНЕД может принимать и второй, необязательный аргумент, который определяет систему нумерации дней недели для вывода результата. Если в качестве второго аргумента введено значение 2, то функция возвращает значение 1 для понедельника, 2 – для вторника и т.д. Если же в качестве второго аргумента ввести значение 3, то функция вернет 0 для понедельника, 1 – для вторника и т.д.
Для того, чтобы вычислить день недели даты в ячейке, можете использовать собственный числовой формат. Выберите команду Главная→Число→Числовой формат→Другие числовые форматы. В поле Тип диалогового окна Формат ячеек введите дддд и щелкните на кнопке ОК. Введите в ячейку дату (например, 22.07.2010). В результате в ячейке будет отображено четверг – день недели введенной даты.
Вычисление даты прошлого воскресенья
Хотите узнать, на какую дату приходилось прошедшее воскресенье? Воспользуйтесь формулой, приведенной ниже. Но имейте в виду, если текущая дата выпадает на воскресенье, то формула возвращает текущую дату.
=СЕГОДНЯ()-ОСТАТ(СЕГОДНЯ()-1;7)
Для того чтобы найти дату другого дня, а не воскресенья, измените аргумент 1 в формуле на любой другой в диапазоне между 2 (найти понедельник) и 7 (суббота).
Вычисление дня недели, следующего после заданной даты
Приведенная ниже формула возвращает дату заданного дня недели, следующего за определенной датой. Например, эту формулу можно использовать, чтобы вычислить дату первого понедельника после 1 июня 2010 года. Предположим, что ячейка А1 содержит дату, а ячейка А2 – число между 1 и 7 (1 соответствует воскресенью, 2 – понедельнику и т.д.).
=А1+А2-ДЕНЬНЕД(А1)+(А2<деньнед(а1))*7>
Вычисление даты n-го дня недели в заданном месяце
Иногда возникает потребность вычислить дату, приходящуюся на заданный день недели. Приведем пример. Каждую вторую пятницу месяца в компании – день выплаты заработной платы. Как определить дату выплат в каждом месяце? Воспользуйтесь для этого формулой, приведенной ниже.
=ДАТА(А1;А2;1)+А3-ДЕНЬНЕД(ДАТА(А1;А2;1))+ (А4-(А3>=ДЕНЬНЕД(ДАТА(А1;А2;1))))*7
Для этой формулы приняты следующие условия:
• ячейка А1 содержит значение года; • ячейка А2 содержит значение месяца; • ячейка А3 содержит значение дня (1 для воскресенья, 2 – для понедельника и т.д.); • ячейка А4 содержит номер вхождения (к примеру, 2 для выбора второго вхождения дня недели, заданного в ячейке А3).
Например, если использовать эту формулу для определения даты второй пятницы в июне 2010 года, формула вернет дату 8 июня 2010 года.
Примечание
Если значение, содержащееся в ячейке А4, не принадлежит к диапазону дней указанного месяца, то формула вернет дату, относящуюся к следующему месяцу. Например, если задать определение даты шестой пятницы июня 2010 года (а такой даты не существует), то формула вернет дату первой пятницы июля.
Вычисление количества заданных дней недели в месяце
Формула, приведенная ниже, позволяет вычислить количество определенных дней недели заданного месяца. Предположим, ячейка А1 содержит дату, а ячейка А2 – порядковое число дня недели (значение 1 соответствует воскресенью, 2 – понедельнику и т.д.). Имейте в виду, что это формула массива, поэтому, чтобы ввести ее, используйте клавиши <Ctrl+Shift+Enter>.
{=СУММ((ДЕНЬНЕД(ДАТА(ГОД(А1);МЕСЯЦ(А1);СТРОКА(ДВССЫЛ(“1:” &ДЕНЬ(ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;0))))))=В1)*1)}
Если ячейка А1 содержит дату 8 января 2010 года, а ячейка В1 – значение 3, т.е. вторник, формула возвращает значение 4. Это значит, что январь 2010 года содержит четыре вторника.
Приведенная выше формула массива вычисляет год и месяц, используя функции ГОД и МЕСЯЦ. Однако ее можно немного упростить. Для этого введите год и месяц заданной даты в отдельные ячейки. Для приведенной ниже формулы, которая тоже является формулой массива, приняты условия, что ячейка А1 содержит значение года, ячейка А2 – месяца и ячейка В1 – значение дня недели.
{=СУММ((ДЕНЬНЕД(ДАТА(A1;А2;СТРОКА(ДВССЫЛ(“1:” &ДЕНЬ(ДАТА(А1;А2+1;0))))))=В1)*1)}
На рисунке показан результат использования этой формулы на рабочем листе. В этом случае используются и абсолютные, и смешанные ссылки на ячейки, поэтому можно скопировать эту формулу. К примеру, формула в ячейке СЗ выглядит следующим образом:
{=СУММ((ДЕНЬНЕД(ДАТА($В$2;$А3;СТРОКА(ДВССЫЛ(“1:” &ДЕНЬ(ДАТА($В$2;$А3+1;0))))))=С$1)*1)}
Итоговые формулы на этом листе используют функцию СУММ. Они вычисляют общее количество дней в месяце (столбец J) и количество каждого дня недели в году (строка 15).
Отображение даты в текстовом виде
Иногда может потребоваться отобразить дату в виде, не предусмотренном ни в одном встроенном формате дат. К примеру, с помощью приведенной ниже формулы дату 16.3.2010 можно отобразить как 16 марта 2010 г. Предполагается, что стандартная запись даты находится в ячейке А1.
=ДЕНЬ(А1)&“ ”&ТЕКСТ(A1;“ММММ”)&“а ”&ГОД(А1)&“ г.”
Примечание
Результат этой формулы имеет текстовый формат, а не формат даты.
Конечно, такое преобразование может быть полезным, только если предусмотреть выбор суффикса с помощью ряда функций ЕСЛИ (например, для апреля нужно применить суффикс я и удалить ь).
Вычисление праздничных дат
Вычисление дат, выпадающих на специфические праздничные дни, требует использования специальных методов. Безусловно, это не касается таких дат, как Новый год или День Независимости США, поскольку они определены издавна. Для них можно просто использовать функцию ДАТА, о которой речь шла ранее. Следующая формула отображает дату Нового года. Новый год всегда приходится на 1 января; нужный год введите в ячейку А1.
=ДАТА(А1;1;1)
На самом деле проблем не возникает только с праздниками, постоянно отмечаемыми по строго определенным датам. А как быть с праздничными днями, дату которых определяет временной период? К таким праздникам в США, например, относится День труда, который отмечается в первый понедельник сентября.
На рисунке показана рабочая книга Excel, в которой вычисляются даты нескольких праздничных дней. Все формулы содержат ссылку на ячейку А1 – год, для которого вычисляются даты. Поскольку такие праздники, как Новый год и День Независимости, отмечаются в строго определенные дни года, для вычисления их дат используется только функция ДАТА.
Новый год
Новый год отмечается первого января.
=ДАТА(А1;1;1)
День Мартина Лютера Кинга
Этот праздничный день приходится на третий понедельник января. Далее мы узнаем, на какую дату выпадет этот день в 2010 году, определенном в ячейке А1.
=ДАТА(А1;1;1)+ЕСЛИ(2<деньнед(дата(а1;1;1));7-деньнед(дата(а1;1;1))+2; 2-деньнед(дата(а1;1;1)))+((3-1)*7)=””>
День президентов празднуется в третий понедельник февраля. Следующая формула вычисляет дату этого дня в указанном году (ячейка А1):
=ДАТА(А1;2;1)+ЕСЛИ(2<деньнед(дата(а1;2;1));7-деньнед(дата(а1;2;1))+2; 2-деньнед(дата(а1;2;1)))+((3-1)*7)=””>
День памяти павших в гражданской войне в США 1861-1865 гг., в испано-американской и других войнах отмечается в США в последний понедельник мая. Дата, на которую он придется в указанном году (ячейка А1), определяется с помощью следующей формулы:
=ДАТА(А1;6;1)+ЕСЛИ(2<деньнед(дата(а1;6;1));7-деньнед(дата(а1;6;1))+2; 2-деньнед(дата(а1;6;1)))+((1-1)*7)-7=””>
День Независимости США
Этот праздник из года в год отмечается строго 4 июля.
=ДАТА(А1;7;4)
День труда
День труда – это первый понедельник сентября. Формула, приведенная ниже, определяет его дату в указанном году (ячейка А1).
=ДАТА(А1;9;1)+ЕСЛИ(2<деньнед(дата(а1;9;1));7-деньнед(дата(а1;9;1))+2; 2-деньнед(дата(а1;9;1)))+((1-1)*7)=””>
Этот праздник выпадает на 11 ноября.
=ДАТА(A1;11;11)
День Колумба
Этот праздник выпадает на второй понедельник октября. Для вычисления его даты в указанном году (ячейка А1) используется следующая формула:
=ДАТА(А1;10;1)+ЕСЛИ(2<деньнед(дата(а1;10;1));7-деньнед(дата(а1;10;1))+2; 2-деньнед(дата(а1;10;1)))+((2-1)*7)=””>
День Благодарения празднуется в четвертый четверг ноября. Следующая формула вычисляет дату этого дня в указанном году (ячейка А1):
=ДАТА(А1;11;1)+ЕСЛИ(5<деньнед(дата(а1;11;1));7-деньнед(дата(а1;11;1))+5; 5-деньнед(дата(а1;11;1)))+((4-1)*7)=””>
Католическое Рождество празднуется 25 декабря.
=ДАТА(A1;12;25)
Вычисление даты последнего дня месяца
Для того чтобы вычислить дату, соответствующую последнему дню месяца, можно воспользоваться функцией ДАТА. При этом число месяцев необходимо увеличить на единицу, а для дня использовать значение 0. Другими словами, в расчет принимается условие, что нулевой день месяца – это последний день предыдущего месяца.
Приведенная ниже формула возвращает дату, которая соответствует последнему дню месяца. Данная формула предполагает, что исходная дата содержится в ячейке А1.
=ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;0)
Эту формулу можно модифицировать, после чего она позволит вычислять количество дней, содержащихся в определенных месяцах. Следующая формула возвращает целое числовое значение, которое представляет собой количество дней в месяце, заданном в ячейке А1:
=ДЕНЬ(ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;0))
Проверка високосного года
Безусловно, для того чтобы выяснить, является ли високосным определенный год, можно написать формулу, которая установит дату шестидесятого дня года: 29 февраля или 1 марта. Но гораздо эффективнее воспользоваться формулой ДАТА, которая в дополнение откорректирует результат в том случае, если введен неправильный аргумент (например, когда для дня введено значение 29, в то время как в феврале этого года всего 28 дней).
Данная формула возвращает значение ИСТИНА, если дата, находящаяся в ячейке А1, относится к високосному году. В противном случае формула возвращает значение ЛОЖЬ.
=ЕСЛИ(МЕСЯЦ(ДАТА(ГОД(А1);2;29))=2;ИСТИНА;ЛОЖЬ)
Предупреждение
Помните, что для 1900 года результат истина, возвращаемый данной функцией, будет неправильным.
Вычисление квартального периода
В финансовых отчетах часто полезно отображать информацию поквартально. Формула, приведенная ниже, возвращает числовое значение в диапазоне от 1 до 4, которое соответствует календарному кварталу для даты, содержащейся в ячейке А1.
=ОКРУГЛВВЕРХ(МЕСЯЦ(А1)/3;0)
Данная формула делит количество месяцев на три, а затем округляет полученный результат.
Отображение года римскими цифрами
Любителям старых кинофильмов наверняка понравится эта особенность Excel. Следующая формула отображает 1945 год римскими цифрами и возвращает MCMXLV:
=РИМСКОЕ(1945)
Функция РИМСКОЕ возвращает текстовую строку, поэтому ее результат нельзя использовать для выполнения вычислений. Кроме того, Excel не имеет обратной функции, которая позволила бы преобразовать римские цифры в арабские.
В начало
Полезное
