Как складывать и вычитать даты, дни, недели, месяцы и годы в Excel
В этом уроке Вы познакомитесь с различными полезными формулами сложения и вычитания дат в Excel. Например, Вы узнаете, как вычесть из одной даты другую, как прибавить к дате несколько дней, месяцев или лет, и т.д.
Если Вы уже проходили уроки по работе с датами в Excel (наши или любые другие уроки), то должны знать формулы для вычисления единиц времени, таких как дни, недели, месяцы, года.
Анализируя даты в каких-либо данных, часто нужно выполнять над этими датами арифметические действия. Эта статья объяснит некоторые формулы сложения и вычитания дат, которые могут оказаться для Вас полезными.
- Вычитание двух дат в Excel
- Как прибавить (отнять) дни к дате в Excel
- Как прибавить (вычесть) несколько недель к дате
- Добавляем месяцы к дате в Excel
- Добавляем годы к дате в Excel
- Прибавление (вычитание) дней, месяцев и лет к дате
- Как складывать и вычитать время в Excel
Как вычитать даты в Excel
Предположим, что у Вас в ячейках A2 и B2 содержатся даты, и нужно вычесть одну дату из другой, чтобы узнать, сколько между ними дней. Как часто бывает в Excel, этот результат можно получить несколькими способами.
Пример 1. Непосредственно вычитаем одну дату из другой
Думаю, Вы знаете, что Excel хранит даты в виде целых чисел, начиная с 1, что соответствует 1 января 1900 г. Поэтому Вы можете просто арифметически вычесть одно число из другого:
Пример 2. Вычитание дат с помощью функции РАЗНДАТ
Если предыдущая формула кажется Вам слишком простой, тот же результат можно получить более изощрённым способом с помощью функции РАЗНДАТ (DATEDIF).
Следующий рисунок показывает, что обе формулы возвращают одинаковый результат, за исключением ряда 4, где функция РАЗНДАТ (DATEDIF) возвращает ошибку #ЧИСЛО! (#NUM!). Посмотрим, почему это происходит.
Когда Вы вычитаете более позднюю дату (6 мая 2015) из более ранней (1 мая 2015), операция вычитания возвращает отрицательное число. Однако синтаксис функции РАЗНДАТ (DATEDIF) не позволяет, чтобы начальная дата была больше конечной даты и, естественно, возвращает ошибку.
Пример 3. Вычитаем дату из текущей даты
Чтобы вычесть определенную дату из текущей даты, Вы можете использовать любую из ранее описанных формул. Просто вместо сегодняшней даты используйте функцию СЕГОДНЯ (TODAY):
Как и в предыдущем примере, формулы отлично работают, когда текущая дата больше, чем вычитаемая. В противном случае функция РАЗНДАТ (DATEDIF) возвращает ошибку.
Пример 4. Вычитание дат с помощью функции ДАТА
Если Вы предпочитаете вводить даты непосредственно в формулу, указывайте их с помощью функции ДАТА (DATE), а затем вычитайте одну дату из другой.
Функция ДАТА имеет следующий синтаксис: ДАТА(год; месяц; день).
Например, следующая формула вычитает 15 мая 2015 года из 20 мая 2015 года и возвращает разность – 5 дней.
Подводим итог. Если нужно вычитать даты в Excel, и Вы хотите определить количество дней между двумя датами, имеет смысл делать это самым простым и очевидным способом – напрямую вычитать одну дату из другой.
Если нужно подсчитать количество месяцев или лет между двумя датами, тогда функция РАЗНДАТ (DATEDIF) – единственное возможное решение. В продолжении статьи Вы найдёте несколько примеров формул, подробно раскрывающих эту функцию.
Теперь, когда Вы знаете, как вычитать одну дату из другой, посмотрим, как можно прибавить или вычесть из даты определённое количество дней, месяцев или лет. Для этого существует несколько функций Excel. Что именно выбрать зависит от того, какие единицы времени требуется прибавить или вычесть.
Как прибавить (вычесть) дни к дате в Excel
Если у Вас есть дата в ячейке или список дат в столбце, Вы можете прибавить к ним (или отнять) определённое количество дней, используя соответствующую арифметическую операцию.
Пример 1. Прибавление дней к дате в Excel
Общая формула добавления определённого количества дней к дате выглядит так:
Дату можно задать несколькими способами:
- 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
Чтобы вычесть определённое количество дней из даты, Вам снова нужно использовать обычную арифметическую операцию. Единственное отличие от предыдущего примера – минус вместо плюса ��
Вот несколько примеров формул:
=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.
Посмотрим, что делает эта формула. Логика формулы ясна и очевидна. Функция ДАТА(год; месяц; день) получает следующие аргументы:
Всё просто! Если Вы введёте в C2 отрицательное число, формула вычтет месяцы, а не прибавит.
Естественно, ничто не мешает Вам ввести минус прямо в формуле, чтобы вычесть месяцы:
И, конечно, вы можете указать число прибавляемых или вычитаемых месяцев непосредственно в формуле без ссылки на ячейку. Готовые формулы будут выглядеть примерно так:
- Прибавить месяцы к дате: =ДАТА(ГОД(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), но на этот раз нужно указать количество лет, которые Вы хотите добавить:
На листе 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:
Применительно к нашему столбцу с датами, формула принимает следующий вид:
Как складывать и вычитать время в Excel
В Microsoft Excel складывать и вычитать время можно с помощью функции ВРЕМЯ (TIME). Она позволяет Вам обращаться с единицами времени (часами, минутами и секундами) так же, как и с годами, месяцами и днями в функции ДАТА (DATE).
- Прибавить время в Excel: = A2 + ВРЕМЯ(часы; минуты; секунды)
= A2 + TIME(часы, минуты, секунды) - Вычесть время в Excel: = A2 — ВРЕМЯ(часы; минуты; секунды)
= A2 — TIME(часы, минуты, секунды) где A2 – это ячейка с временем, которое нужно изменить.
Например, чтобы добавить 2 часа 30 минут и 15 секунд к времени в ячейке A2 надо использовать следующую формулу:
Если вы хотите прибавлять и вычитать единицы времени в одной формуле, просто добавьте к нужным значениям знак минус «-«. Следующая формула прибавляет к времени в ячейке A2 2 часа 30 минут и вычитает 15 секунд:
Также Вы можете ввести нужные значения в ячейки листа и ссылаться на них в формуле:
Если исходная ячейка содержит и дату, и время, предыдущая формула также прекрасно работает:
Вот так в Excel можно складывать и вычитать даты. Надеюсь, что сегодня Вы узнали пару полезных функций. В любом случае благодарю Вас за внимание и надеюсь увидеться снова!
Как из времени извлечь часы (минуты, секунды)
Функция ЧАС возвращает час заданного времени, определяется как целое число в интервале от 0 до 23.
Синтаксис
ЧАС(время в числовом формате)
Время в числовом формате – время из которого нужно вернуть часы. Может быть представлено:
Функция МИНУТЫ
Функция МИНУТЫ возвращает минуты заданного времени, определяется как целое число в интервале от 0 до 59.
Синтаксис
МИНУТЫ(время в числовом формате)
Время в числовом формате – время из которого нужно вернуть минуты. Значение аргумента такое же, как для функции ЧАС, например, =МИНУТЫ(“15:25”) вернет результат 25 и т.д.
Функция СЕКУНДЫ
Функция СЕКУНДЫ возвращает секунды заданного времени, определяется как целое число в интервале от 0 до 59.
Синтаксис
СЕКУНДЫ(время в числовом формате)
Время в числовом формате – время из которого нужно вернуть секунды. Значение аргумента такое же, как для функции ЧАС, например, =СЕКУНДЫ(“15:25:30”) вернет результат 30 и т.д.
Употребление функций ЧАС, МИНУТЫ, СЕКУНДЫ с другими функциями
Для примера рассчитаем время расписания занятий.
Сначала с помощью функции ВРЕМЯ напишем время начала 1 урока.
Время окончания: В мастере функций выберем функцию ВРЕМЯ. В аргументах функции пропишем, чтобы часы, минуты и секунды брались из ячейки D2, но в минутах прибавим длительность урока (ячейку B2).
Аналогично пропишем начало 2 урока: в аргументах функции ВРЕМЯ пропишем функции ЧАС, МИНУТЫ, СЕКУНДЫ со ссылками на время окончания 1 урока. В минутах прибавим время перерыва (С2).
Далее формулы можно просто продлить.
Далее можно легко пересчитать время занятий, например для короткого дня сделаем длительность урока 35 мин, а перерыв 5 мин. Время начала и время окончания Excel пересчитает автоматически.
Автор Alfi Опубликовано 22.01.2019 22.01.2019 Рубрики Функции
Добавить комментарий Отменить ответ
Для отправки комментария вам необходимо авторизоваться.
Формула конвертации времени в минутах во время в часах и минутах
Имеется число минут например 150 . С простой формулой =FLOOR((D4/60); 0,1) мы получим число в десятичном формате, что при не целом делении покажет 2.5 а не то что хотелось 2:30 . Помогите с формулой, просто не знаю что тут можно использовать для решения вопроса.
Отслеживать
задан 26 дек 2017 в 15:23
Дмитрий Гвоздь Дмитрий Гвоздь
1,270 3 3 золотых знака 23 23 серебряных знака 51 51 бронзовый знак
CONCATENATE(FLOOR(A1/60;1);»:»;MOD(A1;60))
26 дек 2017 в 15:36
B1=A1/24/60. Формат ячейки = «ч:мм».
26 дек 2017 в 16:20
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
В Excel дата хранится в виде целого числа — количества дней, начиная с 01.01.1900 года. Например, 26.12.2017 = 43095 (если ячейке с датой задать числовой или общий формат, можно увидеть десятичное представление даты)
И если 1 сутки — это 1 (единица), а время — часть суток, то совершенно правильный вывод: время — дробная часть десятичного числа.
1 час = 1/24 1 минута = 1/(24*60) = 1/24/60 = 1/1440 1 секунда = =1/(24*60*60) = 1/24/60/60 = 1/86400 Зная это, десятичное число можно преобразовать во время — часы, минуты, секунды, разделив его на нужное число. При этом не забывая задавать нужный формат ячейки.
Иногда время необходимо показать текстом (например, в составе другого текста) Варианты текстового представления:
=ТЕКСТ(A1/1440;"чч:мм") =ЦЕЛОЕ(A1/60)&":"&A1-ЦЕЛОЕ(A1/60)*60 =ЦЕЛОЕ(A1/60)&" ч. "&A1-ЦЕЛОЕ(A1/60)*60&" мин. " В английской версии ТЕКСТ — TEXT, ЦЕЛОЕ — INT
Как добавить время в Google Таблицах
При создании собственного графика, расписания, списка задач или для каких-либо расчетов в Google Таблицах может потребоваться добавление текущего времени в одну или несколько ячеек. Для этого можно использовать методы с автоматической простановкой часов, а также самостоятельно форматировать ячейки должным образом, если время не должно меняться при сохранении. Давайте более детально остановимся на этой теме, разобрав все доступные варианты.
Использование функции ТДАТА
Функция ТДАТА в Гугл Таблицах расшифровывается как «Текущая дата», соответственно, используется для вывода в ячейке текущей даты. Вы будете видеть как число, так и часы с минутами и секундами рядом. С одной стороны это преимущество, поскольку все сведения о дне отображаются рядом. С другой – не всегда необходим вывод числа рядом со временем. Для ознакомления с функцией просто в любой свободной ячейке введите =ТДАТА() и нажмите Enter для получения вывода.
Результат будет обновляться с каждым сохранением таблицы, поэтому вы всегда будете видеть актуальное число и время. К сожалению, формат функции изменить нельзя, поэтому очередность чисел, а также набор информации всегда будет оставаться одинаковым. То есть нельзя исключить отсюда число или те же секунды.
Дополнительно по этой функции отмечу, что если вас интересует вывод только дня, то используйте вместо ТДАТА функцию СЕГОДНЯ. При проблемах с отображением числа вызовите меню «Формат» и поменяйте формат ячейки на подходящий либо же установите общий, чтобы при активизации функции сервис сам понимал, в каком формате необходимо отображать значение.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Формула для вывода времени
Существует специальная формула, состоящая из нескольких функций, которая позволяет выводить в ячейку текущее время без даты. Она может показаться довольно длинной в самой строке, но, по сути, вам ничего менять не придется. Просто вставьте =ВРЕМЯ(ЧАС(ТДАТА()); МИНУТЫ(ТДАТА()); СЕКУНДЫ(ТДАТА())) в необходимую ячейку и нажмите Enter для применения этой функции.
В результате вы увидите время с часами, минутами и секундами. Этого уже достаточно для того, чтобы приступить к дальнейшей работе с таблицей, а данную информацию использовать в других расчетах или же просто всегда видеть в своем расписании.
Вместе с этим стоит понимать, что данное значение всегда обновляется и будет актуальным для вашего часового пояса, который установлен в настройках Google Таблиц. То есть вам нужно внести всего лишь одно изменение или просто несколько раз переключиться между ячейками, чтобы время уже автоматически обновилось.
Если же при использовании формулы получилось так, что время отображается некорректно, скорее всего, в настройках установлен не тот часовой пояс, в котором вы сейчас находитесь. Его можно поменять вручную на подходящий, к тому же подобный параметр актуален и в других случаях, когда необходимо отталкиваться от времени в каком-то другом регионе. Более детально о данной настройке читайте в другой моей статье по ссылке ниже.
Читайте также в Комьюнити: Настройка региона, времени и параметров расчетов в Google Таблицах
Ручной ввод времени
Не стоит исключать ситуации, когда нужно указать в ячейке конкретное время, а не текущее. Обычно для этого используется стандартный метод ввода значения. Однако некоторые пользователи иногда могут столкнуться с проблемами при отображении значений и их использовании в каких-либо функциях. Исключаются такие ситуации правильной настройкой формата ячейки.
- Для начала просто запишите время в ячейке в том формате, который вы хотели бы видеть. Оставьте ячейку выделенной, чтобы все дальнейшие изменения к ней применялись.
- Откройте меню «Формат», наведите курсор на «Числа» и из раскрывающегося списка выберите пункт «Время» или «Дата и время», если ко времени вы пишете еще и число. В большинстве случаев этого уже будет достаточно для корректного отображения времени и возможности добавлять его в свои функции.
Если же ваша задача не была решена, поскольку интересует не совсем стандартное отображение значений, понадобится использовать настройки пользовательского формата. Его можно как полностью создать с нуля, так и использовать настраиваемое отображение времени и даты. Разберем первый вариант, поскольку он простой и подойдет почти всем.
- В списке форматов вам необходимо выбрать «Пользовательские дата и время» или «Другие форматы чисел», если заинтересованы в настройке с нуля.
- При настройке формата даты и времени появятся плитки, которые можно менять местами, например, если сначала нужно отобразить секунды, а потом уже минуты и часы. То же самое касается и отображения дат. Вместе с этим внизу есть целый список распространенных форматов, которые тоже могут подойти.
- После внесения изменений не забудьте нажать «Применить».
Если изменения формата касаются не только одной ячейки, предварительно выделите весь диапазон. Однако не изменяйте формат тех ячеек, где будут расположены другие числа или текст, поскольку это напрямую скажется на работе формул и отображении значений.
Использование календаря
Последним методом взаимодействия со временем в Google Таблицах можно считать вариант с добавлением записей в календарь. В дальнейшем их можно будет закреплять в ячейках и получать уведомления на свой адрес электронной почты или в пуш-оповещениях прямо на смартфоне. К таким уведомлениям можно прикрепить ссылку на таблицу, если она нужна для выполнения задания.
- На панели слева вы видите иконку с календарем, нажатием по которой открывается само приложение.
- В нем можно выбрать дату, а потом уже время, для которого нужно задать задачу или напоминание.
- Остается только добавить описание, прикрепить документ и сформировать событие, которое потом можно вставить в ячейку Гугл Таблиц.
По большей части календарь в Google Таблицах используется для ведения заметок, добавления важных задач и мероприятий. Однако его можно смело добавлять в расписание, использовать прикрепления с чипами для ячеек и всегда быть в курсе предстоящих событий.
Читайте также в Комьюнити: Задачи и напоминания в Google Таблицах
Теперь вы знаете, что в Гугл Таблицах можно не просто вводить дату от руки, но и использовать функции и формулы, которые всегда будут отображать текущее время. Помимо этого была затронута и тема с календарем, которая определенному кругу пользователей точно покажется полезной и применимой на практике.