Функции даты и времени в Excel
В документах Microsoft Excel может потребоваться указать на листе день и время его создания; или Вы работаете с отрезками времени, между которыми нужно посчитать количество дней, месяцев; а может нужно из проставленной даты понять, какому дню недели она соответствует, или добавить к ней определенное значение.
Начнем с функции ДАТА – она выводит заданное число в числовом виде (А1). Аргументы у нее такие: (год;месяц;день). При этом формат ячейки автоматически меняется на Дата.
С ее помощью можно прибавить дни (месяца, года) к дате или отнять. Например, в А2 вставим текущую, используя ТДАТА, а затем в В2 пропишем формулу:
Что она значит: из А2 поочередно вытягивается каждый аргумент, а к дням добавляется 7. И теперь в В2 всегда будет показано значение на 7 дней больше текущего.
Подробнее прочесть про функцию ДАТА можете, перейдя по ссылке.
Следующая функция РАЗНДАТ. Ее в списке нет, но пользоваться ей можно. Она поможет рассчитать дни, месяцы, годы между заданными значениями. Аргумента у нее три: начальная, конечная дата и единица. В качестве последнего указывается: y, m, d, md, ym, yd. Так, например, если нужно посчитать месяцы между датами (А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, чтобы из ноября получить декабрь. Формула такая:
Чтобы узнать, сколько дней в другом месяце, например, сентябре, формула получится:
То есть мы отняли 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 аргумента: начальная и конечная даты и праздники (необязательный). Подробнее о ней рассказано в статье, ссылка на которую приведена в абзаце про РАЗНДАТ.
Она посчитает все рабочие даты за период с 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).
На этом буду заканчивать. Надеюсь, Вы нашли нужную функцию из того списка, который я описала в статье, и теперь сможете вывести или посчитать правильно данные в ячейках с датой и временем в Экселе.
Включение, отключение и настройка автозамены в Excel. Как отключить Автозамену на дату в Excel?
Если в качестве второго аргумента функции ДЕНЬНЕД подставить , то дням недели будут соответствовать номера от 1 (понедельник) до 7 (воскресенье). В итоге функция вернет нам значение :
