Функция Excel YEAR – преобразование даты в год и вычисление возраста по дате рождения
В этом учебном пособии объясняется синтаксис и использование функции Excel ГОД, а также приводятся примеры формул для извлечения года из даты, преобразования даты в месяц и год, вычисления возраста из даты рождения и определить високосные годы.
В нескольких недавних сообщениях мы исследовали различные способы вычисления даты и времени в Excel и изучили множество полезных функций, таких как ДЕНЬ НЕДЕЛИ, ДЕНЬ, МЕСЯЦ и НОМЕР НЕДЕЛИ. Сегодня мы сосредоточимся на более крупной единице времени и поговорим о подсчете лет в ваших листах Excel.
В этом уроке вы узнаете:
Функция ГОД в Excel
Функция ГОД возвращает год из четырех цифр, соответствующий указанной дате, в виде целого числа от 1900 до 9999.
Синтаксис функции ГОД в Excel максимально прост:
ГОД(серийный_номер)
Где серийный_номер — любая действительная дата года, которую вы хотите найти. В формулах Excel YEAR вы можете указывать даты несколькими способами:
- Использование ДАТЫ Например, следующая формула возвращает год для 28 апреля 2015 года:
=ГОД(ДАТА(2015,4,28)) - Как порядковый номер, представляющий дату (дополнительную информацию о том, как даты хранятся в Excel, см. в разделе Формат даты Excel). 28 апреля 2015 года хранится как 42122, поэтому вы можете ввести это число непосредственно в формулу:
=ГОД(42122)Хотя вы можете ввести дату в виде порядкового номера, этот метод не рекомендуется, поскольку нумерация даты может различаться в разных системах.
- В качестве ссылки на ячейку, например =ГОД(A1)
- В результате какой-то другой формулы. Например, вы можете использовать функцию TODAY() для извлечения года из текущей даты:
=ГОД(СЕГОДНЯ())
Простые формулы ГОД могут даже понимать даты, введенные в виде текста, например =ГОД(“28 апреля-2015”). Однако корпорация Майкрософт не гарантирует правильных результатов, если дата предоставляется в виде текстового значения.
На следующем снимке экрана показаны все вышеперечисленные формулы ГОД в действии, и все они возвращают 2015 год, как и следовало ожидать 🙂
Как преобразовать дату в год в Excel
Когда вы работаете с информацией о дате в Excel, ваши рабочие листы обычно отображают полные даты, включая месяц, день и год. Однако для важных вех и важных событий, таких как запуск продукта или приобретение активов, вы можете просмотреть только год без повторного ввода или изменения исходных данных. Ниже вы найдете 3 быстрых способа сделать это.
Пример 1. Извлечение года из даты с помощью функции ГОД
На самом деле вы уже знаете, как использовать функцию ГОД в Excel для преобразования даты в год. На скриншоте выше показана куча формул, и вы можете увидеть еще несколько примеров на скриншоте ниже. Обратите внимание, что функция YEAR прекрасно понимает даты во всех возможных форматах:
Пример 2. Преобразование даты в месяц и год в Excel
Чтобы преобразовать заданную дату в год и месяц, вы можете использовать функцию ТЕКСТ для извлечения каждой единицы по отдельности, а затем объединить эти функции в одной формуле.
В функции ТЕКСТ вы можете использовать разные коды для месяцев и лет, например:
- «ммм» — сокращенные названия месяцев, как янв — дек.
- “мммм” – полные названия месяцев, как январь – декабрь.
- “гг” – 2-значный год
- “гггг” – 4-значный год
Чтобы сделать вывод более читабельным, вы можете разделить коды запятой, дефисом или любым другим символом, как показано ниже. Дата в месяц и год формулы:
=ТЕКСТ(B2, “мммм”) & “, ” & ТЕКСТ(B2, “гггг”)
Или же
=ТЕКСТ(B2, “ммм”) & “-” & ТЕКСТ(B2, “гг”)
Где B2 — ячейка, содержащая дату.
Пример 3. Отображение даты как года
Если на самом деле не имеет значения, как даты хранятся в вашей книге, вы можете заставить Excel показывать только годы, не изменяя исходные даты. Другими словами, вы можете хранить полные даты в ячейках, но отображать только годы.
В этом случае формула не нужна. Вы просто открываете Формат ячеек диалоговом окне, нажав Ctrl + 1, выберите Обычай категория на Число вкладку и введите один из приведенных ниже кодов в Тип коробка:
- yy – для отображения двухзначного года, как 00 – 99.
- yyyy – для отображения 4-значного года, как 1900 – 9999.
Помните, что этот метод не изменяет исходную дату, он только изменяет способ отображения даты на вашем листе. Если вы ссылаетесь на такие ячейки в своих формулах, Microsoft Excel будет выполнять вычисления даты, а не года.
Вы можете найти более подробную информацию об изменении формата даты в этом руководстве: Как изменить формат даты в Excel.
Как рассчитать возраст по дате рождения в Excel
Есть несколько способов рассчитать возраст по дате рождения в Excel – с помощью функции РАЗНДАТ, ДОЛЯ ГОДА или ЦЕЛОЕ в сочетании с СЕГОДНЯ(). Функция СЕГОДНЯ предоставляет дату для расчета возраста, гарантируя, что ваша формула всегда будет возвращать правильный возраст.
Рассчитать возраст по дате рождения (DOB) в годах
Традиционный способ рассчитать возраст человека в годах — вычесть дату рождения из текущей даты. Этот подход отлично работает в повседневной жизни, но аналогичная формула расчета возраста в Excel не совсем верна:
INT((СЕГОДНЯ()-Дата рождения)/365)
Первая часть формулы (СЕГОДНЯ()-B2) вычисляет разницу в днях, и вы делите ее на 365, чтобы получить количество лет. В большинстве случаев результатом этого уравнения является десятичное число, и у вас есть функция INT округлить его до ближайшего целого числа.
Предполагая, что дата рождения находится в ячейке B2, полная формула выглядит следующим образом:
=ЦЕЛОЕ((СЕГОДНЯ()-B2)/365)
Как было сказано выше, эта формула расчета возраста не всегда безупречна, и вот почему. Каждый 4-й год является високосным и содержит 366 дней, тогда как формула делит количество дней на 365. Итак, если кто-то родился 29 февраля, а сегодня 28 февраля, эта формула возраста сделает человека на один день старше.
Деление на 365,25 вместо 365 тоже не безупречно, например, при вычислении возраста ребенка, еще не пережившего високосный год.
Учитывая вышесказанное, вам лучше сохранить этот способ расчета возраста для обычной жизни и использовать одну из следующих формул для расчета возраста по дате рождения в Excel.
РАЗНДАТ(Дата рожденияСЕГОДНЯ(), “у”)
ОКРУГЛ ВНИЗ(ГОДОВАЯ ЧАСТЬ(Дата рожденияСЕГОДНЯ(), 1), 0)
Подробное объяснение приведенных выше формул приведено в разделе Как рассчитать возраст в Excel. А следующий скриншот демонстрирует реальную формулу расчета возраста в действии:
=РАЗНДАТ(B2, СЕГОДНЯ(), “г”)
Расчет точного возраста по дате рождения (в годах, месяцах и днях)
Чтобы вычислить точный возраст в годах, месяцах и днях, напишите три функции РАЗНДАТ со следующими единицами измерения в последнем аргументе:
- Y – для расчета количества полных лет.
- YM – чтобы получить разницу между месяцами, игнорируя годы.
- MD – получить разницу между днями, игнорируя годы и месяцы.
Затем объедините 3 функции DATEDIF в одну формулу, разделите числа, возвращаемые каждой функцией, запятыми и определите, что означает каждое число.
Предполагая, что дата рождения находится в ячейке B2, полная формула выглядит следующим образом:
=РАЗНД ДАТЫ(B2,СЕГОДНЯ(),”Г”) & “Годы,” & РАЗНД ДАТЫ(B2,СЕГОДНЯ(),”ГМ”) & ” Месяцы, ” & РАЗНД ДАТЫ(B2,СЕГОДНЯ(),”MD”) & “Дни”
Эта возрастная формула может пригодиться, скажем, врачу для отображения точного возраста пациентов или кадровику, чтобы знать точный возраст всех сотрудников:
Для получения дополнительных примеров формул, таких как расчет возраста на определенную дату или в определенном году, ознакомьтесь со следующим учебным пособием: Как рассчитать возраст в Excel — примеры формул.
Вычисление високосных лет в Excel
Как известно, почти каждый четвертый год имеет дополнительный день 29 февраля и называется високосным годом. В листах Microsoft Excel можно различными способами определить, относится ли определенная дата к високосному или простому году. Я собираюсь продемонстрировать лишь пару формул, которые, на мой взгляд, легче всего понять.
Формула 1. Проверить, есть ли в феврале 29 дней
Это очень очевидный тест. Поскольку в феврале в високосные годы 29 дней, мы вычисляем количество дней во втором месяце данного года и сравниваем его с числом 29. Например:
=ДЕНЬ(ДАТА(2015,3,1)-1)=29
В этой формуле функция ДАТА(2015,3,1) возвращает 1-й день марта 2015 года, из которого мы вычитаем 1. Функция ДЕНЬ извлекает номер дня из этой даты, и мы сравниваем это число с 29. Если числа совпадают, формула возвращает значение ИСТИНА, в противном случае — ЛОЖЬ.
Если у вас уже есть список дат на листе Excel, и вы хотите знать, какие из них являются високосными, включите в формулу функцию ГОД, чтобы извлечь год из даты:
=ДЕНЬ(ДАТА(ГОД(A2),3,1)-1)=29
Где A2 — ячейка, содержащая дату.
Результаты, возвращаемые формулой, следующие:
Кроме того, вы можете использовать функцию КОНМЕСЯЦА, чтобы вернуть последний день февраля и сравнить это число с 29:
=ДЕНЬ(КОНМЕСЯЦ(ДАТА(ГОД(A2),2,1),0))=29
Чтобы сделать формулу более удобной для пользователя, используйте функцию ЕСЛИ, чтобы она возвращала, скажем, «Високосный год» и «Общий год» вместо ИСТИНА и ЛОЖЬ:
=ЕСЛИ(ДЕНЬ(ДАТА(ГОД(A2),3,1)-1)=29, “високосный год”, “простой год”)
=ЕСЛИ(ДЕНЬ(КОНМЕСЯЦА(ДАТА(ГОД(A2),2,1),0))=29, “високосный год”, “простой год”)
Формула 2. Проверить, состоит ли год из 366 дней
Это еще один очевидный тест, который вряд ли требует каких-либо объяснений. Мы используем одну функцию DATE для возврата 1 января следующего года, другую функцию DATE для получения 1 января этого года, вычитаем последнее из первого и проверяем, равна ли разница 366:
=ДАТА(2016,1,1) – ДАТА(2015,1,1)=366
Чтобы вычислить год на основе даты, введенной в какую-либо ячейку, вы используете функцию Excel ГОД точно так же, как мы делали это в предыдущем примере:
=ДАТА(ГОД(A2)+1,1,1) – ДАТА(ГОД(A2),1,1)=366
Где A2 — ячейка, содержащая дату.
И, естественно, вы можете заключить приведенную выше формулу ДАТА/ГОД в функцию ЕСЛИ, чтобы она возвращала что-то более значимое, чем логические значения ИСТИНА и ЛОЖЬ:
=ЕСЛИ(ДАТА(ГОД(A2)+1,1,1) – ДАТА(ГОД(A2),1,1)=366, “Високосный год”, “Невисокосный год”)
Как уже было сказано, это не единственные возможные способы расчета високосных лет в Excel. Если вам интересно узнать о других решениях, вы можете проверить метод, предложенный Microsoft. (Как обычно, ребята из Microsoft не ищут легких путей, не так ли?) И еще 14 формул вы можете найти на этот блог.
Надеюсь, эта статья помогла вам разобраться с расчетами года в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами на следующей неделе.