Функции для работы с датами в Excel: примеры использования

Для работы с датами в Excel в разделе с функциями определена категория «Дата и время». Разглядим более всераспространенные функции в данной группы.

Как Excel обрабатывает время

Программка Excel «принимает» дату и время как обыденное число. Электрическая таблица конвертирует подобные данные, приравнивая день к единице. В итоге значение времени представляет собой долю от единицы. Например, 12.00 – это 0,5.

Значение даты электрическая таблица конвертирует в число, равное количеству дней от 1 января 1900 года (так решили создатели) до данной даты. К примеру, при преобразовании даты 13.04.1987 выходит число 31880. Другими словами от 1.01.1900 прошло 31 880 дней.

Этот принцип лежит в базе расчетов временных данных. Чтоб отыскать количество дней меж 2-мя датами, довольно от наиболее позднего временного периода отнять наиболее ранешний.

Пример функции ДАТА

Построение значение даты, составляя его из отдельных элементов-чисел.

Синтаксис: год; месяц, денек.

Все аргументы неотклонимые. Их можно задать числами либо ссылками на ячейки с надлежащими числовыми данными: для года – от 1900 до 9999; для месяца – от 1 до 12; для денька – от 1 до 31.

Если для аргумента «Денек» задать большее число (чем количество дней в обозначенном месяце), то излишние деньки перейдут на последующий месяц. К примеру, указав для декабря 32 денька, получим в итоге 1 января.

Пример использования функции:

Зададим большее количество дней для июня:

Примеры использования в качестве аргументов ссылок на ячейки:

Функция РАЗНДАТ в Excel

Возвращает разницу меж 2-мя датами.

• исходная дата;
• конечная дата;
• код, обозначающий единицы подсчета (деньки, месяцы, годы и др.).

Методы измерения интервалов меж данными датами:

• для отображения результата в деньках – «d»;
• в месяцах – «m»;
• в годах – «y»;
• в месяцах без учета лет – «ym»;
• в деньках без учета месяцев и лет – «md»;
• в деньках без учета лет – «yd».

В неких версиях Excel при использовании крайних 2-ух аргументов («md», «yd») функция может выдать неверное значение. Лучше использовать другие формулы.

Примеры деяния функции РАЗНДАТ:

В версии Excel 2007 данной функции нет в справочнике, но она работает. Хотя результаты лучше инспектировать, т.к. вероятны огрехи.

Функция ГОД в Excel

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

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

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

Примеры использования функции МЕСЯЦ:

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

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

Чтоб возвратить порядковый номер денька недельки для обозначенной даты, можно применить функцию ДЕНЬНЕД:

По дефлоту функция считает воскресенье первым деньком недельки.

Для отображения порядкового номера недельки для обозначенной даты применяется функция НОМНЕДЕЛИ:

Дата 24.05.2015 приходится на 22 недельку в году. Неделька начинается с воскресенья (по дефлоту).

В качестве второго аргумента указана цифра 2. Потому формула считает, что неделька начинается с пн. (2-ой денек недельки).

Для указания текущей даты употребляется функция СЕГОДНЯ (не имеет аргументов). Чтоб показать текущее время и дату, применяется функция ТДАТА ().

Главные механизмы работы с датами и временем в Excel

Как обычно, кому нужно стремительно — смотрим видео. Подробности и аспекты — в тексте ниже:

Как вводить даты и время в Excel

Если иметь ввиду русские региональные опции, то Excel дозволяет вводить дату весьма различными методами — и осознает их все:

С внедрением дефисов

С внедрением дроби

Наружный вид (отображение) даты в ячейке быть может весьма различным (с годом либо без, месяц числом либо словом и т.д.) и задается через контекстное меню — правой клавишей мыши по ячейке и дальше Формат ячеек (Format Cells) :

Время вводится в ячейки с внедрением двоеточия. К примеру

По желанию можно добавочно уточнить количество секунд — вводя их также через двоеточие:

И, в конце концов, никто не воспрещает указывать дату и время сходу совместно через пробел, другими словами

27.10.2012 16:45

Резвый ввод дат и времени

Для ввода нынешней даты в текущую ячейку можно пользоваться сочетанием кнопок Ctrl + Ж (либо CTRL+SHIFT+4 если у вас иной системный язык по дефлоту).

Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правую клавишу мыши, то можно избрать — как конкретно копировать выделенную дату:

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

Если необходимо, чтоб в ячейке постоянно была животрепещущая нынешняя дата — лучше пользоваться функцией СЕГОДНЯ (TODAY) :

Как Excel по сути хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой клавишей по ячейке Формат ячеек — вкладка Число — Общий), то можно узреть увлекательную картину:

Другими словами, исходя из убеждений Excel, 27.10.2012 15:42 = 41209,65417

По сути всякую дату Excel хранит и обрабатывает конкретно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, толика от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

• Во-1-х, Excel не умеет работать (без доп опций) с датами ранее 1 января 1900 года. Но это мы переживем! 😉
• Во-2-х, с датами и временем в Excel может быть делать любые математические операции. Конкретно поэтому, что по сути они — числа! А вот это уже открывает перед юзером массу способностей.

Количество дней меж 2-мя датами

Считается обычным вычитанием — из конечной даты вычитаем исходную и переводим итог в Общий (General) числовой формат, чтоб показать разницу в деньках:

Количество рабочих дней меж 2-мя датами

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

Примечание: Эта функция возникла в обычном наборе функций Excel начиная с 2007 версии. В наиболее старых версиях поначалу нужно подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak) . Опосля этого в Мастере функций в группы Дата и время покажется нужная нам функция ЧИСТРАБДНИ (NETWORKDAYS) .

Количество полных лет, месяцев и дней меж датами. Возраст в годах. Стаж.

Про то, как это верно вычислять, лучше почитать здесь.

Сдвиг даты на данное количество дней

Так как одни день в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от данной на, допустим, 20 дней, довольно прибавить к дате это число.

Сдвиг даты на данное количество рабочих дней

Эту операцию производит функция РАБДЕНЬ (WORKDAY) . Она дозволяет вычислить дату, отстоящую вперед либо вспять относительно исходной даты на необходимое количество рабочих дней (с учетом выходных суббот и воскресений и муниципальных праздинков). Внедрение данной функции стопроцентно аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной чуть повыше.

Вычисление денька недельки

Вас не в пн родили? Нет? Убеждены? Можно просто проверить с помощью функции ДЕНЬНЕД (WEEKDAY) из группы Дата и время.

1-ый аргумент данной функции — ячейка с датой, 2-ой — тип отсчета дней недельки (самый удачный — 2).

Вычисление временных интервалов

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

Аспект тут лишь один. Если при сложении нескольких временных интервалов сумма вышла больше 24 часов, то Excel обнулит ее и начнет суммировать снова с нуля. Чтоб этого не происходило, необходимо применить к итоговой ячейке формат 37:30:55:

Ссылки по теме

Поглядите http://planetaexcel.ru/techniques/6/44/
Не оно?

Продублирую вопросик из форума — для функций РАБДЕНЬ и ЧИСТРАБДНИ можно ли:

1. ввести функцию сслыки на перечень праздничков
2. создать эту функцию доступной для хоть какого файла Excel?

Спасибо Для вас большущее Николай за весьма полезные и познавательные ролики которые Вы выкладываете

Просто огромное человеческое Спасибо.

Спасибо на хорошем слове, Анатолий! Будем стараться и далее вас веселить

Николай,
хорошего времени суток. Я на данный момент изучаю excel увлекательным методом: у меня есть товарищ, гуру эксель. =) он даёт задачки, а я их решаю. В решениии помогают Гугл и всякие книги. но есть вещи которых я просто не могу там отыскать. А всякий раз обращаться по «тупому» вопросцу с товарищу тоже неловко.
На данный момент я «встал» на таковой вот задачке.
Есть 3 ячейки. 1 ячейка исходная дата (неважно какая), 2 ячейка конечная дата (тоже неважно какая). В 3 ячейке нужно отдать сумму рабочих дней меж этими 2 датами. Но выходные деньки СРЕДА и СУББОТА либо лишь СРЕДА.
Я так понимаю, что это нужно создать через формулу массивов и функции счётесли. Но просто не понимаю, как можно из 2-ух дат сделать массив (двссыл здесь не работает).

весьма прошу посодействовать.

Чуток не запамятовал, для расчётов нужно применять лишь 1 ячейку.

Решение найдено
=SUM(IF(WEEKDAY(ROW(INDIRECT(A3&»:»&B3));2)<>3;1;0))
всеравно спасибо

Не за что

Хороший денек! Помогите, пожалуйста!

Необходимо вычислить кол-во часов меж 2-мя ячейками, формат время.

Если в 2х ячейках время АМ
А1=ВРЕМЯ(2;15;0)
В1=ВРЕМЯ(8;0;0)
то =В1-А1 вычисляет нормально = 5,45 часов

но если в одной яч. время РМ опосля пополудни, а в др. АМ до пополудни
А1=ВРЕМЯ(23;0;0)
В1=ВРЕМЯ(7;0;0)
то =В1-А1 = ################ — отрицательные либо очень огромные дата и время

почему так происходит и как поправить формулу, чтоб получить часы?

Марина, за 6 лет для вас так никто и не дал ответ, но я отыскал решение! Таковая же неувязка была.
Вот во всех этих ячейках, где ############# прописать формулу: =1-ABS(A1-B1) тогда будет реально рассчитываться сколько часов прошло.

PS Надеюсь ещё не позно

Александр, если в ячейке А1 стоит дата (к примеру 13.01.2014), а в ячейке А2 количество календарных дней (к примеру 14, предполагается что дата из ячейки А1 тоже учитывается), то формула перекидывающая выходной денек на предыдущую пятницу может смотреться так:

Хороший денек, помогите пожалуйста, составить формулу для последующего: имеется длинный лист с датами истечения договора: нужна формула чтоб от TODAY+5 представим сигнализировала к примеру конфигурацией цвета

Спасибо! А вы не могли бы разъяснить, пожалуйста, как её использовать? Поэтому что 1900 год, опосля подключения надстройки, он всё равно заявляет номером 1. Может быть, где то обязана была показаться доп функция, но я её не углядел
Спасибо!

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

first of all, thank you very much for your work! It helps A LOT!

one question regarding time format:if time es exported as text like the following

08090439

how can i convert into time format?

Thank you in advance!

Хорошего всем денька, подскажите пожалуйста в формулой по вычислению суммы пени по просрочке

Условие: есть дата оплаты план (П.Дата) и есть дата платы Факк (Ф.Дата), есть сумма на оплату например 10 000(Сумма) и коэф пени к примеру 0,01%(Пени)
Приблизительно вычисление обязано смотреться след образом : (П.Дата — Ф.Дата ) * Сумма * Пени , т.е.
26 авг — 30 авг= -4 ( Деньки просрочки)
4* 10 000*0,01%
Все бы отлично, но есть одно НО. если формулу протягивать на н-ое количество листов , то она считает все попорядку и просроченнные и не просроченные платежи. Т.к. разница дат выходит как с минусом — это и есть просрочка, так и с плюсом — эти платежи еще в перспективе.
Пробовал прикрутить условие «Если» , т.е. если деньки в разнице дат меньше нуля, то показывается значение с минусом, что фактически и обязано идти в расчет, а если деньки в разнице дат больше нуля то должен отображаться ноль и все перемножения коэфицентов будут равны нулю, что тоже есть верно. но при вставке данной формулы выходит ошибка , дескать различные еденицы измерения в расчетах. и в графе где по условию должны отображаться деньки со значением минус -отображается значение ячейки в которой все это прописано
Всем затык, осознать не могу, по логике вроде все правильно, а по факту не выходит, может у кого есть решение данного вопросца либо подобные приемы для решения?

Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel

Хороший денек почетаемый юзер!

В данной статье мы побеседуем о трёх главных функций дат, это функция ГОД, функция МЕСЯЦ и функция ДЕНЬ в Excel. Это три составляющие хоть какой даты и сейчас разглядим, как с ними работать и для что это нам нужно. Сначала, эти функции служат для извлечения определенного параметра из имеющейся даты, мы может раздельно изъять год, числовую последовательность месяца либо денька. Внедрение этих функций будет животрепещуще когда, например, необходимо будет поделить ваши данные по временным отрезкам по годам, по месяцам либо по денькам. Нередко внедрение этих функций оправдано при работе со статистическими данными, когда их применение делают лучше характеристики анализа.

Сейчас давайте разглядим каждую из функций времени по отдельности:

Функция ГОД в Excel

Основная обязанность, которую делает функция ГОД в Excel, является возврат года, который будет соответствовать обозначенной дате. Сам год будет определять функция ГОД как целое число в спектре от 1900 года до 9999 года.

Синтаксис, который употребляет функция ГОД в Excel весьма обычной: =ГОД(указаная_дата_в_числовом_формате), где:

• Дата_в_числовом_формате, является неотклонимым аргументом и представляет собой дату, год которой следует вычислить. Даты необходимо вводить с помощью функции ДАТА, указывать ссылкой на ячейку, которая содержит дату в соответственном формате либо это будет итог отдельных расчётов других функций и формул. Принципиально!В случае, когда дата будет введена как текст, это приведёт к ошибкам и дилеммам.

Стоит знать что значения, которое возвращает функция ГОД в Excel стопроцентно, ведется по григорианскому календарю, без различия какой формат для даты был указан. Даже если вы указываете дату по другому летосчислению либо другому календарю, значение которое функция ГОД возвратит, будет соответствовать дате григорианского календаря.

Все даты MS Excel хранит как поочередные числа и конкретно это дозволяет нам работать с ними и применять в собственных вычислениях. По дефлоту 1-ая дата под номером 1, это 1 января 1900 года, а вот, например, 1 января 2018 года будет предоставлен числом 43101, так разница меж этими 2-мя датами будет составлять 43101 денек.

Функция МЕСЯЦ в Excel

Основная изюминка, как употребляется функция МЕСЯЦ в Excel, это возврат значения месяца из даты, который данный как числовой формат. Месяц будет возвращен функцией как целое число в спектре чисел от 1 до 12, что соответствует месяцам от января до декабря.

Синтаксис, который употребляет функция МЕСЯЦ в Excel, обычной и незатейливый: =МЕСЯЦ(ваша_дата_в_числовом_формате), где:

• Дата_в_числовом_формате, является неотклонимым аргументом и представляет собой дату, месяц которой следует вычислить. Даты необходимо вводить с помощью функции ДАТА, указывать ссылкой на ячейку, которая содержит дату в соответственном формате либо это будет итог отдельных расчётов других функций и формул. Принципиально!В случае, когда дата будет введена как текст, это приведёт к ошибкам и дилеммам.

Индивидуальности, как употребляется функция МЕСЯЦ в Excel, соответствуют аналогично предшествующей рассматриваемой функции, потому что формат дат распространяется на все функции времени.

Функция ДЕНЬ в Excel

Основная обязанность, которую делает функция ДЕНЬ в Excel, является возврат число денька, которое будет в обозначенной дате. Денек будет возвращен функцией как целое число в спектре чисел от 1 до 31, что зависит от наибольшего количества дней в месяце.

Синтаксис, который употребляет функция ДЕНЬ в Excel, так же не отличаеться множеством аргументов: =ДЕНЬ(ваша_дата_в_числовом_формате), где:

• Дата_в_числовом_формате, является неотклонимым аргументом и представляет собой дату, денек которой следует вычислить. Даты необходимо вводить с помощью функции ДАТА, указывать ссылкой на ячейку, которая содержит дату в соответственном формате либо это будет итог отдельных расчётов других функций и формул. Принципиально!В случае, когда дата будет введена как текст, это приведёт к ошибкам и дилеммам.

Как и прошлые функции, функция ДЕНЬ в Excel хранит даты как поочередные числа и значения которые она возвращает, соответствуют датам григорианского календаря.

Раздельно для каждой функции я решил не создавать примеры их использования, так как они весьма идентичны в нраве работы, рознятся лишь приобретенные результаты, разницу вы сможете изучить на снимке экрана: Также, для примера, сможете изучить варианты работы этих функций в моей статье «Как посчитать количество дней в Excel».

Ну, вот и всё что я, пока, желал для вас поведать о таковых функциях работы с датами как функция ГОД, функция МЕСЯЦ и функция ДЕНЬ. Весьма надеюсь, что моя статья для вас посодействовала в решении вашей трудности. Жду ваши лайки и комменты, делитесь статьей с друзьями и сотрудниками, пусть нас будет много!

«Я желаю жить как бедный человек с средствами.
»
Пабло Пикассо