Функция 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 по пути 🙂 Мастер может выполнять следующие вычисления: Например, вот как можно добавить 4 разные единицы в ячейках B3:E3 к дате в A4. Формула в B4 строится в режиме реального времени по мере изменения условий: Если вам интересно изучить другие возможности мастера, не стесняйтесь загрузить ознакомительную версию Ultimate Suite ниже, которая включает в себя это, а также еще 60 надстроек для Excel, экономящих время. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе! Ultimate Suite 14-дневная полнофункциональная версия (файл .exe)Как сделать формулы даты в Excel с мастером даты и времени
Доступные загрузки
Учебники по датам в Excel: