Функции даты и времени в Excel

В документах Microsoft Excel может потребоваться указать на листе день и время его создания; или Вы работаете с отрезками времени, между которыми нужно посчитать количество дней, месяцев; а может нужно из проставленной даты понять, какому дню недели она соответствует, или добавить к ней определенное значение.

Во всем этом нам могут помочь функции, которые находятся в категории «Дата и время» в Экселе. Их там чуть больше двадцати, и с каждым разом разработчики добавляют новые. В данной статье мы на примерах рассмотрим практически все. И, думаю, после этого у Вас отпадут вопросы: как поставить актуальную дату, как посчитать рабочие дни за определенный период, как узнать последнее число месяца и много других.

Начнем с функции ДАТА – она выводит заданное число в числовом виде (А1). Аргументы у нее такие: (год;месяц;день). При этом формат ячейки автоматически меняется на Дата.

С ее помощью можно прибавить дни (месяца, года) к дате или отнять. Например, в А2 вставим текущую, используя ТДАТА, а затем в В2 пропишем формулу:

=ДАТА(ГОД(А2);МЕСЯЦ(А2);ДЕНЬ(А2)+7)

Что она значит: из А2 поочередно вытягивается каждый аргумент, а к дням добавляется 7. И теперь в В2 всегда будет показано значение на 7 дней больше текущего.

Подробнее прочесть про функцию ДАТА можете, перейдя по ссылке.

Следующая функция РАЗНДАТ. Ее в списке нет, но пользоваться ей можно. Она поможет рассчитать дни, месяцы, годы между заданными значениями. Аргумента у нее три: начальная, конечная дата и единица. В качестве последнего указывается: y, m, d, md, ym, yd. Так, например, если нужно посчитать месяцы между датами (А3 и В3), выбирайте m. Формула выглядит так:

=РАЗНДАТ(А3;В3;»m»)

Подробнее о РАЗНДАТ прочтите в статье: как посчитать дни между датами в Эксель.

ДАТАЗНАЧ – делает из указанной даты порядковый номер. Дело в том, что Эксель каждую дату понимает как определенное число. Отсчет начинается с 01.01.1900 года – это 1, 02.01.1990 – это 2 и так далее. В качестве аргумента – дата, записанная в виде текста (Е1), или объединенные значения из нескольких ячеек (D4).

В примере видно, что 13 ноября 2017 года соответствует число 43052. Его можно использовать для расчетов, применения фильтров, форматирования.

ДЕНЬ – достает из определенной даты только день. В качестве аргумента – указанная в числовом формате дата.

Например, применим формулу к ячейке А1. Результат будет 13 (А5).

ДАТАМЕС – вернет дату на заданное количество месяцев больше или меньше текущей. Аргументы: начальная дата и количество месяцев. Второй может быть как отрицательным, так и положительным числом.

Если к А1 прибавить 2 мес, получится 13 января (А6): =ДАТАМЕС(А1;2). Если нужна дата в прошлом, тогда в скобках укажите отрицательное значение: (А1;-2) – выйдет 13 сентября.

КОНМЕСЯЦА – возвращает последний день указанного месяца. В качестве аргументов: дата начала и число месяцев.

Например, нужно узнать последний день декабря. Начальной датой возьмем А1, значит мне нужно прибавить 1, чтобы из ноября получить декабрь. Формула такая:

=КОНМЕСЯЦА(А1;1)

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

=КОНМЕСЯЦА(А1;-2)

То есть мы отняли 2 месяца от начальной даты (А1), чтобы получился сентябрь.

Функция ЧАС преобразует заданное десятичное число в часы или выделяет из ячейки с датой и временем только час. В Экселе время обозначается десятичными числами, так 12:00 – это 0,5 (А8), а если написать 0,46 (А9), то выделится 11 часов. Соответствующие формулы записаны в Е8 и Е9.

Если же у Вас в ячейке показываются текущие дата и время (А10), то из нее можно выделить число соответствующее часам: =ЧАС(А10).

Функция МИНУТЫ похожа на предыдущую, только возвращает соответствующее значение. Например, в А10 стоит 17:45. Записав формулу: =МИНУТЫ(А10), в В11 покажется только число 45. Если работаете с десятичными числами (А9), то можно указать и их в качестве аргумента. Так из 0,46 выделяется 2 минуты.

Чтобы показать количество секунд, используется функция СЕКУНДЫ. В ячейке А10 стоит 26 сек, хотя они и не отображаются; а числу 0,46 (А9) соответствует 24 сек.

Если из установленного в ячейке дня необходимо выделить только МЕСЯЦ, используется соответствующая функция. Формула: =МЕСЯЦ(А13) вернет в В13 число 11, которое относится к ноябрю.

Если нужно посчитать дни между двумя датами, причем только рабочие, используется ЧИСТРАБДНИ. У нее 3 аргумента: начальная и конечная даты и праздники (необязательный). Подробнее о ней рассказано в статье, ссылка на которую приведена в абзаце про РАЗНДАТ.

Возьмем такую формулу:

=ЧИСТРАБДНИ(А14;В14;С14:С15)

Она посчитает все рабочие даты за период с 13 ноября по 13 января, и при этом вычтет 2 праздника, 1 и 7 января.

Верхняя функция считает за выходные все сб и вс. Если у Вас это другие дни, лучше использовать ЧИСТРАБДНИ.МЕЖД. Ее аргументы: начальная дата, конечная, выходные, праздники.

Аргументу «выходные» соответствует определенное число. При их заполнении появится выпадающий список, из которого нужно его выбрать. Так, например, у меня выходной только в среду, значит, пишу в формуле «14». Указываю праздники, и выходит 51 рабочий дней.

Можно записать выходные и по-другому – в виде 1 и 0, взятых в кавычки. Нули – это рабочие, а единицы – выходные. Запись «0010010» означает, что мы отдыхаем в среду и субботу. Итого, работаем всего 42 дня.

Если на листе Excel в определенной ячейке нужна сегодняшняя дата, тогда используйте ТДАТА. Она в числовом формате отобразит дату и время (А18), установленные на компьютере. Аргументов здесь нет.

ВРЕМЯ – позволит вывести не целое число, соответствующее заданному времени. Аргумента здесь три – часы, мин и сек. Например, 18:14:57 – это 0,760381944.

ВРЕМЗНАЧ тоже вернет числовое значение для времени, только в качестве аргумента используется число в текстовом формате, заключенное в кавычки: =ВРЕМЯЗНАЧ(«18:14:57»).

Функцию СЕГОДНЯ я уже описывала. Она отобразит текущую дату. Аргументы здесь не указываются.

Кстати именно с ее помощью можно посчитать возраст человека по дате рождения. Для этого из текущего дня выделяется год и отнимается г/р человека: =ГОД(СЕГОДНЯ())-1945.

Если нужно определить день недели по заданной дате, тогда воспользуйтесь функцией ДЕНЬНЕД. У нее первый аргумент – это дата, второй – тип, какое число нужно вернуть. При вводе формулы развернется список, из которого его можно выбрать. Например, я хочу, чтобы пн соответствовала единица, вт – двойка и так далее, значит ставлю «11».

В результате, для 13 ноября 2017 года показано – 1. Правильно – это понедельник.

Чтобы вывести номер недели в году, на которую припадает определенное число, используется НОМНЕДЕЛИ. Здесь аргументы те же – дата и тип. Второй выбирается из выпадающего списка. Первая неделя в году может считаться или с 1 января (это 1 система), или по стандарту ISO 8601 с первого четверга (2 система).

Выделяйте в списке значения и смотрите в небольшой области рядом пояснения. Например, возьмем за первую неделю ту, которая начинается с понедельника, значит, ставим для второго аргумента «2».

В примере, 13 ноября в 2017 году (А21) припадает на 47 неделю.

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

Чтобы избежать ошибок в расчетах, начальное значение введем с помощью функции ДАТА (А24). Теперь давайте отсчитаем 80 дней (В24):

=РАБДЕНЬ(А24;В24)

Если нужно, последним аргументом указываются адреса ячеек с праздниками. Ячейка, в которую выводится результат, должна быть отформатирована как дата (С24).

На этом буду заканчивать. Надеюсь, Вы нашли нужную функцию из того списка, который я описала в статье, и теперь сможете вывести или посчитать правильно данные в ячейках с датой и временем в Экселе.

---

Оценить статью: