Вычисление возраста
Excel может помочь вычислить возраст человека различными способами. В таблице ниже приведены распространенные способы использования функций Дата и время.
Чтобы использовать эти примеры в Excel, выделите нужные данные в таблице, щелкните их правой кнопкой мыши и выберите пункт Копировать. Откройте новый таблицу, щелкните правой кнопкой мыши ячейку A1 и выберите параметры в > Сохранить форматирование источника.
Результатом является возраст человека — разница между сегодняшним и датами рождения в A2.
В этом примере используются функции ГОДи СЕЙЧАС.
Если в этой ячейке не отображается число, убедитесь, что оно отформатировано как число или общее. Узнайте, как отформатировать ячейку как число или дату.
Возраст человека, который родился в 1960 г., без использования ссылок на ячейки.
Если эта ячейка не отображается как число, убедитесь, что она отформатирована как число или общий. Узнайте, как отформатировать ячейку как число или дату.
Вычисляет возраст дробной части года между датами в A5 и A3.
Вычисляет возраст между датами в A5 и A6 ( 12,08).
Для учета високосного года, который приходится каждые 4 года, в формуле используется 365,25.
Вычисляет количество дней между двумя датами без использования ссылок на ячейки ( 153).
Количество дней между двумя датами с использованием двух функций даты.
Двумя аргументами функции ДНИ могут быть фактические даты, ссылки на ячейки или другая функция даты и времени, например функция СЕГОДНЯ.
Количество месяцев между A3 и текущей датой.
Если эта ячейка не отображается как число, убедитесь, что она отформатирована как число или общий. Узнайте, как отформатировать ячейку как число или дату.
Количество рабочих дней между двумя датами в A2 и A3 — 107. Рабочими днями не считаются выходные дни и праздники. Последний аргумент (A3:A5) представляет собой количество праздничных дней, которое вычитается из количества рабочих дней.
В этом примере используется функция ЧИСТРАБДНИ.
Количество дней между двумя датами в A2 и A3 ( 570). Он основан на 360-дневном году (двенадцать месяцев по 30 дней), типичных для бухгалтерских вычислений.
В этом примере используется функция ДНЕЙ360.
Преобразуем этот формат в формат даты( 02.01.2014, то есть на четыре месяца раньше даты в A3).
В этом примере функция ДАТАМЕС используется для вычисления даты погашения кредитных билетов.
Дополнительные сведения
Узнайте, как вычислять разницу между датами с использованием функций Google Sheets: DATEDIF, DAYS360 и EDATE.
Сколько лет, месяцев, дней прошло с конкретной даты в EXCEL
history 8 апреля 2013 г.
Определим, сколько лет, месяцев и дней прошло с определенной даты, а также решим обратную задачу.
Если в ячейке В2 содержится сегодняшняя дата, а в А2 –дата начала отсчета (например, день рождения), то формула: =РАЗНДАТ(A2;B2;»y»)&» г. «& РАЗНДАТ(A2;B2;»ym»)&» мес. «& РАЗНДАТ(A2;B2;»md»)&» дн.»
Итоговая формула приведена в файле примера:
Уточним склонения: год/ лет, месяца/ месяцев, дня/ дней
Воспользовавшись идеями, подсказанными нашим пользователем МСН, также просклоняем месяцев/ месяца и дней/ дня
=ЕСЛИ(РАЗНДАТ(A2;B2;»y»);РАЗНДАТ(A2;B2;»y»)&» «&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(A2;B2;»y»)-11;100);9);10);»[ РАЗНДАТ() с параметрами md и ym подсчитает, что разница составляет 1 месяц и 14 дней.
Обратная задача
Теперь решим обратную задачу: задав количество количество лет, месяцев и дней, рассчитаем конечную дату.
Конечную дату будем рассчитывать двумя способами: через функцию ДАТА() и ДАТАМЕС()
Вычислив конечную дату, затем вычислим с помощью РАЗНДАТ() разницу между ними (должна совпасть с заданной). И наконец, из конечной даты получим начальную, вычтя заданное количество лет, месяцев и дней с использованием формул:
Если для начальной даты 15.01.2015 (строка 8 в файле примера, лист Обратная задача ) все вычисления понятны и одинаковы (добавили 1 год, 2 месяца и 5 дней), получили 20.03.2015, разница составила теже 1 год, 2 месяца и 5 дней, расчетная начальная дата совпала с заданной начальной датой, то для 29.01.2015 все не так гладко (см. строку 9). Для наглядности прибавим 1 месяц.
Как мы уже знаем из статьи Функция ДАТАМЕС() в MS EXCEL функции ДАТА() и ДАТАМЕС() не эквивалентны при прибавлении месяцев:
При прибавлении месяцев между ДАТАМЕС() и ДАТА() существует разница. Прибавим к 29.01.2015 один месяц:
Расчетная начальная дата в обоих случаях не совпадает с заданной начальной датой: получим 01.02.2015 и 28.01.2015 вместо 29.01.2015.
8 способов — как посчитать возраст в Excel по дате рождения.
В Excel нет специальной функции, чтобы посчитать возраст, однако существует несколько различных способов его определения на основе даты рождения. Вы узнаете несколько приемов, как рассчитать его в количестве полных лет, в годах, месяцах и днях на текущий момент или на какой-то определённый момент времени. Мы объясним преимущества и недостатки каждого способа, покажем, как создать идеальную формулу расчета возраста в Экселе и настроить ее для решения некоторых конкретных задач.
В повседневной жизни вопрос «Сколько вам лет?» обычно подразумевает ответ, указывающий, сколько лет вы прожили. В Microsoft Excel вы можете создать формулу для исчисления точной продолжительности жизни в месяцах, днях, часах и даже минутах. Но давайте не будем нарушать традицию и научимся сначала считать по полным годам.
Основная формула Excel расчета для возраста в годах.
Как вы обычно определяете, сколько вам лет? Просто вычитая дату рождения из текущей. Этот традиционный подход, знакомый нам еще с детства, может также использоваться в Excel.
Если предположить, что день рождения (ДР) записан в ячейке B2, то вычислить возраст в годах можно следующим образом:
=(СЕГОДНЯ()-B2)/365
=(TODAY()-B2)/365
Первая часть выражения (СЕГОДНЯ()-B2) возвращает разницу между текущей и датой рождения в днях. А затем вы делите это на 365, чтобы получить количество лет.
Формула очевидна и легко запоминаема, однако есть небольшая проблема. В большинстве случаев мы получаем число, как показано на скриншоте ниже. Что, согласитесь, несколько непривычно и не слишком удобно.
Чтобы отобразить количество прожитых лет, используйте один из предлагаемых методов округления десятичного числа до ближайшего целого:
Функция ЦЕЛОЕ округляет в меньшую сторону до ближайшего целого.
Округляет количество прожитых лет по стандартным правилам в зависимости от значения дробной части (более 0,5 — в большую сторону).
Округляет в меньшую сторону.
Отбрасывает дробную часть, оставляя только целое.
Во всех случаях ноль в формуле означает, что в возвращаемой цифре не должно быть ни одного десятичного разряда.
Недостатки: использование этого выражения в Экселе дает довольно точные результаты, но не безупречные. Деление на среднее число дней в году в большинстве случаев работает нормально, но иногда все же показывает неверный возраст. Например, если кто-то родился 29 февраля, а сейчас 28 февраля, программа сделает человека на один день старше.
В качестве альтернативы вы можете использовать в знаменателе 365,25 вместо 365, поскольку каждый четвертый год состоит из 366 дней. Однако и этот подход не идеален. Например, если вы рассчитываете возраст ребенка, который еще не прожил високосный год, деление на 365,25 дает неправильный результат.
В целом, вычитание ДР из текущей даты прекрасно работает в обычной жизни, но не является идеальным подходом в Excel. Ведь мы здесь ожидаем получить точный ответ.
Далее мы поговорим о нескольких специальных функциях, которые безошибочно вычисляют возраст независимо от года.
Как определить возраст с функцией ДОЛЯГОДА.
Синтаксис здесь следующий:
YEARFRAC (дата_начала, дата_окончания, [базис])
Первые два аргумента очевидны и вряд ли требуют какого-либо дополнительного объяснения. А вот Базис — это аргумент, который определяет основу для подсчета дней.
Чтобы составить абсолютно точную формулу, передайте в ДОЛЯГОДА следующие значения:
Учитывая вышеизложенное, формула Excel для расчета возраста по дате рождения выглядит следующим образом:
Предполагая, что ДР вписан в ячейку B2, выражение принимает следующий вид:
Как и в предыдущем примере, результат также является десятичным числом. Чтобы исправить это, используйте любой из подходящих вам методов округления.
Итак, вот улучшенный вариант:
Метод округления можете выбрать сами.
Особенности применения этих функций мы уже рассмотрели выше.
Как узнать возраст в Excel при помощи РАЗНДАТ.
Она может возвращать разницу в различных единицах времени, таких как годы, месяцы и дни, в зависимости от параметра, который вы задаете в аргументе:
Поскольку мы хотим подсчитать возраст в годах, мы используем параметр «Y»:
В этом примере день рождения записан в ячейке B2, и вы ссылаетесь на него в своей формуле возраста:
В этом случае дополнительная операция округления не требуется, так как РАЗНДАТ с аргументом «Y» сразу выводит количество лет.
Рассчитываем в днях, месяцах и годах.
Высчитывать можно не только количество лет, как это обычно принято, но и более мелкие единицы времени.
Давайте попробуем рассчитать полный возраст, который включает в себя дни, месяцы и годы.
Фактически, определение чьего-либо возраста на основе даты рождения является частным случаем расчета разницы в датах в Excel, где конечной датой является текущий момент времени. Итак, вы используете обычную функцию РАЗНДАТ:
Ну а если нужно сделать совсем красиво, используйте более сложное выражение:
=ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();»y»);РАЗНДАТ(B2;СЕГОДНЯ();»y»)&» «&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();»y»)-11;100);9);10);»[
Более подробно об использовании функции РАЗНДАТ читайте в материале о расчете разности между датами в Excel.
Сколько вам будет на определённую дату?
Вы можете воспользоваться формулой РАЗНДАТ из предыдущего раздела, но вместо СЕГОДНЯ() используйте ссылку на ячейку, в которой и будет указано, на какой именно момент времени вы хотите определить возраст человека.
Кстати, аналогичным образом можно определять и величину трудового стажа.
Итак, вот как это может выглядеть:
Эта формула полезна в ситуациях, когда полная дата для расчета не определена, и вы знаете только год.
Допустим, вы работаете с медицинской базой данных, и ваша цель – выяснить, сколько лет пациенту на момент последнего полного медицинского обследования.
Если предположить, что ДР указан в столбце B, начиная со строки 2, а год последнего медицинского осмотра находится в столбце C, то расчет выглядит следующим образом:
Поскольку точный срок медицинского осмотра не определен, вы используете функцию ДАТА с произвольным аргументом дня и месяца, например, DATА(C3; 1; 1).
Функция ДАТА извлекает номер года из ячейки С2, создает полную дату, используя введенные вами номера месяца и дня (в этом примере 1 января), и передает ее в РАЗНДАТ. В результате вы получаете сумму прожитых пациентом лет по состоянию на 1 января определенного года.
Узнайте дату, когда человек достигает N лет
Предположим, ваш друг родился 21 марта 1974 года. Как вам определить, когда ему исполняется 50 лет? Обычно вы просто добавляете 50 лет к ДР человека. В Excel вы делаете то же самое, используя ДАТА:
Вместо того, чтобы жестко указывать количество лет в формуле, вы можете сослаться на определенную ячейку, в которую ваши пользователи могут вводить сколько угодно лет.
Калькулятор возраста для Excel
Пользователям надстройки Ultimate Suite не нужно беспокоиться о создании своего собственного калькулятора возраста в Excel — это всего лишь пара щелчков мышью:
Формула мгновенно вставляется в выбранную ячейку, и вы можете дважды щелкнуть маркер заполнения, чтобы скопировать его вниз по столбцу.
Как вы могли заметить, формула, созданная калькулятором возраста в Excel, более сложна, чем те, которые мы обсуждали до сих пор.
К сожалению, пока русской локализации нет, но вы легко можете заменить в формуле английские слова на русские и затем использовать ее там, где это необходимо.
Либо вовсе не добавляйте текст и получите возраст в виде текста «лет/месяцев/дней» (см. ячейку B7 на скриншоте выше).
Если вы хотите избавиться от нулевых единиц, таких как «0 дней», установите флажок «Не показывать нулевые значения».
Также вы можете более подробно узнать о мастере даты и времени здесь.
Если вам интересно протестировать этот калькулятор возраста, а также открыть для себя еще 60 надстроек для экономии времени в Excel, то можете загрузить пробную версию Ultimate Suite. Если вам понравились инструменты и вы решили получить лицензию, не пропустите это специальное предложение для читателей нашего сайта.
Надеюсь, из рассмотренных нами вариантов вы сможете выбрать для себя самый наилучший и удобный, чтобы посчитать возраст человека по дате его рождения.
Также рекомендуем почитать о работе с датами:
Как в excel посчитать возраст по дате рождения?
Для определения возраста в программе электронных таблиц нам могут пригодиться несколько встроенных функций из раздела Дата и время. Все они находятся на вкладке Формулы ленты инструментов.
Воспользуемся одной из этих функций и заполним столбецС текущей датой, чтобы иметь возможность ссылаться на него в формуле. Иногда вместо даты в ячейках могут появиться числа. Тогда необходимо вручную изменить формат ячейки с Числовогона Дата на вкладке Главная.
Рассмотрим несколько способов подсчёта возраста.
1.Внесём в ячейку D2 следующую формулу =(С2-В2)/365,25. Она вычислит разницу в годах между датами из ячеек C2 и B2. Число 365,25 должно учесть високосные года. Протянув за маркер автозаполнения вниз до конца таблицы, заполним столбец данными.
При необходимости можно обойтись без столбцаС, напрямую внеся в формулу одну из приведённых ранее функций. Например =(СЕГОДНЯ()-В2)/365,25.
После нажатия на кнопку ОК в ячейке D2 появится формула =ДОЛЯГОДА(В2;С2).
В описанных вариантах расчётов полученные результаты необходимо округлить до нужного количества десятичных знаков (команда Формат ячеек на вкладке Главная или через контекстное меню). Также, для более правильных вычислений можно воспользоваться функцией ОКРУГЛВНИЗ(). Она находится в разделе Математические на вкладке на вкладке Формулы.
3. Ещё один вариант вычисления возраста по дате предполагает использование функции ГОД(). Она преобразует дату из формата Дата и время в число. После можно производить простые математические расчёты. Введём в ячейку D2 формулу =(ГОД(C2)-ГОД(B2)) или воспользуемся вариантом формулы без дополнительного столбца =(ГОД(ТДАТА())-ГОД(A2)).
Данная формула больше всего подходит для подсчёта трудового стажа сотрудников. Результат её работы не требует дополнительных ухищрений с округлением.
Автор: Светлана Бартош
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями
Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст
Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.
Кстати, такая возможность есть
на всех страницах нашего сайта
Как вычислить дату в Excel
Дата и время в Excel – это числа сформатированные специальным образом. Датой является целая часть числа, а время (часы и минуты) – это дробная часть.
По умолчанию число 1 соответствует дате 01 января 1900 года. То есть каждая дата – это количество дней прошедших от 01.01.1900. На данном уроке детально рассмотрим даты, а на следующих уроках – время.
Как в Excel посчитать дни между датами?
Так как дата является числом, значит можно проводить с ним математические вычислительные и расчетные операции. Посчитать количество дней между двумя датами Excel не составляет особых проблем. Для наглядного примера сначала выполним сложение, а потом вычитание дат. Для этого:
Обратите внимание, формат «Дата» автоматически присвоился для ячейки A3. Не сложно догадаться, чтобы посчитать разницу в датах в Excel нужно от новейшей даты отнять старшую дату. В ячейке B1 введите формулу:=A3-A1. Соответственно мы получаем количество дней между этими двумя датами.
Вычисление возраста по дате рождения в Excel
Теперь мы научимся вычислять возраст по дате рождения:
Таким образом, применение функции позволило нам точно вычислить возраст по дате рождения в 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 день.
На следующем уроке мы на готовых примерах будем работать со временем и периодами суток.