Обработка значений дат в программе excel

Расчет разности дат

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

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

1. Для начала выделяем целевой диапазон ячеек, воспользовавшись зажатой левой кнопкой мыши.
2. Щелкаем по выделенной области правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Формат ячеек“. Аналогичного результата можно добиться, используя комбинацию клавиш Ctrl+1.
3. Перед нами окно настроек формата ячеек:
   • по умолчанию мы должны оказаться во вкладке “Число” (если это не так, переключаемся в нее);
   • в списке “Числовые форматы” находим позицию “Дата” и кликаем на нее;
   • в поле “Тип” выбираем нужный образец представления данных, в соответствии с которым они будут отображаться на листе;
   • нажимаем OK.
4. Теперь все значения, вводимые в ячейки выделенной области будут восприниматься программой как данные в формате “Дата“.

Растягивание формулы для подсчета возраста

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

Однако отображение одной и той же даты на целый столбец как минимум портит эстетический вид таблицы, а как максимум – не всегда является возможным. Поэтому лучше закрепить ячейку, что осуществляется так:

1. Выберите поле с формулой расчета возраста и добавьте между буквой и цифрой ячейки конечной даты знаки $, которые обозначают константу. Так вы закрепите всю ячейку, а не только столбец или ряд. В итоге номер будет выглядеть, например, как $A$1.

2. Затем зажмите левую кнопку мыши у правой границы ячейки и потяните ее на всю длину таблицы до конца перечисления людей.

3. Убедитесь в том, что все возрасты отображаются корректно. Если нет, еще раз проверьте то, правильно ли вы закрепили ячейку.

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

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

Функция РАЗНДАТ – разность двух дат в днях, месяцах и годах

Предположим, вы работаете в пенсионном фонде и хотите определить трудовой стаж в количестве лет, дней, месяцев и так далее. Это можно сделать с помощью функции РАЗНДАТ.

Ее синтаксис элементарный.

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

Единственный из этих аргументов, который действительно может вызвать вопросы у неподготовленного читателя – это способ измерения. С его помощью мы задаем, в каких единицах будет измеряться разница между датами. Это может быть количество месяцев, лет или количество месяцев без учета лет. Еще один вариант – это количество дней без учета месяцев и лет. 

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

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

Пример №1. Подсчет количества лет между двумя датами

На примере выше, формула возвращает количество лет между двумя датами. Например, функция вернет значение “27” если вы захотите посчитать количество полных лет между двумя датами 19 июня 1989 года и 26 марта 2017 года. Система подсчитывает количество полных лет и игнорирует количество месяцев и дней между датами.

Разница дат в полных месяцах

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

То есть, формула следующая.

=РАЗНДАТ(A2;B2;”m”)

Эта формула также может давать несколько неточные данные. Поэтому всегда нужно перепроверять значения самостоятельно. 

Разница в днях без учета лет

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

1

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

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

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

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

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

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

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

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

Вычисление дня недели

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

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

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

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

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

Пример 1

1. Чтобы выяснить, сколько проработал в компании тот или иной служащий, нужно из текущей даты вычесть дату поступления его на работу. Если в рабочей таблице содержатся даты приема на работу, в нее целесообразно добавить сегодняшнее число. (Самый простой способ введения текущей даты — с помощью комбинации клавиш CTRL SHIFT;).

2. Активизируйте ячейку, в которую будет заноситься трудовой стаж данного служащего.

3. Теперь из текущей даты нужно вычесть дату приема на работу. В рассматриваемом примере сначала можно попробовать воспользоваться формулой =$В$2-C5

Обратите внимание на результат! Получилось такое большое число, потому что формула вычисляет количество дней (а не лет) работы на предприятии

4. Разделив результаты на 365, получим ответ в годах. В нашем случае формула должна иметь вид =($В$2-C5)/365. Теперь легко заметить, что первый сотрудник проработал на предприятии более 22 лет.

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

Вычисление возраста или стажа функцией РАЗНДАТ (DATEDIF)

​ функций и даже​Т.е. при желании подсчитать​: У меня в​​ как числовое значение,​​ «;E2;» дня(-ей)»)​ посчитать в Excel:​​Преобразует дату в числовом​​Как быть? Вероятно, решение​

​ для расчета страхового​РАЗНДАТ(A2;B2;»ym»)&» мес. «&​Правка​щелкните поле​Этап 2.​​ и 01.08.2012 но​​(Analisys Toolpak)​ интересную картинку:​ понимает их все:​ на панели «ФОРМУЛЫ».​ и вывести, например,​ задании предполагается использование​ так и быть​Пример 3. В документе​ возраст от даты​ формате в минуты.​ будет зависеть от​ стажа для определения размера​ЕСЛИ(ДЕНЬ(A2)>ДЕНЬ(B2);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B2;-1);0))-ДЕНЬ(A2)+ДЕНЬ(B2);ДЕНЬ(B2)-ДЕНЬ(A2))&» дн.»​выберите пункты​Кон_дата​03.06.2012​ по формуле какой​​. После этого в​То есть, с точки​​»Классическая» форма​ Ее всегда нужно​ ваш стаж в​ функции ДНЕЙ360(), можете​

​ результатом какого-либо вычисления,​

​ записаны даты заключения​

​ рождения, трудовой стаж,​МЕСЯЦ​ конкретной задачи, стоящей​ пособий по временной​В случае, если получается​Очистить​и выберите ячейку​10.12.2014​ то….​ Мастере функций в​ зрения Excel, 27.10.2012​3.10.2006​ вводить вручную. Первым​ виде «3 г.​ на примере неё​

​ производимого вне ячеек​	​ договоров сотрудников компании.​
​ сроки беременности и​	​Преобразует дату в числовом​
​ перед пользователем. Необходимо​	​ нетрудоспособности (об этом​
​ целое количество лет,​	​и​ C2 на листе​Этап 3.​
​Skv​	​ категории​ 15:42 = 41209,65417​Сокращенная форма​
​ аргументом функции всегда​	​ 4 мес. 12​ решить, а то​

​ (например, функции ДАТА(),​

​ По умолчанию договор​ др. Примеры расчетов​ формате в месяцы.​ помнить, что нужно​ см. статью Расчет страхового​ то формула вернет​Все​ (20.09.2011).​

​11.12.2014​: Моё видение решения​

​Дата и время​На самом деле любую​3.10.06​ должна быть новейшая​ дн.», необходимо ввести​

​ что то не​ функции СЕГОДНЯ()).​

​ заключается на 1​ точных сроков, возрастов​

planetaexcel.ru>

Создание диапазона дат с двух дат

Чтобы отобразить диапазон дат в одной ячейке на основе дат в разных ячейках, вы можете использовать формулу, основанную на функции ТЕКСТ.

В показанном примере формула в ячейке E5:

Функция ТЕКСТ принимает числовые значения и преобразует их в текстовые значения, используя заданный вами формат. В этом примере мы используем формат «ммм д» для обеих функций TEКСT в E5. Результаты объединяются дефисом, используя простую конкатенацию.

Примечание. Другие примеры в столбце E используют разные текстовые форматы.

Если дата окончания отсутствует, формула не будет работать правильно, потому что дефис будет добавлен к дате начала (например, «1 марта»).

Чтобы обработать этот случай, вы можете обернуть конкатенацию и вторую функцию ТЕКСТ внутри ЕСЛИ следующим образом:

Это создает полный диапазон дат, когда обе даты присутствуют, но выводит только дату начала, когда дата окончания отсутствует.

Для обработки случая, когда обе даты отсутствуют, вы можете вложить еще один ЕСЛИ следующим образом:

Эта формула просто возвращает пустую строку («»), когда дата1 недоступна.

2 популярных способа сравнения 2 дат в Excel

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

Первая дата больше второй, или нет

В этом случае нужно использовать аналогичную формулу, которая используется для проверки чисел: =A1>A2. Если эта проверка подтверждается, то в ячейку, куда была записана эта формула, возвращается значение “ИСТИНА”. Если же первое число меньше второго, то тогда будет записано “ЛОЖЬ”. Аналогично, только зеркально противоположно с формулой =A1

Функция ЕСЛИ, значение ИСТИНА или ЛОЖЬ

Это уже более сложный вариант, который дает возможность не только говорить, действительно ли одна дата больше другой, но и указывать, на какое количество дней. Чтобы это сделать, необходимо нажать по ячейке и записать туда формулу =ЕСЛИ(A2>B2;»Первая дата больше второй на»&» «&A2-B2&» «&»дней»;»Первая дата меньше второй на»&» «&B2-A2&» «&»дней»)

1

Здесь мы осуществляем проверку двух дат, и исходя из того, что у нас получилось в итоге, выводим результат, на сколько дней первая дата больше, чем вторая. Чтобы это сделать, нами был использовал оператор &, который объединяет две строки текста между собой и объединенный результат возвращает в ячейку. Далее получившийся текст можно использовать сам по себе или в других формулах.

Разница между датами в Excel вычисляется функцией РАЗНДАТ

​ЧИСТРАБДНИ​ использовать  ДАТАМЕС() при добавлении (вычитании)​ (трудового) стажа в​ результат в виде​.​Не заполняйте поле​20.04.2017​ данного вопроса:​появится необходимая нам​ дату Excel хранит​С использованием дефисов​ дата, а вторым​ в ячейку следующую​ получается!)​

Как посчитать разницу между датами в Excel

​Примечание: аргумент единица может​ год. Требуется выяснить,​ и продолжительности до​Возвращает количество полных рабочих​ месяцев, а РАЗНДАТ()​ MS EXCEL).​

1. ​ 37 г. 0​В ячейке D3 введите​Праздники​
2. ​Нажмите клавиши​Ooo ooo​ функция​
3. ​ и обрабатывает именно​3-10-6​ всегда – старшая​ формулу:​AleksSid​ принимать одно из​
4. ​ сколько дней осталось​ окончания периодов времени​ дней между двумя​ — это недокументированная​Теперь решим обратную задачу:​ мес. 0 дн.,​

​=(ГОД(C3)-ГОД(B3))*12+МЕСЯЦ(C3)-МЕСЯЦ(B3)​.​+C.​

​: ну если даты​ЧИСТРАБДНИ​ так — как​С использованием дроби​ дата. Третий аргумент​=РАЗНДАТ(A1;A2;»y»)&» г. «&РАЗНДАТ(A1;A2;»ym»)&» мес.​: Код =ЦЕЛОЕ(ДНЕЙ360(A1;B1;0)/360*12)​ шести значений:​

​ до момента окончания​ с помощью функции​ датами.​ и, соответственно, не​

Формула разница дат в Excel

​ что не очень​, а затем нажмите​Нажмите клавишу RETURN.​Выделите на листе ячейку​ написаны в формате​(NETWORKDAYS)​ число с целой​3/10/6​

​ функции определяет единицу​ «&РАЗНДАТ(A1;A2;»md»)&» дн.»​Czeslav​— «Y»: в результате​ трудового договора у​ РАЗНДАТ. Как правильно​ЧИСТРАБДНИ.МЕЖД​ гарантирующая правильность расчетов​ лет, месяцев и​ красиво.​ клавишу RETURN.​Результатом является 75 рабочих дней​ A1, а затем​ дд. мм. гггг,​.​ и дробной частью.​Внешний вид (отображение) даты​ измерения количества, которое​где А1 — ячейка​: Код =DATEDIF(A8;B8;»m»)​

​ подсчетов будет выведено​ каждого из сотрудников,​ считать разницу между​Возвращает количество полных рабочих​ функция. Об этой​ дней, рассчитаем конечную​Воспользовавшись идеями, подсказанными нашим​Результат — 9.​ (без учета праздников).​ нажмите клавиши​ то просто выделяешь​Про то, как это​ Целая часть числа​ в ячейке может​

​ возвращает функция =РАЗНДАТ().​ с датой поступления​Для вычислений длительностей интервалов​ количество полных лет​ чтобы подготовить новые​ двумя датами при​ дней в интервале​ функции написана статья Функция​ дату.​ пользователем МСН, также​Вычисление количества лет между​Выберите ячейку D2.​+V.​ (удерживая левую кнопку​ правильно вычислять, лучше​ (41209) — это​ быть очень разным​ В данном случае​ на работу, А2​ дат в Excel​ между начальными аргументами;​ экземпляры для продления​ 365 и 366​

​ между двумя датами,​ РАЗНДАТ() — Вычисление​Конечную дату будем рассчитывать​ просклоняем месяцев/ месяца​ двумя датами​Перетащите маркер заполнения​

​Вычисление разницы в днях​ мыши) , и​ почитать тут.​ количество дней, прошедших​ (с годом или​ это символ «d»​ — с датой​ есть функция​— “M”: в результате​ договоров.​ днях в году?​ руководствуясь параметрами, указывающими​ разности двух дат​

exceltable.com>

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

Итак, в Excel есть две функции для вычисления кол-ва дней.

С помощью функции ДНИ

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

Итак, вы указываете эти две даты в аргументах функции и получаете количество дней между ними.

Хороший пример вы можете видеть на картинке ниже:

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

Используем эту формулу:

Также вы можете вручную прописать даты в функции (без указания ячеек), но тогда вам нужно заключить их в кавычки.

С помощью функции РАЗНДАТ

Эта функция аналогична, её отличие в том, что можно указать больше параметров. То есть больше «подстроить» под ситуацию.

Также можно вычислить кол-во месяцев или лет между датами.

Допустим, мы имеем то что указано на картинке ниже:

Пропишем формулу:

Эта функция использует три наших аргумента:

• Дата начала — B1
• Дата окончания — B2
• «D» — текстовая строка

Важный момент: этой функции не будет в подсказке при написании названия функции, т.е. если вы будете писать РАЗНДАТ, Excel будет вести себя так, как будто он не знает что это за функция, но это не так.

Функция РАЗНДАТ больше подходит для ситуаций, когда нужно вычислить кол-во лет или месяцев между датами, в других ситуациях удобнее применять ДНИ (функцию).

Вот формула, которая даст результат по месяцам, которые будут между датами:

А эта формула, тоже самое что и прошлая, только вычисляет кол-во лет:

Как в Excel хранится время

Excel сохраняет время, используя тот же формат порядковой нумерации, что и для дат. День у Excel «начинается», как и у нас с вами в полночь. Каждый час составляет 1/24 дня, поэтому он представлен в виде десятичного значения: 0,041666…

Если Вы хотите узнать, что «видит» Excel, когда вы записываете в ячейку 9:00 утра, то умножьте: 0,041666 на 9.

Одна минута в Excel представлена числом: 1 / (24 * 60) = 1/1440 = 0,000694.

Секунда составляет 1 /(24 * 60 * 60 )= 1/86400 = 0,00001157407…

Поэтому если вы запишете в ячейку 8:30 утра, Excel совершит следующий расчет:

(8 * (1/24)) + (30 * (1/1440)) = 0,354167

Так что, если вы опаздываете на работу на полчаса и хотите сообщить об этом своему боссу, напишите ему / ей и скажите, что будете на месте через 0,02083333….

Если время указано без даты, например, 9:00, Excel сохраняет его, как 9:00 0 января 1900 года. Это может затруднить выполнение математических расчетов для значений времени (без даты), поскольку вычитание 6:00 из 3:00 станет отрицательным и будет считаться ошибкой, так как  0,125 – 0,25 = -0,125 , что будет отображаться как #########.

Если же время указано вместе с датой, то проблем не возникнет. Так 11:00 20 августа 2020 года будет сохранено как 44063,46.

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

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

Функция РАБДЕНЬ пишется так:

=РАБДЕНЬ(1;2;3)

1 – начальная дата, ОТ КОТОРОЙ ПОЙДЕТ ОТЧЕТ. Эта дата в число добавляемых дней НЕ ВХОДИТ!

2 – количество рабочих дней. Функция найдет дату последнего дня из указанного количества

3 – праздники. Показывают в виде диапазона, прописывают в виде массива в фигурных скобках. Если в рабочем периоде праздников нет, то не указывают. Либо указывают на диапазон с ними.

Для функции РАБДЕНЬ.МЕЖД используют такую запись:

= РАБДЕНЬ.МЕЖД(1;2;3;4)

1 – начальная дата, ОТ КОТОРОЙ ПОЙДЕТ ОТЧЕТ. Эта дата в число добавляемых дней НЕ ВХОДИТ!

2 – количество рабочих дней, функция покажет дату последнего из них.

3 – выходные дни. Excel предлагает на выбор числовые коды для выходных, но можно поступить проще. Надо указать дни недели в виде строки из единиц и нулей. Первый знак будет относиться к первому дню недели (у нас это понедельник, у кого-то воскресенье). Единицами указывается выходные дни. Например, если для стандартных выходных в субботу и воскресенье используется запись – «0000011». Не забываем про кавычки!

4 – праздничные дни

 Разберем такую пример. На выполнение работы по ремонту требуется согласно нормативу 15 рабочих дней. Какого числа работы должны быть закончены в Казахстане, если они начаты 22 апреля 2020 года при условии стандартных выходных или выходных по понедельникам. Праздничные дни Казахстана в мае 2020 года – 1 мая, 7 мая, 9 мая. Пи этом так как 9 мая выпало на субботу, поэтому согласно правилам и постановлению день отдыха перенесен на 8 мая.

Решения задачи следующее

Рисунок 4

Обратите внимание, что в функции РАБДЕНЬ.МЕЖД можно вообще убрать выходные, оставив только праздники и указов для параметра «выходные дни» строку их 7 нулей. В функции РАБДЕНЬ это невозможно, в ней выходные дни используются автоматически

И еще. Результатом работы обеих функций будет значение в числовом виде. Для преобразования их в формат дат воспользуйтесь форматом даты или примените к результату функцию ТЕКСТ, указав сначала полученное значение, а затем в кавычках нужный формат. Если нужно, чтобы дата выглядела как «число.номер месяца.номер года из 4 цифр», то есть вот так – 15.05.2020  -, тогда формат будет таким «ДД.ММ.ГГГГ».

Рисунок 5

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

Рисунок 6

 В качестве варианта используется функция ДАТА. Вначале мы извлекаем из начальной даты ДЕНЬ, МЕСЯЦ и ГОД соответствующими функциями. Затем к нужному промежутку добавляем требуемое количество. И наконец, функцией ДАТА превращаем это все в дату. В функции ДАТА вначале указываем ГОД, затем МЕСЯЦ и последним ДЕНЬ.

Все вместе выглядит так:

Рисунок 7

Обратите внимание, что для этих функций преобразование в формат даты не требуется. Функцию ДАТА применяют и для нахождения даты через заданное количество недель

Надо только это количество умножить на 7, а результат прибавить к значению функции ДЕНЬ.

https://i.postimg.cc/4xxFrFKS/image9.jpg

Рисунок 8

Как СУММИРОВАТЬ время в Excel

Иногда вам может потребоваться быстро сложить все временные значения в Excel. Добавить несколько значений времени в Excel довольно просто (все, что нужно, — это простая формула SUM).

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

Давайте посмотрим на пример.

Ниже у меня есть список задач вместе со временем, которое займет каждая задача, в столбце B, и я хочу быстро добавить это время и узнать общее время, которое потребуется для всех этих задач.

В ячейке B9 я использовал простую формулу СУММ, чтобы вычислить общее время, которое будут занимать все эти задачи, и оно дает мне значение 18:30 (что означает, что на выполнение потребуется 18 часов 20 минут. все эти задачи)

Пока все хорошо!

Как СУММИРОВАТЬ за 24 часа в Excel

Теперь посмотрим, что происходит, когда я меняю время выполнения задачи 2 с 1 часа до 10 часов.

Теперь результат показывает 03:20, что означает, что для выполнения всех этих задач потребуется 3 часа 20 минут.

Это неверно (очевидно)

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

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

К счастью, это легко исправить.

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

Ниже приведены некоторые форматы, которые вы можете использовать:

Формат	ожидаемый результат
[Хм	28:30
: сс	1710:00
d «D» чч: мм	1 Д 04:30
d «D» hh «Min» ss «Sec»	1 Д 04 мин 00 сек
d «День» hh «Минута» ss «Секунды»	1 день 04 минута 00 секунды

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

Вы можете использовать приведенную ниже формулу ТЕКСТ, чтобы показать время, даже если оно превышает 24 часа:

или формулу ниже, если вы хотите преобразовать часы, превышающие 24 часа, в дни:

Как рассчитать и отобразить отрицательное время в Excel

При расчете разницы во времени в Excel вы иногда можете получить результат в виде ошибки ######, если вдруг она представляет собой отрицательное число. Это происходит, если время начала больше, чем время окончания события. Такое часто случается, если рассчитывают продолжительность каких-то событий без учета даты. Получается, что время окончания меньше, чем время начала, поскольку это просто время следующего дня. К примеру, мы начинаем работу в 17 часов и заканчиваем на следующий день в 10 часов.

Но есть ли способ правильно отображать отрицательное время в Excel? Конечно, способ есть, и даже не один.

Способ 1. Измените систему дат Excel на систему дат 1904 года.

Самый быстрый и простой способ нормально отображать отрицательное время (со знаком минус) — это перейти на систему дат 1904 года. Для этого нажмите « Файл» > «Параметры» > «Дополнительно », прокрутите вниз до раздела « При расчете этой книги» и поставьте галочку в поле «Использовать систему дат 1904».

Нажмите  , чтобы сохранить новые настройки, и теперь отрицательные величины времени будут отображаться правильно, как отрицательные числа:

Как видите, время начала больше, чем время окончания, и результат получен со знаком «минус».

Способ 2. Рассчитать отрицательное время в Excel с помощью формул

Если изменить систему дат по умолчанию в Excel нецелесообразно, вы можете заставить отрицательное время отображаться правильно, используя одну из следующих формул:

=ЕСЛИ(A2-B2>0; A2-B2; «-» & ТЕКСТ(ABS(A2-B2);»ч:мм»))

=ЕСЛИ(A2-B2>0; A2-B2; ТЕКСТ(ABS(A2-B2);»-ч:мм»))

Они обе проверяют, является ли отрицательной разница во времени (A2-B2). Если она меньше нуля, первая формула вычисляет абсолютное значение (без учёта знака) и объединяет этот результат со знаком «минус». Вторая формула делает точно такой же расчет, но использует отрицательный формат времени «-ч::мм «.

Примечание. Имейте в виду, что в отличие от первого метода, который обрабатывает отрицательное время как отрицательное число, результатом функции ТЕКСТ всегда является текстовая строка, которую нельзя использовать в математических вычислениях.

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

На случай если время окончания меньше, чем время начала, мы можем проверить это при помощи функции ЕСЛИ.

=ЕСЛИ(B2<A2;B2+1;B2)-A2

Если разница во времени отрицательная, ко времени окончания добавляем 1 день (24 часа).  В результате получим правильную разницу во времени, но без знака «минус».

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

Таблицы Excel хранят даты, которые представлены в качестве последовательности числовых значений. Начинается она с 1 января 1900 года. Этой дате будет соответствовать число 1. При этом 1 января 2009 года заложено в таблицах, как число 39813. Именно такое количество дней между двумя обозначенными датами.

Функция ГОД используется аналогично смежным:

• МЕСЯЦ;
• ДЕНЬ;
• ДЕНЬНЕД.

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

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

=ГОД(адрес ячейки с датой в числовом формате)

Аргумент функции является обязательным для заполнения. Он может быть заменен на «дата_в_числовом_формате». В примерах ниже, вы сможете наглядно увидеть это

Важно помнить, что при отображении даты в качестве текста (автоматическая ориентация по левому краю ячейки), функция ГОД не будет выполнена. Ее результатом станет отображение #ЗНАЧ

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

Рассмотрим пример работы с функцией ГОД в Excel. Если нам нужно получить год из исходной даты нам не поможет функция ПРАВСИМВ так как она не работает с датами, а только лишь текстовыми и числовыми значениями. Чтобы отделить год, месяц или день от полной даты для этого в Excel предусмотрены функции для работы с датами.

Пример: Есть таблица с перечнем дат и в каждой из них необходимо отделить значение только года.

Введем исходные данные в Excel.

Для решения поставленной задачи, необходимо в ячейки столбца B ввести формулу:

=ГОД (адрес ячейки, из даты которой нужно вычленить значение года)

В результате мы извлекаем года из каждой даты.

Аналогичный пример работы функции МЕСЯЦ в Excel:

Пример работы c функциями ДЕНЬ и ДЕНЬНЕД. Функция ДЕНЬ получает вычислить из даты число любого дня:

Функция ДЕНЬНЕД возвращает номер дня недели (1-понедельник, 2-второник… и т.д.) для любой даты:

Во втором опциональном аргументе функции ДЕНЬНЕД следует указать число 2 для нашего формата отсчета дня недели (с понедельника-1 по восркесенье-7):

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

Создадим формулу из комбинаций функций ИНДЕКС и ДЕНЬНЕД:

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