Как вычесть из даты дату в экселе: Как складывать и вычитать даты, дни, недели, месяцы и годы в Excel

Вычисление разности двух дат — Excel

Используйте networkDAYS. Функция INTL, если нужно вычислить количество рабочих дней между двумя датами. Кроме того, можно исключить выходные дни и праздники.

Прежде чем начать: Решите, нужно ли исключить даты праздников. В этом случае введите список дат праздников в отдельной области или на отдельном листе. Поместите каждую праздниковую дату в собственную ячейку. Затем выберем эти ячейки, выберем «Формулы> «Определить имя». Привяжем диапазон MyHolidaysи нажмите кнопку «ОК». Затем создайте формулу с помощью действий ниже.

1. Введите даты начала и окончания.

В этом примере дата начала находится в ячейке D53, а даты окончания — в ячейке E53.

2. В другой ячейке введите формулу, например:

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

Примечание. В Excel 2007 нет networkDAYS. Функция INTL. Однако в нем есть и NETWORKDAYS. В Excel 2007 пример будет выглядеть так: =NETWORKDAYS(D53;E53). Вы не указываете 1, так как в networkDAYS предполагается, что выходные — в субботу и воскресенье.

3. При необходимости измените 1.

Если суббота и воскресенье не являются выходными днями, измените число 1 в списке IntelliSense. Например, 2 являются выходными днями по понедельникам и воскресеньям.

Если вы используете Excel 2007, пропустите этот шаг. Функция NETWORKDAYS в Excel 2007 всегда предполагает, что выходные дни — в субботу и воскресенье.

4. Введите название диапазона праздников.

Если вы создали название диапазона праздников в разделе «Перед началом» выше, введите его в конце, как понравится.

Если у вас нет праздников, оставьте запятую и MyHolidays. Если вы используете Excel 2007, пример будет такой: =NETWORKDAYS(D53,E53,MyHolidays).

Совет. Если вы не хотите ссылаться на название диапазона праздников, можно также ввести диапазон, например D35:E:39. Или введите в формулу каждый праздник. Например, если ваши праздники были 1 и 2 января 2016 г., введите их так: =NETWORKDAYS. INTL(D53,E53,1,{«01.01.2016″,»02.01.2016»}). В Excel 2007 все выглядит так: =NETWORKDAYS(D53;E53;{«01.01.2016″;»02.01.2016»})

Как вычислить дату в 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). Но если просто введете ее в строку формул, то она сработает.

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

Параметр	Описание
«d»	Количество полных дней
«m»	Количество полных месяцев
«y»	Количество полных лет
«ym»	Количество полных месяцев без учета лет
«md»	Количество дней без учета месяцев и лет
«yd»	Количество дней без учета лет

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

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

Ввод даты в ячейку 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

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

3 примера, как функция ЕСЛИ работает с датами.

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

Примеры работы функции ЕСЛИ с датами.

Дата в качестве условия, с которым работает функция ЕСЛИ, может быть записана в какую-то ячейку Excel, либо же прямо вставлена в формулу. Вот тут-то и возникают некоторые особенности и сложности работы функции ЕСЛИ с датами.

Пример 1. Формула условия для дат с функцией ДАТАЗНАЧ (DATEVALUE)

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

В отличие от многих других функций Excel, ЕСЛИ не может распознавать даты и интерпретирует их как текст, как простые текстовые строки.

Поэтому вы не можете выразить свое логическое условие просто как  >«15.07.2019» или же >15.07.2019. Увы, ни один из приведенных вариантов не верен.

Чтобы функция ЕСЛИ распознала дату в вашем логическом условии именно как дату, вы должны обернуть ее в функцию ДАТАЗНАЧ (в английском варианте – DATEVALUE).

Например, ДАТАЗНАЧ(«15.07.2019»).

Полная формула ЕСЛИ может иметь следующую форму:

=ЕСЛИ(B2<ДАТАЗНАЧ(«10.09.2019″),»Поступил»,»Ожидается»)

Как показано на скриншоте, эта формула ЕСЛИ оценивает даты в столбце В и возвращает «Послупил», если дата поступления до 10 сентября. В противном случае формула возвращает «Ожидается».

Пример 2. Формула условия для дат с функцией СЕГОДНЯ()

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

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

К примеру, сегодня — 9 сентября 2019 года.

В столбце C отметим товар, который уже поступил. В ячейке C2 запишем:

=ЕСЛИ(B2<СЕГОДНЯ(),»Поступил»,»»)

В столбце D отметим товар, который еще не поступил. В ячейке D2 запишем:

=ЕСЛИ(B2<СЕГОДНЯ(),»»,»Ожидается»)

Пример 3. Расширенные формулы ЕСЛИ для будущих и прошлых дат

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

Выделим даты, отстоящие более чем на месяц от текущей, в прошлом. Укажем для них «Более месяца назад». Запишем это условие:

=ЕСЛИ(СЕГОДНЯ()-B2>30,»Более месяца назад»,»»)

Если условие не выполнено, то в ячейку запишем пустую строку «».

А для будущих дат, также отстоящих более чем на месяц, укажем «Ожидается».

=ЕСЛИ(B2-СЕГОДНЯ()>30,»Ожидается»,»»)

Если все результаты попробовать объединить в одном столбце, то придется составить выражение с несколькими вложенными функциями ЕСЛИ:

=ЕСЛИ(СЕГОДНЯ()-B2>30,»Более месяца назад», ЕСЛИ(B2-СЕГОДНЯ()>30,»Ожидается»,»»))

Примеры работы функции ЕСЛИ:

Сравнение ячеек в Excel — Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или без учета регистра. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству… Как проверить правильность ввода данных в Excel? — Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить… Функция ЕСЛИ: проверяем условия с текстом — Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. СодержаниеПроверяем условие для полного совпадения текста.ЕСЛИ + СОВПАДИспользование функции ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСКЕСЛИ… Визуализация данных при помощи функции ЕСЛИ — Функцию ЕСЛИ можно использовать для вставки в таблицу символов, которые наглядно показывают происходящие с данными изменения. К примеру, мы хотим показать в отдельной колонке таблицы, происходит рост или снижение продаж.…

5 1 голос

Рейтинг статьи

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

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

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

Способ 1: вычитание

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

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

В открывшемся окне слева выберите подходящий нам формат, а потом в основной области определитесь с типом: 14.03.12, 14 мар 12 или другой. Нажимайте «ОК».

В те ячейки, для которых только что поменяли формат, вводите данные. Я заполнила А1 и В1. Теперь нужно выбрать любую ячейку (D1), в которой установлен общий формат данных, иначе расчеты будут некорректные. Поставьте в нее «=» и нажмите сначала позднюю (В1) дату, потом раннюю (А1). Чтобы вычислить между ними количество дней, жмите «Enter».

Как видите, между 5 июня и 24 сентября 111 дней.

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

Для этого выделите ячейку, в которой будет результат (В3), и посмотрите, чтобы для нее был выбран общий формат.

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

«d» – количество дней;
«m» – количество полных месяцев;
«y» – количество полных лет;
«md» – посчитает дни без учета месяцев и годов;
«yd» – подсчет дней без учета только годов;
«ym» – посчитает месяцы не учитывая год.

Ставим в В3 знак равенства, пишем РАЗНДАТ и открываем скобку. Затем выделяем раннюю дату (А1), потом позднюю (В1), ставим в кавычках подходящую единицу и закрываем скобку.   Между всеми аргументами ставьте «;». Для расчета, нажмите «Enter».

У меня получилась такая формула:

=РАЗНДАТ(A1;B1;»d»)

Выбрав в качестве единицы «d», я получила результат – 111.

Если изменить данное значение, например, на «md», тогда формула посчитает разницу между 5 и 24 без учета месяцев и годов.

Меняя таким образом данный аргумент, получится вывести точный возраст человека. В одной ячейке будут года «y», второй месяцы «ym», третьей дни «md»

Способ 3: считаем рабочие дни

Чтобы посчитать именно рабочие дни за определенный период используется функция ЧИСТРАБДНИ().

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

Выделите ячейку, в которой будете считать, и выберите для нее общий числовой формат.

Дальше ставьте «=» и пишите функцию. У меня она выглядит так:

=ЧИСТРАБДНИ(A5;B5;C5)

В качестве аргументов указываем начальную дату (А5), потом конечную (В5). Последний аргумент – это праздники (С5). Разделяем их «;».

Нажав «Enter» появится результат, в примере ячейка D5 – 21 день.

Теперь рассмотрим, если в месяце несколько праздников. Например, в январе Новый год и Рождество. Выделяем ячейку (D6) и ставим в нее равно. Потом нажимаем в строке формул на букву «f». Откроется окно «Вставка функции». В поле «Категория» выберите «Полный алфавитный перечень» и найдите в списке нужную функцию. Жмите «ОК».

Дальше необходимо выбрать аргументы функции. В «Нач_дата» выбираем начальное значение (А6), в «Кон_дата» – конечное (В6). В последнем поле вписываем даты праздников в скобках {} и кавычках «». Потом нажимайте «ОК».

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

=ЧИСТРАБДНИ(A6;B6;{«01.01.17″;»07.01.17»})

Чтобы не прописывать праздники вручную, можно указать в соответствующем поле определенный диапазон. У меня это С6:С7.

Рабочие дни посчитаются, и функция будет иметь вид:

=ЧИСТРАБДНИ(A6;B6;C6:C7)

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

=ЧИСТРАБДНИ(A8;B8;C8)

В феврале получилось 19 рабочих дней.

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

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

---

Оценить статью: Загрузка… Поделиться новостью в соцсетях

 

Об авторе: Олег Каминский

Вебмастер. Высшее образование по специальности «Защита информации». Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности

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

Поскольку Excel хранит даты и время как числовые значения, вы можете добавлять или вычитать одно из другого. На рис. 94.1 показан простой пример, который отслеживает количество отработанных часов. Итоговое значение вычисляется в ячейке В8 с помощью следующей формулы (ячейка отформатирована с помощью числового формата [ч]:мм (см. статью 59 для получения более подробной информации)): =СУММ(В2:В6). Ячейка В9 использует следующую формулу для расчета времени, которое осталось отработать для завершения 40-часовой рабочей недели: =(40/24)-В8.

Если количество отработанных часов превышает 40, то формула в ячейке В9 возвращает отрицательное число. Но, как вы можете видеть на рис. 94.2, Excel не может справиться с отрицательным значением времени.

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

Одно из решений заключается в применении дополнительной системы дат 1904, которая задает серийный номер 1 дате 2 января 1904 года. Выберите Файл ► Параметры, чтобы открыть окно Параметры Excel. Перейдите в раздел Дополнительно и в области При пересчете этой книги установите флажок Использовать систему дат 1904. Отрицательные значения времени сейчас будут выводиться правильно, как показано на рис. 94.3.

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

Правильно введите даты в Excel с помощью функции DATE

Автор Глеб Захаров На чтение 4 мин. Просмотров 76 Опубликовано 10.07.2019

Используйте функцию ДАТА для ввода дат в формулы даты

Функция DATE в Excel возвращает дату или серийный номер даты, комбинируя отдельные элементы дня, месяца и года, введенные в качестве аргументов функции.

Например, если в ячейку рабочего листа введена следующая функция DATE:

 = ДАТА (2016,01,01) 

возвращается серийный номер 42370 , который относится к дате 1 января 2016 года.

Ввод дат как дат

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

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

Синтаксис и аргументы функции DATE

Синтаксис функции относится к макету функции и включает в себя имя функции, скобки и аргументы.

Синтаксис для функции DATE:

 = DATE (год, месяц, день) 

• Год (обязательно). Введите год длиной от одной до четырех цифр или укажите ссылку на ячейку для расположения данных на листе.
• Месяц (обязательно). Введите месяц года в виде положительного или отрицательного целого числа от 1 до 12 (с января по декабрь) или введите ссылку на ячейку для расположения данных.
• День (обязательно). Введите день месяца в виде положительного или отрицательного целого числа от 1 до 31 или укажите ссылку на ячейку для расположения данных.

Примечания по использованию

лет

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

Если значение, введенное для аргумента Year , находится в диапазоне от нуля до 1899, это значение добавляется к 1900 для определения года. Например, DATE (0,1,2) возвращает 2 января 1900 года (1900 + 0), а DATE (1899,12,31) возвращает 31 декабря 3799 (1900 + 1899). По этой причине всегда передавайте годы, используя четыре цифры, чтобы избежать ошибок – например, «2016», а не «16».

Если аргумент Year находится между 1900 и 9999, это значение вводится как год для полученной даты. Например, DATE (2016,1, 2) возвращает 2 января 2016 года. Если аргумент Year меньше 0 или больше 9999, Excel возвращает #NUM! значение ошибки.

Месяцы

Если значение, введенное для аргумента Месяц , больше 12, значение делится на 12, а число полных лет добавляется к аргументу Год . Например, DATE (2016,15,1) возвращает дату 1 марта 2017 г. (15–12 = 1 год + 3 месяца).

Если аргумент Month меньше 1, это число месяцев вычитается из текущей даты, чтобы дать более раннюю дату. Например, DATE (2016, -5,1) возвращает дату 1 июля 2015 года.

дней

Если значение, введенное для аргумента Day , превышает количество дней в указанном месяце, функция добавляет это количество дней к следующему месяцу. Например, DATE (2016,1,35) возвращает серийный номер, представляющий 4 февраля 2016 года, то есть через четыре дня после 31 января 2016 года.

Если аргумент Day меньше 1, Day вычитает это количество дней из предыдущего месяца. Например, DATE (2016,1, -15) возвращает серийный номер, представляющий 16 декабря 2015 г., то есть за 15 дней до 31 декабря 2015 г.

Изменение формата даты в Excel

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

Чтобы изменить формат даты:

1. Выделите ячейки на листе, которые содержат или будут содержать даты
2. Нажмите клавиши Ctrl + 1 , чтобы открыть диалоговое окно Формат ячеек .
3. Нажмите на вкладку Число в диалоговом окне.
4. Нажмите Дата в окне списка категорий.
5. В окне типа выберите нужный формат даты
6. Нажмите кнопку ОК , чтобы сохранить изменение формата и закрыть диалоговое окно.

Или щелкните правой кнопкой мыши выбранные ячейки, чтобы открыть контекстное меню, затем выберите в меню Формат ячеек , чтобы открыть диалоговое окно Формат ячеек .

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

Юлианские числа

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

Например, юлианский номер дня 2016007 состоит из семи цифр, при этом первые четыре цифры номера представляют год, а последние три – день года. Это число представляет седьмой день 2016 года или 7 января 2016 года.

Аналогичным образом число 2010345 представляет 345-й день 2010 года или 11 декабря 2010 года.

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

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

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

1. Введите сроки выполнения в столбце A.

2. org/ListItem»>

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

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

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

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

Для функции EDATE требуются два аргумента: дата начала и количество месяцев, которые вы хотите добавить или вычесть. Чтобы вычесть месяцы, введите в качестве второго аргумента отрицательное число. Например, = EDATE («15.09.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 г.

Добавить или вычесть годы из даты

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

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

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

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

• Функции MONTH и DAY возвращают только исходные значения из ячейки A2, но функция DATE требует их.

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

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

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

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

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

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

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

• Функция DAY возвращает 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 введите = EDATE (A1,16) .

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

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

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

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

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

Вы также можете просто сослаться на ячейку, содержащую значение даты, или используя формулу = EDATE (A1, -5) для того же результата.

Другие примеры

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

Дата начала	Годы добавлены или вычтены	Формула	Результат
24.10. 2013	3 (прибавьте 3 года)	= ДАТА (ГОД (A2) + B2, МЕСЯЦ (A2), ДЕНЬ (A2))	24.10.2016
24.10.2013	-5 (отнимите 5 лет)	= ДАТА (ГОД (A4) + B4, МЕСЯЦ (A4), ДЕНЬ (A4))	24.10.2008

Как вычесть даты в Excel

На главную »Basic-Excel» Вычесть-даты-в-Excel

Даты в Excel

Если вы хотите вычесть даты в Excel, это помогает понять, как Excel сохраняет даты.

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

Система нумерации, используемая для дат Excel, различается в зависимости от компьютерных систем, но чаще всего используется следующая система нумерации:

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

Т.е. для вычитания дат в Excel:

1. Введите дату начала и дату окончания в свою электронную таблицу;
2. Вычтите дату начала из даты окончания;

3. Убедитесь, что результат отформатирован как число.

   (Самый простой способ сделать это — выделить ячейку, а затем выбрать формат «Общий» в раскрывающемся меню на вкладке Home на ленте Excel).

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

Примеры вычитания даты в Excel

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

Формулы: 9023 9023 9023 9023 18 января 2011 г. A B C 1 Дата начала Дата окончания Разница Разница = B2-A2 3 19 ноября 1986 г. 26 ноября 2020 г. = B3-A3 4 09 марта 2011 г. 23 июня 2012 г. = B4-A4

Результаты: 09 -Март-2011 A B C 1 Дата начала окончания Дата начала Разница 2 03 января 2011 года 18 января 2011 года 15 3 19 ноября 1986 года 26 ноября 2020 года 12426 4 2 3 июня 2012 г. 472

Обратите внимание, что в приведенной выше таблице результатов ячейки в столбцах A и B отформатированы в формате даты, но ячейки в столбце C отформатированы с ‘формат.

Изменение форматирования ячейки Excel

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

Самый простой способ сделать это — выбрать параметр форматирования Общее в раскрывающемся меню формата на ленте Excel. Обычно он находится в группе «Число» на вкладке Home ленты (см. Ниже):

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

См. Также Как вычесть время в Excel >>

Вернуться к Basic Excel Page
Вернуться к ExcelFunctions.net Домашняя страница

Описание всех функций времени и даты в Excel [5500 слов]

Используйте функцию РАБДЕНЬ для вычисления количества рабочих дней между двумя датами

Еще одна полезная функция — это функция РАБДЕНЬ.

Эта функция возвращает день, который представляет собой некоторое количество рабочих дней в будущем или до некоторой даты. «Рабочий день» — это дни, исключая выходные и любые указанные праздничные дни.

Функция требует двух аргументов и имеет дополнительный третий.

= РАБДЕНЬ (начальная_дата, дни, [праздники])

• начальная_дата — этот обязательный аргумент является датой, с которой вы хотите подсчитать количество рабочих дней
• дней — этот обязательный аргумент — количество дней от даты начала, которое вы хотите подсчитать. Использование отрицательного числа даст вам дату на много рабочих дней раньше даты начала.
• праздники — этот аргумент не является обязательным.Это позволяет вам добавлять праздничные даты в формулу для функции РАБДЕНЬ, чтобы пропускать их вместе с выходными.

Во-первых, обратите внимание, что мы указали 3 праздничные даты в ячейках A8, A9 и A10. Мы будем использовать их в наших примерах, чтобы исключить аргумент «праздничные дни» вместе с выходными.

В строке 2 мы хотим узнать дату, равную 90 рабочим дням с понедельника, 14 ноября 2016 г.

За исключением выходных и праздничных дней (A8: A10), это будет вторник, 21 марта 2017 г., согласно WORKDAY функция.

В строке 3 мы можем сразу увидеть влияние выходных и праздничных дней на результат нашей формулы. Нам нужна дата, равная 2 рабочим дням со среды, 23 ноября 2017 г.

Из-за выходного дня в четверг, 24 ноября, и выходных дней в субботу и воскресенье, результатом будет понедельник, 28 ноября 2016 г.

Третий Пример на рисунке показывает, как использовать РАБДЕНЬ, чтобы найти дату до даты начала.

Мы хотим знать дату за 20 рабочих дней до понедельника, 17 января 2017 года.

Итак, наш аргумент ДЕНЬ должен быть отрицательным значением (-20).

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

Как использовать функцию ДАТА в Excel

Ядром этой формулы является функция ДАТА, которая автоматически корректирует значения месяца и года, выходящие за пределы допустимого диапазона. В формуле год передается в дату без изменений, а 2 — за месяц (февраль) и…

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

Функция ДАТА создает правильную дату Excel с заданными значениями года, месяца и дня. Затем нужно просто сравнить каждую дату в диапазоне с датой, созданной с помощью DATE.Ссылка B4 полностью относительна, …

Функция СЧЁТЕСЛИМН создана для подсчета ячеек, соответствующих нескольким критериям. В этом случае нам нужно предоставить два критерия: один критерий для более ранней даты и один для более поздней даты. Поставляем указанные диапазоны дат (…

Эта формула строит окончательный результат из двух частей, соединенных конкатенацией с амперсандом (…

Функция И принимает несколько аргументов и возвращает ИСТИНА только тогда, когда все аргументы возвращают ИСТИНА.Функция ДАТА создает правильную дату Excel с заданными значениями года, месяца и дня. Поскольку ссылка на B4 полностью …

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

Функция РАЗНДАТ (Дата + Разница) — некоторая аномалия в Excel.Функция совместимости, изначально пришедшая из Lotus 1-2-3, Excel не помогает предоставлять аргументы при вводе функции. Однако РАЗНДАТ …

Функция И принимает несколько аргументов и возвращает ИСТИНА только тогда, когда все аргументы возвращают ИСТИНА. Даты — это просто порядковые номера в Excel, поэтому более ранние даты всегда меньше более поздних. В приведенной выше формуле любые даты …

Правила проверки данных срабатывают, когда пользователь добавляет или изменяет значение ячейки.Функция И принимает несколько аргументов (логических значений) и возвращает ИСТИНА, только если все аргументы возвращают ИСТИНА. Функция ДАТА создает …

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

Функция СУММЕСЛИМН позволяет использовать логические операторы (т.е.е. «=», «>», «> =» и т. д.) и несколько критериев. Чтобы сопоставить целевые значения между двумя датами, нам нужно использовать два критерия. Функция СУММЕСЛИМН требует, чтобы каждый критерий был . ..

Работая изнутри, функции ГОД, МЕСЯЦ и ДЕНЬ извлекают соответствующие компоненты даты: = ГОД (B5) // 1960 = МЕСЯЦ (B5) // 3 = ДЕНЬ (B5) // 8 На внешнем уровне функция ДАТА просто повторно собирает …

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

Метка времени Unix отслеживает время как текущее количество секунд. Отсчет начинается в «эпоху Unix» 1 января 1970 года, поэтому метка времени Unix — это просто общее количество секунд между любой заданной датой и эпохой Unix. С …

Получить номер недели в Excel и сбросить для каждого месяца

Использование функции WEEKDAY () для сброса номера недели на 1 каждый раз, когда наступает первый понедельник следующего месяца

Существует формула, которая дает дату в предыдущий понедельник.

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

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

Для начала возьмем 1 января 2017 г. и внесем соответствующие изменения.

Сначала получите день недели, связанный с этой датой.

 Ячейка D6 = WEEKDAY (B6,3) 

• Используйте return_type = 3, где понедельник равен 0, а воскресенье — 6.

Вы получите 0 за 1 января.

Перетащите эту формулу вниз.

3 января, то есть вторник, счетчик возвращает 1.

Это означает, что до понедельника осталось 1 день.

В то время как 8 января (воскресенье) возвращает 6, поскольку до понедельника осталось 6 дней.

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

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

Возвращаясь к 2 января (понедельник), подумайте, что вы хотите отобразить.

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

Для 2 января вы хотите, чтобы он возвращал 2 для всей недели, а для недели, начинающейся 9 января, вы хотите, чтобы он возвращал 9 для включительных дат.

Для этого используйте функцию ДЕНЬ () в Excel.

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

 Ячейка D6 = ДЕНЬ (B6-WEEKDAY (B6,3)) 

Для 2 января формула дает 2-0 и возвращает 2.

3 января возвращает 3-1, возвращает 2 и т. Д.

До этого момента вы будете получать одно и то же число за каждую неделю.

Используйте эти результаты и преобразуйте их в недели с 1 по 5 для каждого месяца.

Есть много способов сделать это.

Один из способов — заключить это в формулу WEEKNUM ().

Используйте формулу WEEKNUM () даты и аргумент DAY (), но будьте осторожны, чтобы смотреть на тот же месяц и тот же год.

Для формулы WEEKNUM (), поскольку вы разделите ее и выделите DAY () отдельно, вместо нее следует использовать функцию DATE ():

 Ячейка D6 = WEEKNUM (DATE (YEAR (B6), 1, DAY (B6-WEEKDAY (B6,3)))) 

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

Перетащите эту формулу вниз.

Вы заметите, что для недель, разделенных между двумя месяцами, таких как неделя с 31 января (среда) и 1 февраля (четверг), возвращаемые значения верны.

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

Используйте return_type = 2, где понедельник — 1, а воскресенье — 7.

Если день недели для даты a больше 5, что означает, что это суббота или воскресенье, то на нем ничего не должно отображаться.