Сложение времени в Microsoft Excel. Учет времени в Excel: суммирование вычитание разницы часов и минут

Содержание

Трюк №38. Трюки с возможностями даты и времени в Excel

Сложение времени в Microsoft Excel. Учет времени в Excel: суммирование вычитание разницы часов и минут

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

По умолчанию в Excel используется система дат 1900.

Это означает, что числовое значение, лежащее в основе даты 1 января 1900 года, равно 1, у 2 января 1900 года — 2 и так далее. В Excel эти значения называются последовательными значениями, позволяющими использовать даты в вычислениях. Формат времени очень похож, но Excel обрабатывает время как десятичные доли, где 1 — это 24:00, или 00:00.

Числовое значение 18:00 (в русской версии) равно 0,75, так как это три четверти от 24 часов.

Чтобы узнать числовое значение даты или времени, отформатируйте ячейку, содержащую значение, форматом Общий (General). Например, у даты и времени (в английской версии) 3/July/2002 3:00:00 РМ числовое значение 37440.625, где число после десятичной точки представляет время, а 37440 — последовательное значение для 3 июля 2002 года.

Сложение за пределами 24 часов

При помощи функции СУММ (SUM) или просто знака плюс время можно складывать. Таким образом, =SUM(A1:A5) даст нам общее количество часов в диапазоне А1 :А5, если эти ячейки содержат допустимые значения времени. Однако если Excel не дано никаких специальных указаний, он не складывает время за пределами 24 часов.

Это происходит потому, что, когда значение времени превышает 24 часа (настоящее значение равно 1), оно переходит в новый день и отсчет начинается заново. Чтобы заставить Excel не переходить в новый день после каждых 24 часов, можно использовать формат ячеек 37:30:55 или пользовательский формат [ч]:MM:cc([h]:mm:ss).

Схожий формат можно применять для получения общего количества минут или секунд, Чтобы узнать полное количество минут, когда время равно 24:00, отформатируйте ячейку как [м] ([m]), и вы получите 1440. Чтобы получить общее количество секунд, используйте пользовательский формат [с] ([s]) и вы получите 86400.

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

Если вы хотите использовать фактические значения времени в других вычислениях, помните о следующих «магических» числах: 60 — 60 минут или 60 секунд; 3600 — 60 секунд * 60 минут; 24 — 24 часа; 1440 — 60 минут * 24 часа; 86400 — 24 часа * 60 минут * 60 секунд.

Вооружившись этими магическими числами и предыдущей информацией, намного проще манипулировать временем и датами. Взглянем на следующие примеры и посмотрим, что они обозначают (предполагаем, что время записано в ячейке А1). Если у вас есть число 5.50 и вы хотите получить 5:30 или 5:30 a.m., используйте следующую формулу: =А1/24. Необходимо указать подходящий формат!

Если время должно выглядеть как 17:30 или 5:30 p.m., используйте следующую формулу: =(А1/24)+0.5.

Чтобы получить противоположное значение, то есть десятичное время из настоящего времени, воспользуйтесь формулой =А1*24.

Если в ячейке содержится настоящая дата и настоящее время (например, 22/Jan/03 15:36), а вы хотите получить только дату, используйте следующую формулу: =INT(A1), в русской версии Excel =ЦЕЛОЕ(А1).

Чтобы получить только время: =A1-INT(A1), в русской версии Excel =А1-ЦЕЛОЕ(А1) или =MOO(A1;1), в русской версии Excel =OCTAT(A1;1). И вновь необходим подходящий формат.

Чтобы найти разность между двумя датами, воспользуйтесь формулой =DATEDIF(A1;A2;”d”), где А1 — это более ранняя дата. Получим количество дней между двумя датами. В качестве результата можно также указать «m» или «у», то есть месяцы или годы. (В действительности функция DATEDIF в Excel 97 не документирована и является функцией Lotus 123.)

Если более ранние дата или время неизвестны, помогут функции МИН (MIN) и МАКС (МАХ). Например, чтобы наверняка получить правильный результат, можно воспользоваться такой функцией: =DATEDIF(MIN(Al;A2); MAX(Al,A2),”d”), в русской версии Excel: =DATEDIF(MИН(Al;A2);MAKC(A1;A2);”d”).

При работе со временем может также понадобиться учитывать начальное и конечное время. Например, начальное время — это 8:50 p.m. в ячейке А1, а конечное время — 9:50 a.m. в ячейке А2.

Если вы вычтете начальное время из конечного (=А2-А1), получите в ответе ######, так как Excel по умолчанию не работает с отрицательными значениями времени.

Подробнее о том, как работать с отрицательными значениями времени, — в разделе «Трюк № 74. Отображение отрицательных значений времени».

Иначе это ограничение можно обойти двумя способами, гарантировав положительный результат: =MAX(A1;A2)-MIN(A1;A2), в русской версии Excel =МАКС(А1;А2)-МИН(А1;А2) или =A1-A2+IF(A1>A2,1), в русской версии Excel =А1-А2+ЕСЛИ(А1>А2;1).

Можно также приказать Excel прибавить любое количество дней, месяцев или лет к любой дате: =DATE(YEAR(A1)+value1;MONTH(Al)+value2;DAY(Al)+value3), в русской версии Excel =ДАТА(ГОД(А1)+value1;,МЕСЯЦ(А1)+value2;ДЕНЬ(А1)+value3).

Чтобы добавить один месяц к дате в ячейке А1, воспользуйтесь формулой =DATE(YEAR(A1);MONTH(A1)+1;DAY(AD), в русской версии Excel =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;ДЕНЬ(А1)).

В Excel реализовано и несколько дополнительных функций, являющихся частью надстройки Analysis ToolPak.

Выберите команду Файл → Надстройки (File → Add-Ins) и установите флажок Пакет анализа (Analysis ToolPak) и, если появится сообщение с вопросом, нужно ли установить эту надстройку, ответьте согласием.

Станут доступны дополнительные функции, такие, как ДАТАМЕС (EDATE), КОНЕЦМЕСЯЦА (EMONTH), ЧИСТРАБДНИ (NETWORKDAYS) и WEEKNUM. Все эти функции можно найти в категории Дата и время (Date & Time) диалогового окна мастера функций. Их легко применять, сложнее узнать, что эти функции существуют, и привлечь их к делу.

Настоящие даты и время

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

Это можно легко распознать, немного расширив столбцы, выделив столбец, выбрав команду → Ячейки → Выравнивание (Home → Cells → Alignment) и для параметра По горизонтали (Horizontal) выбрав значение По значению (General) (это формат ячеек по умолчанию).

Щелкните кнопку ОК и внимательно просмотрите даты и время. Если какие-либо значения не выровнены по правому краю, Excel не считает их датами.

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

Не снимая выделение столбца, выберите команду → Специальная вставка → Значение → OK (Home → Paste Special → Value → Add).Tenepb Excel будет преобразовывать все текстовые даты и время в настоящие даты и время.

Возможно, вам придется еще раз изменить форматирование. Еще один простой способ — ссылаться на ячейки так: =А1+0 или =А1*1.

Ошибка даты?

Excel ошибочно предполагает, что 1900 год был високосным годом (Добавим, он был последним годом XIX века, а не первым XX). Это означает, что внутренняя система дат Excel считает, что существовал день 29 февраля 1900 года, тогда как его не было! Самое невероятное — Microsoft сделала это намеренно, по крайней мере, они так утверждают.

Источник: http://Excel2010.ru/hack-38.html

Как суммировать время в excel часы минуты

Сложение времени в Microsoft Excel. Учет времени в Excel: суммирование вычитание разницы часов и минут

Одной из задач, которая может стать перед пользователем во время работы в Эксель, является сложение времени.

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

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

Скачать последнюю версию Excel

Суммирование времени

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

  1. Выделяем соответствующие ячейки. Если это диапазон, то просто зажимаем левую кнопку мыши и обводим его. В случае, если мы имеем дело с отдельными ячейками, разбросанными по листу, то тогда выделение их проводим, кроме всего прочего, зажав кнопку Ctrl на клавиатуре.
  2. Кликаем правой кнопкой мыши, тем самым вызвав контекстное меню. Переходим по пункту «Формат ячеек…». Вместо этого можно также после выделения на клавиатуре набрать комбинацию Ctrl+1.
  3. Открывается окно форматирования. Переходим во вкладку «Число», если оно открылось в другой вкладке. В блоке параметров «Числовые форматы» переставляем переключатель в позицию «Время». В правой части окна в блоке «Тип» выбираем тот вид отображения, с которым будем работать. После того, как настройка выполнена, жмем на кнопку «OK» в нижней части окна.

Урок: Форматирование таблиц в Excel

Способ 1: показание часов через промежуток времени

Прежде всего, давайте посмотрим, как рассчитать, сколько будут показывать часы по прошествии определенного периода времени, выраженного в часах, минутах и секундах. В нашем конкретном примере нужно узнать, сколько будет на часах через 1 час 45 минут и 51 секунду, если сейчас на них установлено время 13:26:06.

  1. На отформатированном участке листа в разных ячейках с помощью клавиатуры вводим данные «13:26:06» и «1:45:51».
  2. В третьей ячейке, в которой тоже установлен формат времени, ставим знак «=». Далее кликаем по ячейке со временем «13:26:06», жмем на знак «+» на клавиатуре и кликаем по ячейке со значением «1:45:51».
  3. Для того чтобы результат вычисления вывести на экран, жмем на кнопку «Enter».

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

Способ 2: использование функции

Альтернативным вариантом предыдущему способу является использование функции СУММ.

  1. После того, как первичные данные (текущее показание часов и отрезок времени) введены, выделяем отдельную ячейку. Кликаем по кнопке «Вставить функцию».
  2. Открывается Мастер функций. Ищем в перечне элементов функцию «СУММ». Выделяем её и жмем на кнопку «OK».
  3. Запускается окно аргументов функции. Устанавливаем курсор в поле «Число1» и кликаем по ячейке, содержащей текущее время. Затем устанавливаем курсор в поле «Число2» и кликаем по ячейке, где указано время, которое нужно прибавить. После того, как оба поля заполнены, жмем на кнопку «OK».
  4. Как видим, происходит вычисление и в изначально выделенной ячейке выводится результат сложения времени.

Урок: Мастер функций в Excel

Способ 3: суммарное сложение времени

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

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

  1. Но, прежде нам нужно будет отформатировать ячейки по-другому, а не так, как было описано в предыдущих вариантах. Выделяем область и вызываем окно форматирования. Во вкладке «Число» переставляем переключатель «Числовые форматы» в позицию «Дополнительно». В правой части окна находим и устанавливаем значение «[ч]:мм:сс». Чтобы сохранить изменение, жмем на кнопку «OK».
  2. Далее нужно выделить диапазон, заполненный значением времени и одну пустую ячейку после него. Находясь на вкладке «», нажимаем на значок «Сумма», расположенный на ленте в блоке инструментов «Редактирование». Как альтернативный вариант, можно набрать на клавиатуре сочетание клавиш «Alt+=».
  3. После этих действий в пустой выделенной ячейке появится результат вычислений.

Урок: Как посчитать сумму в Экселе

Источник: https://timezin.ru/stati/kak-summirovat-vremya-v-excel-chasy-minuty.html

Сложить секунды. Учет времени в Excel: суммирование вычитание разницы часов и минут. Учет времени на протяжении суток

Сложение времени в Microsoft Excel. Учет времени в Excel: суммирование вычитание разницы часов и минут

Мы проверили на предыдущем уроке, что дата в Excel – это число количества дней прошедших от 01.01.1900г. А сейчас убедимся что время в Excel – это дробная часть числа, которая представляет собой период суток.

Работать с временем приходиться не редко. Например когда нужно отделу кадров сформировать отчет по балансу рабочего времени сотрудников предприятия.

Или производственному отделу нужно посчитать сколько потребуется работо-часов фрезерному станку на изготовления деталей мелкосерийного производства.

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

В диалоговом окне «Формат ячеек» на вкладке «Число» выберите «Пользовательский» в текстовом поле «Тип категории». Затем вы можете видеть, что каждая дата и ячейка времени добавляются один час. В приведенных выше формулах 1 указывает на добавление одного часа или одной минуты или одной секунды, вы можете изменить его по мере необходимости.

Рекомендуемые инструменты производительности

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

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

В настоящее время он преподает степень магистра журналистики в Университете Клариона.

Как посчитать сумму времени в Excel?

Каждая дробная часть чисел в Excel может отображаться в часах и минутах (в зависимости от выбранного формата отображения).

Данный факт лучше усвоить на конкретном примере:

  1. Заполните диапазон ячеек A1:A3 числовыми значениями: 0,25 (четверть дня); 0,5(полдень); 0,75 (3/4 дня).
  2. Выделите A1:A3 и с помощью диалогового окна «Формат ячеек» (CTRL+1) задайте формат «Время», как показано на рисунке:

Чтобы сложить время в Excel по формуле или с помощью функции:=СУММ() нужно использовать специальный формат.

Решение. В каждом случае рассчитывайте рабочее время за вычетом начала работы и считайте утренние и дневные часы. Это будет выглядеть примерно так. Выберите ячейку или столбец с результатом. Щелкните правой кнопкой мыши и выберите «Форматировать ячейки». Выберите категорию «Пользовательский».

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

  1. В ячейку А4 введите:=СУММ(A1:A3). Как видно вместо 36 часов мы видим только 12 – это время следующего дня. А чтобы отобразить сумму часов меняем формат ячейки.
  2. Открываетм окно «Формат ячеек»-«Число»-«Числовые форматы»-«Все форматы». В поле «Тип:» вводим: [ч]:мм:сс. И нажимаем ОК.

Теперь отображается количество суммированных часов и минут.

  • Создайте новый рабочий лист и введите следующие значения.
  • Убедитесь, что числа разделены двоеточием.
  • Обязательно вставьте начальные нули и разделите числа на двоеточие.
  • В списке «Категория» выберите «Пользовательский».
  • В поле «Тип» выберите коды номера для создания нужного формата.

ПРИМЕЧАНИЕ.

Применение форматов к дополнительным ячейкам

Если 1 представляет целый день, «0, 5» составляет половину дня или 12 часов. Таким образом, «1, 5» дня составляют полтора дня или 36 часов, «0, 25» равно 6 часам. Если вы добавите рабочее время всех рабочих дней вместе, вы увидите, что результат не может быть прав.

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

Данный метод отображения времени весьма полезный. Допустим нам нужно узнать разницу между временем в минутах. В ячейку B2 введите формулу:=A2-A1. Установите теперь для ячейки «Все форматы» и на этот раз в поле «Тип:» вводим: [мм]. И нажимаем ОК.

Так нам удалось перевести время в минуты. Если мы в эту же ячейку введем число 1 (или время 24:00 что по сути одно и то же), то мы узнаем что в сутках 1440 минут. Так фактически можно в Excel посчитать любое время в минутах.

На вкладке «Начало» в номере группы нажмите кнопку «Запуск диалога». Откроется диалоговое окно Формат ячеек. Эти эффекты форматирования различаются. В зависимости от форматирования ячейки-мишени отображается другой результат.

Это время, когда вы добавляете 18 часов до 17 часов. Форматирование, начинающееся с 1, указывает время, в то время как форматирование с 3 указывает на количество часов.

Поэтому, если вы хотите определить время, используйте 1-формат, если вы хотите вычислить сумму часов, 3-формат.

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

Содержимое ячейки теперь выглядит следующим образом. На вкладке «Начало» щелкните стартер диалогового окна в номере группы. В диалоговом окне «Форматирование ячеек» выберите категорию «Пользовательская».

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

Учет времени на протяжении суток

На следующем примере посчитаем, какая часть суток прошла от полночи до 9:45.

  1. В ячейки A1:A3 введите 9:45.
  2. Перейтите на A1 и вызовите окно «Формат ячеек» (CTRL+1). На закладке «Число», из списка «Числовые форматы» выберите «Дробный».
  3. Перейдите на A2 и задайте таким же способом «Процентный» формат (или просто нажмите комбинацию горячих клавиш CTRL+SHIFT+5).
  4. Перейдите на A3 и удалите формат ячейки. А из предыдущих уроков вы знаете, что удалить формат это присвоить формат по умолчанию «Общий». Для этого можно нажать комбинацию CTRL+SHIFT+` или использовать выше описанный способ.

Как видите 9:45 – это 2/5 частей суток. Проще говоря, прошло пока 40,63% суток.

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

Теперь вы можете активно выполнять счет-фактуру в то время! Откройте книгу. Рассчитайте продолжительность для каждого пункта повестки дня. Сравните результат с файлом. Ваш результат должен выглядеть следующим образом.

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

Как поставить текущее время в Excel?

Автоматический ввод текущего времени в Excel выполняется комбинацией горячих клавиш CTRL+SHIFT+;. Автоматическая вставка текущей даты и времени в Excel отличается использованием клавиши SHIFT.

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

Не забудьте включить начальные нули и использовать «двоеточие» для разделения чисел. Щелкните правой кнопкой мыши ячейки, которые вы хотите отобразить во вновь созданном формате, а затем нажмите «Форматировать ячейки».

  • Убедитесь, что вы разделяете числа с символом «двоеточие».
  • В диалоговом окне откройте вкладку «Число» и нажмите «Настроить».
  • В списке настраиваемых форматов, щелкните соответствующий формат и нажмите «ОК».

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

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

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

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

Сложить минуты и секунды онлайн. Учет времени в Excel: суммирование вычитание разницы часов и минут

Сложение времени в Microsoft Excel. Учет времени в Excel: суммирование вычитание разницы часов и минут

Мы проверили на предыдущем уроке, что дата в Excel – это число количества дней прошедших от 01.01.1900г. А сейчас убедимся что время в Excel – это дробная часть числа, которая представляет собой период суток.

Работать с временем приходиться не редко. Например когда нужно отделу кадров сформировать отчет по балансу рабочего времени сотрудников предприятия.

Или производственному отделу нужно посчитать сколько потребуется работо-часов фрезерному станку на изготовления деталей мелкосерийного производства.

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

Как складывать и вычитать даты, дни, недели, месяцы и годы в Excel – Microsoft Excel для начинающих

Сложение времени в Microsoft Excel. Учет времени в Excel: суммирование вычитание разницы часов и минут

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

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

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

Как вычитать даты в Excel

Предположим, что у Вас в ячейках A2 и B2 содержатся даты, и нужно вычесть одну дату из другой, чтобы узнать, сколько между ними дней. Как часто бывает в Excel, этот результат можно получить несколькими способами.

Пример 1. Непосредственно вычитаем одну дату из другой

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

=B2-A2

Пример 2. Вычитание дат с помощью функции РАЗНДАТ

Если предыдущая формула кажется Вам слишком простой, тот же результат можно получить более изощрённым способом с помощью функции РАЗНДАТ (DATEDIF).

=РАЗНДАТ(A2;B2;”d”)
=DATEDIF(A2,B2,”d”)

Следующий рисунок показывает, что обе формулы возвращают одинаковый результат, за исключением ряда 4, где функция РАЗНДАТ (DATEDIF) возвращает ошибку #ЧИСЛО! (#NUM!). Посмотрим, почему это происходит.

Когда Вы вычитаете более позднюю дату (6 мая 2015) из более ранней (1 мая 2015), операция вычитания возвращает отрицательное число. Однако синтаксис функции РАЗНДАТ (DATEDIF) не позволяет, чтобы начальная дата была больше конечной даты и, естественно, возвращает ошибку.

Пример 3. Вычитаем дату из текущей даты

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

=СЕГОДНЯ()-A2
=TODAY()-A2

или

=РАЗНДАТ(A2;СЕГОДНЯ();”d”)
=DATEDIF(A2,TODAY(),”d”)

Как и в предыдущем примере, формулы отлично работают, когда текущая дата больше, чем вычитаемая. В противном случае функция РАЗНДАТ (DATEDIF) возвращает ошибку.

Пример 4. Вычитание дат с помощью функции ДАТА

Если Вы предпочитаете вводить даты непосредственно в формулу, указывайте их с помощью функции ДАТА (DATE), а затем вычитайте одну дату из другой.

Функция ДАТА имеет следующий синтаксис: ДАТА(год; месяц; день).

Например, следующая формула вычитает 15 мая 2015 года из 20 мая 2015 года и возвращает разность – 5 дней.

=ДАТА(2015;5;20)-ДАТА(2015;5;15)
=DATE(2015,5,20)-DATE(2015,5,15)

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

Если нужно подсчитать количество месяцев или лет между двумя датами, тогда функция РАЗНДАТ (DATEDIF) – единственное возможное решение. В продолжении статьи Вы найдёте несколько примеров формул, подробно раскрывающих эту функцию.

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

Как прибавить (вычесть) дни к дате в Excel

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

Пример 1. Прибавление дней к дате в Excel

Общая формула добавления определённого количества дней к дате выглядит так:

= Дата + N дней

Дату можно задать несколькими способами:

  • Cсылкой на ячейку:

    =A2+10

  • Обращением к функции ДАТА (DATE):

    =ДАТА(2015;5;6)+10
    =DATE(2015,5,6)+10

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

    =СЕГОДНЯ()+10
    =TODAY()+10

Следующий рисунок показывает действие этих формул. На момент написания текста текущей датой было 6 мая 2015 г.

Примечание: Результатом выполнения этих формул является целое число, представляющее дату. Чтобы показать его как дату, необходимо выбрать ячейку (или ячейки) и нажать Ctrl+1.

Откроется диалоговое окно Формат ячеек (Format Cells). На вкладке Число (Number) в списке числовых форматов выберите Дата (Date) и затем укажите нужный вам формат.

Более подробное описание Вы найдёте в статье Как изменить формат даты в Excel.

Пример 2. Вычитание дней из даты в Excel

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

= Дата – N дней

Вот несколько примеров формул:

=A2-10
=ДАТА(2015;5;6)-10
=СЕГОДНЯ()-10

Как прибавить (вычесть) несколько недель к дате

Когда требуется прибавить (вычесть) несколько недель к определенной дате, Вы можете воспользоваться теми же формулами, что и раньше. Просто нужно умножить количество недель на 7:

  • Прибавляем N недель к дате в Excel:

    = A2 + N недель * 7

    Например, чтобы прибавить 3 недели к дате в ячейке А2, используйте следующую формулу:

    =A2+3*7

  • Вычитаем N недель из даты в Excel:

    = А2 – N недель * 7

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

    =СЕГОДНЯ()-2*7
    =TODAY()-2*7

Как прибавить (вычесть) несколько месяцев к дате в Excel

Чтобы прибавить (или отнять) определённое количество месяцев к дате, нужно использовать функцию ДАТА (DATE) или ДАТАМЕС (EDATE), как показано ниже.

Пример 1. Прибавляем несколько месяцев к дате с помощью функции ДАТА

Если список дат находится, например, в столбце A, укажите количество месяцев, которые Вы хотите добавить (положительное число) или отнять (отрицательное число) в какой-то ячейке, скажем, в C2.

Введите в ячейку B2 указанную ниже формулу, щёлкните по выделенному углу ячейки и протащите его мышкой вниз по столбцу B до последней заполненной ячейки в столбце A. Формула из ячейки B2 будет скопирована во все ячейки столбца B.

=ДАТА(ГОД(A2);МЕСЯЦ(A2)+$C$2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)+$C$2,DAY(A2))

Посмотрим, что делает эта формула. Логика формулы ясна и очевидна. Функция ДАТА(год; месяц; день) получает следующие аргументы:

  • Год из даты в ячейке A2;
  • Месяц из даты в ячейке A2 + количество месяцев, указанное в ячейке C2;
  • День из даты в ячейке A2;

Всё просто! Если Вы введёте в C2 отрицательное число, формула вычтет месяцы, а не прибавит.

Естественно, ничто не мешает Вам ввести минус прямо в формуле, чтобы вычесть месяцы:

=ДАТА(ГОД(A2);МЕСЯЦ(A2)-$C$2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)-$C$2,DAY(A2))

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

  • Прибавить месяцы к дате:

    =ДАТА(ГОД(A2);МЕСЯЦ(A2)+2;ДЕНЬ(A2))
    =DATE(YEAR(A2),MONTH(A2)+2,DAY(A2))

  • Вычесть месяцы из даты:

    =ДАТА(ГОД(A2);МЕСЯЦ(A2)-2;ДЕНЬ(A2))
    =DATE(YEAR(A2),MONTH(A2)-2,DAY(A2))

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

В Excel предусмотрена специальная функция, которая возвращает дату, отстоящую от заданной на определенное количество месяцев назад или вперёд, – это функция ДАТАМЕС (EDATE). Она доступна в последних версиях Excel 2007, 2010, 2013 и новой Excel 2016.

При использовании ДАТАМЕС (EDATE) Вы указываете два следующих аргумента:

  • Начальная дата – дата, от которой отсчитывается количество месяцев.
  • Месяцы – количество месяцев, которые нужно прибавить (положительное число) или отнять (отрицательное число).

Эти формулы дадут тот же самый результат, что и формулы с функцией ДАТА (DATE) в предыдущем примере:

При использовании функции ДАТАМЕС (EDATE) начальную дату и количество месяцев можно указывать непосредственно в формуле. Даты можно задавать с помощью функции ДАТА (DATE) или как результат выполнения других формул. Например:

  • Эта формула прибавляет 10 месяцев к 7 мая 2015 года

    =ДАТАМЕС(ДАТА(2015;5;7);10)
    =EDATE(DATE(2015,5,7),10)

  • Эта формула отнимает 10 месяцев от сегодняшней даты

    =ДАТАМЕС(СЕГОДНЯ();-10)
    =EDATE(TODAY(),-10)

Примечание: Функция ДАТАМЕС (EDATE) возвращает просто целое число. Чтобы представить его как дату, необходимо применить к ячейке формат даты. Как это сделать, указано в статье Как изменить формат даты в Excel.

Как прибавить (отнять) годы к дате в Excel

Добавление лет к датам в Excel осуществляется так же, как добавление месяцев. Вам необходимо снова использовать функцию ДАТА (DATE), но на этот раз нужно указать количество лет, которые Вы хотите добавить:

= ДАТА(ГОД(дата) + N лет; МЕСЯЦ(дата); ДЕНЬ(дата))
= DATE(YEAR(дата) + N лет, MONTH(дата), DAY(дата))

На листе Excel, формулы могут выглядеть следующим образом:

  • Прибавляем 5 лет к дате, указанной в ячейке A2:

    =ДАТА(ГОД(A2)+5;МЕСЯЦ(A2);ДЕНЬ(A2))
    =DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))

  • Вычитаем 5 лет из даты, указанной в ячейке A2:

    =ДАТА(ГОД(A2)-5;МЕСЯЦ(A2);ДЕНЬ(A2))
    =DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))

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

Прибавление (вычитание) дней, месяцев и лет к дате

Если Вы внимательно читали два предыдущих примера, то, думаю, догадались, как добавить (или вычесть) сразу годы, месяцы и дни к дате в одной формуле. Да, с помощью старой доброй функции ДАТА (DATA)!

  • Для прибавления X лет, Y месяцев и Z дней:

    = ДАТА(ГОД(дата) + X лет; МЕСЯЦ(дата) + Y месяцев; ДЕНЬ(дата) + Z дней)
    = DATE(YEAR(дата) + X лет, MONTH(дата) + Y месяцев, DAY(дата) + Z дней)

  • Для вычитания X лет, Y месяцев и Z дней:

    = ДАТА(ГОД(дата) – X лет; МЕСЯЦ(дата) – Y месяцев; ДЕНЬ(дата) – Z дней)
    = DATE(YEAR(дата) – X лет, MONTH(дата) – Y месяцев, DAY(дата) – Z дней)

Например, следующая формула прибавляет 2 года и 3 месяца, и вычитает 15 дней из даты в ячейке A2:

=ДАТА(ГОД(A2)+2;МЕСЯЦ(A2)+3;ДЕНЬ(A2)-15)
=DATE(YEAR(A2)+2,MONTH(A2)+3,DAY(A2)-15)

Применительно к нашему столбцу с датами, формула принимает следующий вид:

=ДАТА(ГОД(A2)+$C$2;МЕСЯЦ(A2)+$D$2;ДЕНЬ(A2)+$E$2)
=DATE(YEAR(A2)+$C$2,MONTH(A2)+$D$2,DAY(A2)+$E$2)

Как складывать и вычитать время в Excel

В Microsoft Excel складывать и вычитать время можно с помощью функции ВРЕМЯ (TIME). Она позволяет Вам обращаться с единицами времени (часами, минутами и секундами) так же, как и с годами, месяцами и днями в функции ДАТА (DATE).

  • Прибавить время в Excel:

    = A2 + ВРЕМЯ(часы; минуты; секунды)
    = A2 + TIME(часы, минуты, секунды)

  • Вычесть время в Excel:

    = A2 – ВРЕМЯ(часы; минуты; секунды)
    = A2 – TIME(часы, минуты, секунды)

    где A2 – это ячейка с временем, которое нужно изменить.

Например, чтобы добавить 2 часа 30 минут и 15 секунд к времени в ячейке A2 надо использовать следующую формулу:

=A2+ВРЕМЯ(2;30;15)
=A2+TIME(2,30,15)

Если вы хотите прибавлять и вычитать единицы времени в одной формуле, просто добавьте к нужным значениям знак минус “-“. Следующая формула прибавляет к времени в ячейке A2 2 часа 30 минут и вычитает 15 секунд:

=A2+ВРЕМЯ(2;30;-15)
=A2+TIME(2,30,-15)

Также Вы можете ввести нужные значения в ячейки листа и ссылаться на них в формуле:

=A2+ВРЕМЯ($C$2;$D$2;$E$2)
=A2+TIME($C$2,$D$2,$E$2)

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

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

Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/kak-skladyvat-i-vychitat-daty-dni-nedeli-mesjacy-i-gody-v-excel-438.html

Поделиться:
Нет комментариев

    Добавить комментарий

    Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.