Как в excel к дате прибавить дни и получить дату

Как вычислить дату в Excel

Дата и время в Excel – это числа сформатированные специальным образом. Датой является целая часть числа, а время (часы и минуты) – это дробная часть.

По умолчанию число 1 соответствует дате 01 января 1900 года. То есть каждая дата – это количество дней прошедших от 01.01.1900. На данном уроке детально рассмотрим даты, а на следующих уроках – время.

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

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

1. На чистом листе в ячейку А1 введите текущею дату нажав CTRL+;.
2. В ячейке A2 введите промежуточный период в днях, например 127.
3. В ячейку A3 введите формулу:=A1+A2.

Обратите внимание, формат «Дата» автоматически присвоился для ячейки A3. Не сложно догадаться, чтобы посчитать разницу в датах в Excel нужно от новейшей даты отнять старшую дату. В ячейке B1 введите формулу:=A3-A1. Соответственно мы получаем количество дней между этими двумя датами.



Вычисление возраста по дате рождения в Excel

Теперь мы научимся вычислять возраст по дате рождения:

1. На новый лист в ячейки A1:A3 введите даты: 18.04.1985; 17.08.1977; 08.12.1992
2. В ячейки B1:B3 проставьте текущею дату.
3. Теперь нужно воспользоваться функцией по преобразованию количества дней в количество лет. Для этого вручную введите в диапазон C1:C3 следующее значение: =РАЗНДАТ(A1;B1;"y").

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

Внимание! Чтобы перевести дни в года не достаточно формулы: =(B1-A1)/365. Более того даже если мы знаем что 1 сутки = 0,0027397260273973 года, то формула:=(B1-A1)*0,0027397260273973 так же не даст точного результата.

Дни в года наиболее точно преобразует функция:=РАЗНДАТ(). Английская версия =DATEDIF(). Вы не найдете ее в списке мастера функций (SHIFT+F3). Но если просто введете ее в строку формул, то она сработает.

Функция РАЗНДАТ поддерживает несколько параметров:

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

Внимание! Чтобы функция:=РАЗНДАТ() работала без ошибок, следите за тем, чтобы начальная дата была старше конечной даты.

Ввод даты в ячейку Excel

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

Заполните таблицу датами, так как показано на рисунке:

Разные способы ввода дат. В столбце А – способ ввода, а в столбце B – результат отображения.

Обратите внимание, что в формате ячеек по умолчанию «Общий», даты также как и число выравниваются по правой стороне, а текст по левой. Значение в ячейке B4 распознается программой как текст.

В ячейке B7 Excel сам присвоил текущий год (сейчас 2015-ый) по умолчанию. Это видно при отображении содержимого ячеек в строке формул. Обратите внимание, как изначально вводилось значение в A7.

Вычисление даты Excel

На чистом листе в ячейки А1:B1 введите 01.01.1900, а в ячейках A2:B2 вводим 01.01.1901. Теперь меняем формат ячеек на «числовой» в выделенном диапазоне B1:B2. Для этого можно нажать комбинацию клавиш CTRL+SHIFT+1.

B1 теперь содержит число 1, а B2 – 367. То есть прошел один високосный год (366 дней) и 1 день.

Способ отображения даты можно задать с помощью диалогового окна «Формат ячеек». Для его вызова нажмите: CTRL+1. На закладке «Число» выберите в списке «Числовые форматы» - «Дата». В разделе «Тип» отображаются самые популярные форматы для отображения дат.

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

Читайте также: Функции для работы с датами в Excel

На следующем уроке мы на готовых примерах будем работать со временем и периодами суток.

все уроки

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

28605 27.10.2012 Скачать пример

Видео

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

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

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

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

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

16:45

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

16:45:30

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

27. 10.2012 16:45

Быстрый ввод дат и времени

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

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

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

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

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

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

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

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

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

• Во-первых, 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:

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

• Как вычислять возраст (стаж) в полных годах-месяцах-днях
• Как сделать выпадающий календарь для быстрого ввода любой даты в любую ячейку.
• Автоматическое добавление текущей даты в ячейку при вводе данных.
• Как вычислить дату второго воскресенья февраля 2007 года и т.п.

Добавить или вычесть даты — Служба поддержки Майкрософт

Excel

Формулы и функции

Дополнительные функции

Дополнительные функции

Сложение или вычитание дат

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно. .. Меньше

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

Добавление или вычитание дней из даты

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

1. Введите даты родов в столбце A.

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

3. В ячейке C2 введите =A2+B2 и при необходимости скопируйте вниз.

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

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

Функция EDATE требует два аргумента: начальную дату и количество месяцев, которые вы хотите добавить или вычесть. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента. Например, =ДАТАМЕС("9/15/19",-5) возвращает 4 /15/19 .

1. В этом примере вы можете ввести даты начала в столбце A.

2. Введите количество месяцев для прибавления или вычитания в столбце B. Чтобы указать, следует ли вычесть месяц, вы можете ввести знак минус (-) перед числом (например, -1).

3. Введите = ДАТАМЕС(A2,B2) в ячейку C2 и скопируйте при необходимости.

   Примечания:

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

     .

   • Excel хранит даты в виде последовательных порядковых номеров, чтобы их можно было использовать в вычислениях. По умолчанию 1 января 1900 г. имеет порядковый номер 1, а 1 января 2010 г. — порядковый номер 40179, так как это 40 178 дней после 1 января 1900 г.

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

     • Нажмите Ctrl+1 , чтобы открыть диалоговое окно Формат ячеек , и щелкните вкладку Число .

     • В категории щелкните Дата , выберите нужный формат даты и щелкните OK . Значение в каждой из ячеек должно отображаться в виде даты, а не серийного номера.

Прибавление или вычитание лет из даты

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

=ДАТА(ГОД(A2)+B2,МЕСЯЦ(A2),ДЕНЬ(A2))

Как работает формула:

• Функция ГОД просматривает дату в ячейке A2 и возвращает 2019 год. Затем она добавляет 3 года к ячейке B2, в результате чего получается 2022 год.

• Функции МЕСЯЦ и ДЕНЬ возвращают только исходные значения из ячейки A2, но они требуются для функции ДАТА.

• Наконец, функция ДАТА затем объединяет эти три значения в дату через 3 года — 08.02.22 .

Добавление или вычитание комбинации дней, месяцев и лет до/из даты

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

=ДАТА(ГОД(A2)+B2,МЕСЯЦ(A2)+C2,ДЕНЬ(A2)+D2)

Как работает формула:

• Функция ГОД просматривает дату в ячейке A2 и возвращает 2019 год. Затем она добавляет 1 год из ячейки B2, в результате чего получается 2020 год.

• Функция МЕСЯЦ возвращает 6, а затем добавляет к нему 7 из ячейки C2. Это становится интересным, потому что 6 + 7 = 13, то есть 1 год и 1 месяц. В этом случае формула распознает это и автоматически добавит к результату еще один год, переместив его с 2020 на 2021 год.

• Функция ДЕНЬ возвращает 8 и добавляет к нему 15. Это будет работать аналогично части формулы МЕСЯЦ, если вы превысите количество дней в данном месяце.

• Функция ДАТА затем объединяет эти три значения в дату, которая находится через 1 год, 7 месяцев и 15 дней – 23.01.21 .

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

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

1. В ячейке A1 введите 08.02.13 .

2. В ячейке B1 введите =A1-15 .

3. В ячейке C1 введите =A1+30 .

4. В ячейке D1 введите =C1-15 .

Добавить месяцы к дате

Мы будем использовать функцию EDATE , и вам потребуется начальная дата и количество месяцев, которые вы хотите добавить. Вот как добавить 16 месяцев к 24.10.13:

1. В ячейке A1 введите 24.10.13 .

2. В ячейке B1 введите =ДАТАМЕС(A1,16) .

3. Чтобы отформатировать результаты в виде дат, выберите ячейку B1. Щелкните стрелку рядом с Числовой формат , > Краткая дата .

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

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

Введите дату в ячейку A1 и в ячейку B1 введите формулу =ДАТА(15.04.2013,-5) .

Здесь мы указываем значение даты начала, вводя дату, заключенную в кавычки.

Вы также можете просто обратиться к ячейке, содержащей значение даты, или использовать формулу = ДАТАМЕС(A1,-5) для получения того же результата.

Еще примеры

Чтобы прибавить или вычесть годы из даты

Добавление или вычитание дат в Excel для Mac

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

Добавление дней к дате или вычитание дней из даты

Предположим, остаток на счете должен быть оплачен 8 февраля 2012 г. Вы хотите перевести средства на свой расчетный счет, чтобы эти средства поступили за 15 календарных дней до установленного срока. Кроме того, вы знаете, что в вашей учетной записи предусмотрен 30-дневный цикл выставления счетов, и вы хотите определить, когда вам следует перевести средства для оплаты счета за март 2012 г. , чтобы эти средства были доступны за 15 дней до этой даты. Для этого выполните следующие действия:

1. Открыть новый лист в книге.

2. В ячейке A1 введите 08.02.12 .

3. В ячейке B1 введите =A1-15 и нажмите клавишу RETURN.

   Эта формула вычитает 15 дней из даты в ячейке A1.

4. В ячейке C1 введите =A1+30 и нажмите клавишу RETURN.

   Эта формула добавляет 30 дней к дате в ячейке A1.

5. В ячейке D1 введите =C1-15 и нажмите клавишу RETURN.

   Эта формула вычитает 15 дней из даты в ячейке C1.

   В ячейках A1 и C1 указаны даты оплаты (08.02.12 и 09.03.12) остатков по счетам за февраль и март. В ячейках B1 и D1 указаны даты (24.01.12 и 23.02.12), к которым вы должны перевести свои средства, чтобы эти средства поступили на 15 календарных дней раньше сроков оплаты.

Добавление месяцев к дате или вычитание месяцев из даты

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

Функция ДАТАМЕС требует два значения (также называемых аргументом): начальная дата и количество месяцев, которые вы хотите добавить или вычесть. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента (например, =ДАТАМЕС("15.02.12",-5)). Эта формула вычитает 5 месяцев из 15.02.12 и дает дату 9/15/11.

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

Например, предположим, что вы хотите добавить 16 месяцев к 16 октября 2012 г.

1. В ячейке A5 введите 16.10.12 .

2. В ячейке B5 введите =ДАТА(A5,16) , а затем нажмите RETURN .

   Функция использует значение в ячейке A5 в качестве начальной даты.

3. В ячейке C5 введите =ДАТАМЕС("16/12/12",16) , а затем нажмите RETURN .

   В этом случае функция использует значение даты, которое вы вводите напрямую, «16.10.12».

   В ячейках B5 и C5 указана дата 16.02.14.

   Почему мои результаты отображаются в виде чисел, а не дат?

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

   1. Выберите ячейки B5 и C5.

   2. На вкладке Главная в разделе Формат выберите Формат ячеек , а затем выберите Дата . Значение в каждой из ячеек должно отображаться в виде даты, а не серийного номера.

Прибавление или вычитание лет из даты

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

1. На новом листе введите 09.06.2009 в ячейку A2, а затем введите 3 в ячейку B2.

2. В ячейке A3 введите 02.09.2009 , а затем введите -5 в ячейке B3.

3. В ячейке A4 введите 10.12.2010 , а затем введите 25 в ячейке B4.

4. В ячейке A6 введите =ДАТА(ГОД(A2)+B2,МЕСЯЦ(A2),ДЕНЬ(A2)) , а затем нажмите RETURN .

   Эта формула складывает значение в ячейке B2 (3 года) со значением в ячейке A2 для результата от 09.06.2012.

5. В ячейке A7 введите =ДАТА(ГОД(A3)+B3,МЕСЯЦ(A3),ДЕНЬ(A3)) и нажмите RETURN .

   Эта формула добавляет значение в ячейке B3 (–5 лет) к значению в ячейке A3, чтобы получить результат 9/2/2004.

6. В ячейке A8 введите =ДАТА(ГОД(A4)+B4,МЕСЯЦ(A4),ДЕНЬ(A4)) и нажмите RETURN .

   Эта формула добавляет значение в ячейке B4 (25 лет) к значению в ячейке A4, чтобы получить результат 10. 12.35.

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

   Например, в ячейке A6 функция ГОД используется для даты в ячейке A2 (09.06.2009) и возвращает 2009 в качестве года. Затем формула добавляет 3 (значение в ячейке B2) к значению года, в результате чего получается 2012. В той же формуле функция МЕСЯЦ возвращает значение 6, а функция ДЕНЬ возвращает значение 9. Затем функция ДАТА объединяет эти три значения в дату через три года: 09.06.2012.

Добавление комбинации дней, месяцев и лет к дате

Предположим, вы хотите добавить комбинацию дней, месяцев и лет к определенной дате.

1. На новом листе введите 09.06.2012 в ячейку A2.

2. В ячейке A4 введите =ДАТА(ГОД(A2)+3,МЕСЯЦ(A2)+1,ДЕНЬ(A2)+5) , а затем нажмите RETURN .

   Эта формула добавляет 3 года, 1 месяц и 5 дней к 6/9./2012, по результатам 14.07.2015.

3. В ячейке A5 введите =ДАТА(ГОД(A2)+1,МЕСЯЦ(A2)+7,ДЕНЬ(A2)+5) , а затем нажмите RETURN .

   Эта формула добавляет 1 год, 7 месяцев и 5 дней к 09.06.2012, чтобы получить результат 14.01.2014.

   В каждой формуле указанное количество лет, месяцев и дней добавляется к дате, содержащейся в ячейке A2.

   Например, в ячейке A5 функция ГОД используется для даты в ячейке A2 (09.06.2012) и возвращает 2012 в качестве года. Затем формула добавляет 1 к значению года, в результате чего получается 2013 год.

   ---

   Learn more

   • 
     Windows 11 как перенести пуск
   • 
     Как узнать сколько плашек оперативки установлено в ноутбуке
   • 
     Патч корд для роутера
   • 
     Удаление шума в adobe audition
   • 
     Структура и состав персонального компьютера
   • 
     Как создать папку на смартфоне андроид
   • 
     Прошивка на телевизор philips
   • 
     Флешка показывает меньший объем как восстановить
   • 
     Код экземпляра устройства
   • 
     Настроить смарт тв на телевизоре lg
   • 
     Что значит расшарить