Excel дата в формуле

Функция ДАТА() в MS EXCEL

Функция ДАТА() , английский вариант DATE(), возвращает целое число, представляющее определенную дату. Формула =ДАТА(2011;02;28) вернет число 40602. Если до ввода этой формулы формат ячейки был задан как Общий, то результат будет отформатирован как дата, т.е. 28.02.2011.

Синтаксис функции

ДАТА(год;месяц;день)

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

В EXCEL даты хранятся в виде последовательности чисел (1, 2, 3, . ), что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 г. имеет номер 1, а 28 февраля 2011 г. — номер 40602, так как интервал между этими датами составляет 40 602 дня. О том как EXCEL хранит дату и время, читайте эту статью.

Для того, чтобы прибавить к дате 28.02.2011, содержащейся в ячейке А1, например, 5 лет, можно использовать следующую формулу:
=ДАТА(ГОД(A1)+5;МЕСЯЦ(A1);ДЕНЬ(A1))
получим результат 28.02.2016

Для того, чтобы прибавить к дате 28.02.2011, например, 15 месяцев, можно использовать следующую формулу:
=ДАТА(ГОД(A1);МЕСЯЦ(A1)+15;ДЕНЬ(A1)) или формулу =ДАТАМЕС(A1;15)
получим результат 28.05.2012

Примечание. При прибавлении месяцев между ДАТАМЕС() и ДАТА() существует разница. Прибавим к 30.01.2009 один месяц:

  • =ДАТАМЕС(«30.01.2009»;1) вернет 28.02.2009, т.к. 30 февраля не существует, то функция вернет последний день месяца, т.е. 28.02.2009 («лишние» 2 дня будут отброшены);
  • =ДАТА(ГОД(«30.01.2009»);МЕСЯЦ(«30.01.2009»)+1;ДЕНЬ(«30.01.2009»)) вернет 02.03.2009: «лишние» 2 дня (29 и 30 февраля) будут прибавлены к дате.

Если записать формулу =ДАТА(2008;3;) , то формула вернет 29.02.2008. Т.е. опуская последний аргумент День мы тем самым задаем его равным 0. Поэтому формула возвращает последний день предыдущего месяца!

10 популярных функций даты и времени в Microsoft Excel

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

Работа с функциями даты и времени

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

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

  1. Для введения формулы через Мастер функций выделите ячейку, где будет выводиться результат, а затем сделайте щелчок по кнопке «Вставить функцию». Расположена она слева от строки формул.

После этого происходит активация Мастера функций. Делаем клик по полю «Категория».

Из открывшегося списка выбираем пункт «Дата и время».

Кроме того, Мастер функций можно активировать, выделив ячейку на листе и нажав комбинацию клавиш Shift+F3. Существует ещё возможность перехода во вкладку «Формулы», где на ленте в группе настроек инструментов «Библиотека функций» следует щелкнуть по кнопке «Вставить функцию».

Имеется возможность перемещения к окну аргументов конкретной формулы из группы «Дата и время» без активации главного окна Мастера функций. Для этого выполняем перемещение во вкладку «Формулы». Щёлкаем по кнопке «Дата и время». Она размещена на ленте в группе инструментов «Библиотека функций». Активируется список доступных операторов в данной категории. Выбираем тот, который нужен для выполнения поставленной задачи. После этого происходит перемещение в окно аргументов.

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

Его аргументами являются «Год», «Месяц» и «День». Особенностью обработки данных является то, что функция работает только с временным отрезком не ранее 1900 года. Поэтому, если в качестве аргумента в поле «Год» задать, например, 1898 год, то оператор выведет в ячейку некорректное значение. Естественно, что в качестве аргументов «Месяц» и «День» выступают числа соответственно от 1 до 12 и от 1 до 31. В качестве аргументов могут выступать и ссылки на ячейки, где содержатся соответствующие данные.

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

Близки к этой функции по значению операторы ГОД, МЕСЯЦ и ДЕНЬ. Они выводят в ячейку значение соответствующее своему названию и имеют единственный одноименный аргумент.

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

Из контекста понятно, что в качестве аргументов «Начальная дата» и «Конечная дата» выступают даты, разницу между которыми нужно вычислить. А вот в качестве аргумента «Единица» выступает конкретная единица измерения этой разности:

  • Год (y);
  • Месяц (m);
  • День (d);
  • Разница в месяцах (YM);
  • Разница в днях без учета годов (YD);
  • Разница в днях без учета месяцев и годов (MD).

В отличии от предыдущего оператора, формула ЧИСТРАБДНИ представлена в списке Мастера функций. Её задачей является подсчет количества рабочих дней между двумя датами, которые заданы как аргументы. Кроме того, имеется ещё один аргумент – «Праздники». Этот аргумент является необязательным. Он указывает количество праздничных дней за исследуемый период. Эти дни также вычитаются из общего расчета. Формула рассчитывает количество всех дней между двумя датами, кроме субботы, воскресенья и тех дней, которые указаны пользователем как праздничные. В качестве аргументов могут выступать, как непосредственно даты, так и ссылки на ячейки, в которых они содержатся.

Синтаксис выглядит таким образом:

Оператор ТДАТА интересен тем, что не имеет аргументов. Он в ячейку выводит текущую дату и время, установленные на компьютере. Нужно отметить, что это значение не будет обновляться автоматически. Оно останется фиксированным на момент создания функции до момента её перерасчета. Для перерасчета достаточно выделить ячейку, содержащую функцию, установить курсор в строке формул и кликнуть по кнопке Enter на клавиатуре. Кроме того, периодический пересчет документа можно включить в его настройках. Синтаксис ТДАТА такой:

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

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

Основной задачей функции ВРЕМЯ является вывод в заданную ячейку указанного посредством аргументов времени. Аргументами этой функции являются часы, минуты и секунды. Они могут быть заданы, как в виде числовых значений, так и в виде ссылок, указывающих на ячейки, в которых хранятся эти значения. Эта функция очень похожа на оператор ДАТА, только в отличии от него выводит заданные показатели времени. Величина аргумента «Часы» может задаваться в диапазоне от 0 до 23, а аргументов минуты и секунды – от 0 до 59. Синтаксис такой:

Читать еще:  Abs excel функция

Кроме того, близкими к этому оператору можно назвать отдельные функции ЧАС, МИНУТЫ и СЕКУНДЫ. Они выводят на экран величину соответствующего названию показателя времени, который задается единственным одноименным аргументом.

Функция ДАТАЗНАЧ очень специфическая. Она предназначена не для людей, а для программы. Её задачей является преобразование записи даты в обычном виде в единое числовое выражение, доступное для вычислений в Excel. Единственным аргументом данной функции выступает дата как текст. Причем, как и в случае с аргументом ДАТА, корректно обрабатываются только значения после 1900 года. Синтаксис имеет такой вид:

Задача оператора ДЕНЬНЕД – выводить в указанную ячейку значение дня недели для заданной даты. Но формула выводит не текстовое название дня, а его порядковый номер. Причем точка отсчета первого дня недели задается в поле «Тип». Так, если задать в этом поле значение «1», то первым днем недели будет считаться воскресенье, если «2» — понедельник и т.д. Но это не обязательный аргумент, в случае, если поле не заполнено, то считается, что отсчет идет от воскресенья. Вторым аргументом является собственно дата в числовом формате, порядковый номер дня которой нужно установить. Синтаксис выглядит так:

Предназначением оператора НОМНЕДЕЛИ является указание в заданной ячейке номера недели по вводной дате. Аргументами является собственно дата и тип возвращаемого значения. Если с первым аргументом все понятно, то второй требует дополнительного пояснения. Дело в том, что во многих странах Европы по стандартам ISO 8601 первой неделей года считается та неделя, на которую приходится первый четверг. Если вы хотите применить данную систему отсчета, то в поле типа нужно поставить цифру «2». Если же вам более по душе привычная система отсчета, где первой неделей года считается та, на которую приходится 1 января, то нужно поставить цифру «1» либо оставить поле незаполненным. Синтаксис у функции такой:

Оператор ДОЛЯГОДА производит долевой расчет отрезка года, заключенного между двумя датами ко всему году. Аргументами данной функции являются эти две даты, являющиеся границами периода. Кроме того, у данной функции имеется необязательный аргумент «Базис». В нем указывается способ вычисления дня. По умолчанию, если никакое значение не задано, берется американский способ расчета. В большинстве случаев он как раз и подходит, так что чаще всего этот аргумент заполнять вообще не нужно. Синтаксис принимает такой вид:

Мы прошлись только по основным операторам, составляющим группу функций «Дата и время» в Экселе. Кроме того, существует ещё более десятка других операторов этой же группы. Как видим, даже описанные нами функции способны в значительной мере облегчить пользователям работу со значениями таких форматов, как дата и время. Данные элементы позволяют автоматизировать некоторые расчеты. Например, по введению текущей даты или времени в указанную ячейку. Без овладения управлением данными функциями нельзя говорить о хорошем знании программы Excel.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Функция ДАТА — как использовать формулу ДАТА в Excel

Функция ДАТА возвращает число, представляющее дату определенного дня, месяца и года. Функция дата используется, как правило, для построения даты из составных частей, таких как день, месяц и год. Минимальное и максимальное значение дат, которое может принять Excel — 1 января 1900 года и 31 декабря 9999 года соответственно.

Синтаксис функции ДАТА

Формула ДАТА состоит из трех частей:

ДАТА(год; месяц; день)

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

Месяц – второй аргумент, который указывает, какой месяц будет фигурировать в нашей дате. Обратите внимание, что значения месяца не ограничены рамками от 1 до 12, а могут принимать любые значения, даже отрицательные. К примеру, если вы напишите формулу =ДАТА(2013;13;1), Excel вернет дату, которая отстоит от 1-го января 2013 года на 13 месяцев, т.е. 1/1/2014.

День – часть функции, которая задает день необходимой даты. Так же, как и аргумент Месяц, может принимать любые значения (не только от 1 до 31). К примеру, если вы введете формулу =ДАТА(2013;01;35), Excel вернет дату, которая отстоит от 1-го января 2013 года на 35 дней, т.е. 4/02/2013. Точно так же вы можете использовать отрицательные значения, чтобы создавать прошедшие даты.

Давайте рассмотрим пример использования функции ДАТА. Предположим, что у нас имеется год, месяц и день, находящиеся в трех различных полях. Необходимо объединить все три значения для создания даты. Как показано выше, вводим формулу =ДАТА(A2;B2;C2). Результатомм в данном случае будет дата 19-го июня 2013 года.

Формат даты в Excel

Каждый раз, когда вы презентуете данные содержащие даты, убедитесь, что они отформатированы должным образом. Существует целый ряд форматов дат, которые используется каждым отделом, компанией или страной по-разному. Лично я предпочитаю формат ДД-MMM-ГГГГ, который исключает двусмысленности.

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

Возможные ошибки формул ДАТА

Функция дата может вернуть следующие ошибки:

Ошибка #ЧИСЛО! формулы ДАТА выдается, когда введенная дата выходит за границы допустимых значений (с 0/1/1900 по 31/12/9999). К примеру, если вы хотели ввести год 2013, но по ошибке ввели 20013, скажем =ДАТА(20013;6;19), Excel вернет ошибку.

Ошибка #ИМЯ! формулы ДАТА возвращается, когда один из аргументов введен не корректно (ввод текста вместо числового значения). К примеру, вы ввели «июнь» как аргумент месяц, вместо 6 (порядковый номер месяца июнь) и ввели следующую формулу =ДАТА(2013; июнь; 19). В этом случае Excel вернет ошибку #ИМЯ!

Вам также могут быть интересны следующие статьи

4 комментария

Можно ли сздать формулу дата не используя в ней информацию из других ячеек (=ДАТА(A2;B2;C2)), а используя в ней числа которые не ссылаються на другие ячейки (=ДАТА(2014;08;26))?

или обязательно нужны данные из других ячеек?

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

А как преобразовать в формат гггг-мм-дд уже внесенные даты рождения (20 тыс шт) в формате дд.мм.гггг ? На установку формата ячеек, в т.ч. и с установкой фолрмата системного времени через часы) не реагирует. Информация нужна срочно.
28/05/2015

ДАТА (функция ДАТА)

С помощью функции ДАТА в Excel можно объединить три отдельных значения в дату.

Функция ДАТА возвращает порядковый номер определенной даты.

Аргументы функции ДАТА описаны ниже.

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

Совет: Во избежание непредвиденных результатов используйте в качестве значения аргумента год четыре цифры. Например, значение «07» может интерпретироваться как «1907» или «2007». Четырехзначное обозначение года предотвращает возникновение путаницы.

Если аргумент год находится в диапазоне от 0 (нуль) до 1899 (включительно), Excel для вычисления года прибавляет это значение к числу 1900. Например, функция ДАТА(108;1;2) возвращает 2 января 2008 (1900+108) года.

Если аргумент год находится в диапазоне от 1900 до 9999 (включительно), Excel использует это значение как год. Например, функция ДАТА(2008;1;2) возвращает 2 января 2008 года.

Читать еще:  Формулы в сводной таблице в excel

Если значение аргумента год меньше 0 или больше 9999, Excel возвращает значение ошибки #ЧИСЛО!.

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

Если значение аргумента месяц больше 12, аргумент месяц добавляет число месяцев к первому месяцу указанного года. Например, функция ДАТА(2008;14;2) возвращает число, соответствующее 2 февраля 2009 года.

Если значение аргумента месяц меньше 1, аргумент месяц вычитает значение этого числа месяцев, увеличенное на 1, из первого месяца указанного года. Например, функция ДАТА(2008;-3;2) возвращает число, соответствующее 2 сентября 2007 года.

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

Если значение аргумента день больше числа дней в указанном месяце, аргумент день добавляет это число дней к первому дню месяца. Например, функция ДАТА(2008;1;35) возвращает число, соответствующее 4 февраля 2008 года.

Если значение аргумента день меньше 1, аргумент день вычитает значение этого числа дней, увеличенное на 1, из первого дня указанного месяца. Например, функция ДАТА(2008;1;-15) возвращает число, соответствующее 16 декабря 2007 года.

Примечание: В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. Дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 447 дней. Чтобы вывести правильную дату, нужно изменить формат ячейки.

Например, =ДАТА(C2;A2;B2) объединяет значение года из ячейки C2, месяца из ячейки A2 и дня из ячейки B2 и помещает их в одну ячейку в виде даты. В примере ниже показан результат в ячейке D2.

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

Щелкните правой кнопкой мыши ячейки, которые вы хотите изменить. На компьютере Mac щелкните ячейки, удерживая нажатой клавишу CTRL.

На вкладке Главная щелкните Формат > Формат ячеек или нажмите клавиши CTRL+1 (COMMAND+1 на компьютере Mac).

3. Выберите нужный язык (местоположение) и формат даты.

Дополнительные сведения о форматировании дат см. в статье Пользовательское форматирование даты.

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

Функция ДАТА формирует дату.

Функция ГОД получает значение «2012» из ячейки C2.

К этому значению прибавляется пять лет («+5»), после чего в ячейке D2 выводится «2017».

Функция МЕСЯЦ получает значение «3» из ячейки C2 и добавляет его в ячейку D2 в качестве значения месяца.

Функция ДЕНЬ получает значение «14» из ячейки C2 и добавляет его в ячейку D2 в качестве значения дня.

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

Функция ДАТА формирует дату.

Функция ЛЕВСИМВ получает первые 4 знака слева в ячейке C2. При этом значение «2014» становится годом преобразованной даты в ячейке D2.

Функция ПСТР ссылается на ячейку C2. Она получает 2 знака справа от 5-го знака. При этом значение «03» становится месяцем преобразованной даты в ячейке D2. «0» не включается в результат из-за формата Дата, заданного для ячейки D2.

Функция ПРАВСИМВ получает первые 2 знака в ячейке C2 от правого края и добавляет «14» в ячейку D2 в качестве значения дня.

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

В приведенном ниже примере ячейка A5 содержит дату, которую нужно увеличить или уменьшить на 7 дней (значение в ячейке C5).

Как складывать и вычитать даты, дни, недели, месяцы и годы в 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. Что именно выбрать зависит от того, какие единицы времени требуется прибавить или вычесть.

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

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

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

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

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

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

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

=ДАТА(2015;5;6)+10
=DATE(2015,5,6)+10
Обращением к другой функции. Например, чтобы прибавить несколько дней к текущей дате, используйте функцию СЕГОДНЯ (TODAY):

Следующий рисунок показывает действие этих формул. На момент написания текста текущей датой было 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 недели из сегодняшней даты, используйте эту формулу:

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

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

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

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

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

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

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

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

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

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

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

=ДАТА(ГОД(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 месяцев от сегодняшней даты

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

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

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

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

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

=ДАТА(ГОД(A2)+5;МЕСЯЦ(A2);ДЕНЬ(A2))
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))
Вычитаем 5 лет из даты, указанной в ячейке A2:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector