Функция Excel DATE с примерами формул для расчета дат

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

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

Функция ДАТА Excel

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

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

Где:

Год – представляет год даты.

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

День – целое число, соответствующее дню месяца, от 1 до 31.

Функция ДАТА доступна во всех версиях Excel 365 — Excel 2007.

Функция ДАТА – советы и примечания

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

Год

Excel интерпретирует аргумент года в соответствии с системой дат, установленной на вашем компьютере. По умолчанию Microsoft Excel для Windows использует систему 1900, где 1 января 1900 года представлен порядковым номером 1. Дополнительные сведения см. в разделе Формат даты Excel.

• Если аргумент года находится в диапазоне от 1900 до 9999 включительно, Excel использует именно то значение, которое вы указали для создания даты. Например, ДАТА(2015, 12, 31) возвращает 31 декабря 2015 года.
• Если аргумент года находится в диапазоне от 0 до 1899 включительно, Excel добавляет указанное число к 1900. Например, ДАТА (100, 12, 31) возвращает 31 декабря 2000 г. (1900 + 100).
• Если аргумент года меньше 0 или больше 9999, формула ДАТА вернет #ЧИСЛО! ошибка.

Кончик. Чтобы избежать путаницы, всегда указывайте год из четырех цифр. Например, если вы введете «01» или «1» в качестве аргумента года, ваша формула ДАТА вернет 1901 год.

Месяц

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

День

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

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

Примеры формул Excel ДАТА

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

Пример 1. Формула DATE для возврата порядкового номера даты

Это наиболее очевидное использование функции ДАТА в Excel.

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

=ДАТА(2015, 5, 20)

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

=ДАТА(ГОД(СЕГОДНЯ()), 1, 1)

И эта формула выводит порядковый номер первого дня текущего месяца в текущем году:

=ДАТА(ГОД(СЕГОДНЯ()), МЕСЯЦ(СЕГОДНЯ(), 1)

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

Пример 2. Как вернуть дату на основе значений в других ячейках

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

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

=ДАТА(A2, A3, A4)

Пример 3. Формула DATE для преобразования строки или числа в дату

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

=ДАТА(ПРАВО(A2,4), СРЕДНЯЯ(A2,3,2), ЛЕВАЯ(A2,2))

Пример 4. Сложение и вычитание дат в Excel

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

• Добавление дней к дате: = ДАТА (2015, 5, 20) + 15 Формула добавляет 15 дней к 20 мая 2015 г. и возвращает 4 июня 2015 г.
• Вычитание дней из даты:=ДАТА(2015, 5, 20) – 15Результатом приведенной выше формулы является 5 мая 2015 г., то есть 20 мая 2015 г. минус 15 дней.
• Чтобы вычесть дату из сегодняшней даты: =СЕГОДНЯ()-ДАТА(2015,5,20) Формула вычисляет, сколько дней находится между текущей датой и какой-либо другой датой, которую вы укажете.

Если вы добавляете или вычитаете две даты, хранящиеся в некоторых ячейках, формула будет такой же простой, как =A1+B1 или A1-B1 соответственно.

Для получения дополнительной информации см.:

Расширенные формулы Excel DATE

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

Использование формул Excel DATE в условном форматировании

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

Предположим, у вас есть список дат в столбце A, и вы хотите закрасить даты, которые произошли ранее 1 мая 2015 года, оранжевым цветом, а те, которые произошли после 31 мая 2015 года, — зеленым.

Формулы DATE, которые вам нужны, следующие:

Оранжевый: =$A2

Зеленый: =$A2>DATE(2015, 5, 31) — выделяет даты старше 31 мая 2015 г.

Подробные инструкции и другие примеры формул см. в разделе Как условно форматировать даты в Excel.

Как сделать формулы даты в Excel с мастером даты и времени

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

А пока я хотел бы представить вам наш мастер даты и времени – быстрый и простой способ расчета дат в Excel. Прелесть этого инструмента в том, что он выводит результаты в виде формул, а не значений. Таким образом, у вас есть своего рода возможность «двух зайцев, один камень» – получить результат быстрее и изучить функции даты Excel по пути 🙂

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

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

Например, вот как можно добавить 4 разные единицы в ячейках B3:E3 к дате в A4. Формула в B4 строится в режиме реального времени по мере изменения условий:

Если вам интересно изучить другие возможности мастера, не стесняйтесь загрузить ознакомительную версию Ultimate Suite ниже, которая включает в себя это, а также еще 60 надстроек для Excel, экономящих время.

Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Доступные загрузки

Ultimate Suite 14-дневная полнофункциональная версия (файл .exe)

Учебники по датам в Excel: