Основные принципы работы с датами и временем в Excel

Как обычно, кому надо быстро - смотрим видео. Подробности и нюансы - в тексте ниже:

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

С использованием дефисов

С использованием дроби

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

Время вводится в ячейки с использованием двоеточия. Например

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

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

27.10.2012 16:45

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

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

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

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

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

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Спасибо Вам огромное Николай за очень полезные и познавательные ролики которые Вы выкладываете\

Просто большое человеческое Спасибо.

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

очень прошу помочь.

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

Добрый день! Помогите, пожалуйста!

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

Если в 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 предположим сигнализировала например изменением цвета

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 following08090439how can i convert into time format?

Thank you in advance!

Доброго всем дня, подскажите пожалуйста в формулой по вычислению суммы пени по просрочке

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

Доброго времени суток.Огромное спасибо Николаю, за этот замечательный сайт. Недавно начал работать с экселем и кучу ответов на вопросы нашёл тут. СПАСИБО. Но появилась проблема ответа на которую пока не вижу.Есть ячейки в которых указано время. Нужно сравнивать это время с текущим системным временем. Если от текущего времени до указанного в ячейке осталось 10 минут, то она станет желтой, если совпадает, то зелёной, на если текущее время больше того, что указано в ячейке - то красным.Есть способы это реализовать?

Заранее всем спасибо.

Помогите, пожалуйста, не проиграть Excelю и решить вот такую задачу:

Есть таблица из трёх столбцов:Идентификатор клиента I Дата регистрации клиента в программе I Платежи за ПЕРИОД

Внимание! Период - это календарная неделя, например, с 27 июля по 2 августа 2015 г..

Мне необходимо определить "срок жизни" клиента в программе.Например, если он зарегистрировался 28 июля 2015 г., то вывести "На этой неделе".Если дата регистрации попадает в период с 20 по 26 июля 2015 г., то вывести "Неделю назад".Аналогично "2 недели назад", "3 недели назад", "Месяц назад", "2 месяца назад", "3 месяца назад", . , "Год назад".

Даты регистрации в программе аж с 2008 года.

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