Как определить возраст по дате рождения в excel: 6 способов — как посчитать возраст в Excel по дате рождения.

Вычисление возраста или стажа функцией РАЗНДАТ (DATEDIF)

Для вычислений длительностей интервалов дат в Excel есть функция РАЗНДАТ, в английской версии — DATEDIF.

Нюанс в том, что Вы не найдете эту функцию в списке Мастера функций, нажав кнопку fx — она является недокументированной возможностью Excel. Точнее говоря, найти описание этой функции и ее аргументов можно только в полной версии англоязычной справки, поскольку на самом деле она оставлена для совместимости со старыми версиями Excel и Lotus 1-2-3. Однако, несмотря на то, что эту функцию не получится вставить стандартным способом через окно Вставка — Функция (Insert — Function), ее можно вручную вписать в ячейку с клавиатуры — и она сработает!

Синтаксис функции следующий:

=РАЗНДАТ(Начальная_дата; Конечная_дата; Способ_измерения)

С первыми двумя аргументами все более-менее понятно — это ячейки со стартовой и конечной датами. А самый интересный аргумент, конечно, последний — он определяет, каким именно образом и в каких единицах будет измеряться интервал между начальной и конечной датами. Этот параметр может принимать следующие значения:

«y»	разница в полных годах
«m»	в полных месяцах
«d»	в полных днях
«yd»	разница в днях с начала года без учета лет
«md»	разница в днях без учета месяцев и лет
«ym»	разница в полных месяцах без учета лет

Например:

Т.е. при желании подсчитать и вывести, например, ваш стаж в виде «3 г. 4 мес. 12 дн.», необходимо ввести в ячейку следующую формулу:

=РАЗНДАТ(A1;A2;»y»)&» г. «&РАЗНДАТ(A1;A2;»ym»)&» мес. «&РАЗНДАТ(A1;A2;»md»)&» дн.»

где А1 — ячейка с датой поступления на работу, А2 — с датой увольнения.

или в английской версии Excel:

=DATEDIF(A1;A2;»y»)&» y. «&DATEDIF(A1;A2;»ym»)&» m. «&DATEDIF(A1;A2;»md»)&» d.»

Ссылки по теме

Как в Excel рассчитать возраст человека — Трюки и приемы в Microsoft Excel

Расчет возраста человека — не самая простая задача, потому что возраст зависит не только от текущего года, но и от текущего дня. И еще вы должны учитывать дополнительные сложности, связанные с високосными годами.

В этой статье я представляю три метода для расчета возраста человека. Эти формулы предполагают, что ячейка В1 содержит дату рождения (например, 2/16/1952), а ячейка В2 хранит текущую дату (вычисленную с помощью функции СЕГОДНЯ).

Метод 1

Следующая формула вычитает дату рождения из текущей даты и делит на 365,25. Функция ЦЕЛОЕ затем устраняет десятичную часть результата: =ЦЕЛОЕ((B2-B1)/365,25). Эта формула не на 100 % точная, поскольку деление в ней выполняется на среднее количество дней в году. Рассмотрим такой пример — ребенку ровно один год. Эта формула возвращает значение 0, а не 1. Подробнее вам расскажут на http://saint-petersburg.mir-repetitorov.ru.

Метод 2

Более точный способ расчета возраста использует функцию ДОЛЯГОДА: =ЦЕЛОЕ(ДОЛЯГОДА(B2;B1)). Функция ДОЛЯГОДА обычно применяется в финансовых расчетах, но прекрасно работает и для подсчета возраста. Эта функция вычисляет долю года, представленную количеством полных дней между двумя датами. Использование функции ЦЕЛОЕ позволяет избавиться от дробной части и получить целое число.

Метод 3

Третий метод для расчета возраста задействует функцию РАЗНДАТ. Эта недокументированная функция не описана в справочной системе Excel: =РАЗНДАТ(B1;B2;"Y").

Если вы любите точность, то используйте другую версию: =РАЗНДАТ(B1;B2;"y")&" года, "&РАЗНДАТ(B1;B2;"ym"&" месяца, "&РАЗНДАТ(B1;B2;"md")&" день"). Эта функция возвращает текстовую строку, например: 24 года, 2 месяца, 21 день.

Вычисление возраста в Excel

Вычислить возраст человека непросто, так как результат зависит не только от текущего года, но и дня. Кроме того, необходимо учесть осложнения, возникающие из-за високосных годов. Мы рассмотрим три метода вычисления возраста человека (рис. 1). [1]

Рис. 1. Вычисление возраста человека

Скачать заметку в формате Word или pdf, примеры в формате Excel

Метод 1. Следующая формула вычитает дату рождения из текущей даты и делит результат на 365,25. Функция ЦЕЛОЕ отсекает дробную часть результата: =ЦЕЛОЕ((B2-B1)/365,25). Эта формула не точна на 100%, так как делителем является среднее количество дней в году. Допустим, есть ребенок, которому ровно 1 год. Для него эта формула вернет 0 в обычный год, и 1 – в високосный (рис. 2).

Рис. 2. Разность дат, деленная на 365,25 иногда дает осечку

Метод 2. Более точный метод подсчета возраста связан с использованием функции ДОЛЯГОДА: =ЦЕЛОЕ(ДОЛЯГОДА(B1;B2)). Как правило, функция ДОЛЯГОДА используется в финансовых расчетах, но подходит и для вычисления возраста. Эта функция рассчитывает долю года, соответствующую целому количеству дней между двумя датами. Функция ЦЕЛОЕ удаляет дробную часть результата и возвращает целое число, соответствующее количеству полных лет.

Метод 3. Данный метод вычисления возраста связан с использованием функции РАЗНДАТ: =РАЗНДАТ(В1:В2;"y"). Это недокументированная функция, которой нет в списке функций, и она не появляется в подсказке, если начать набирать формулу =раз… Правда, ее описание есть в справке Excel.

Эта функция сохранена в Excel для обеспечения совместимости с Lotus 1-2-3. Функция рассчитывает разницу между двумя датами и выражает результат в месяцах, днях или годах. Синтаксис функции РАЗНДАТ(нач_дата;кон_дата;единица). Нач_дата и кон_дата — это стандартные даты (здесь также может стоять ссылка на ячейку, в которой содержится дата). Нач_дата должна быть меньше или равна кон_дата. Третий аргумент – единица – представляет собой текстовую строку, указывающую единицы времени, в которых будет выражен возвращаемый результат. Основные коды интервалов: m — количество полных месяцев, d — количество дней, у — количество полных лет. Аргумент единица может принимать и несколько других значений (подробнее см. справку).

Можно выразить возраст с точностью до дня: =РАЗНДАТ(B1;B2;"y")&" лет "&РАЗНДАТ(B1;B2;"ym")&" месяцев "&РАЗНДАТ(B1;B2;"md")&" дней". Правда, в связи с особенностями русского языка, иногда это будет выглядеть коряво.

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 126, 127.

Как вычислить дату в Excel

Дата и время в Excel – это числа сформатированные специальным образом. Датой является целая часть числа, а время (часы и минуты) – это дробная часть.

По умолчанию число 1 соответствует дате 01 января 1900 года. То есть каждая дата – это количество дней прошедших от 01.01.1900. На данном уроке детально рассмотрим даты, а на следующих уроках – время.

Как в Excel посчитать дни между датами?

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

1. На чистом листе в ячейку А1 введите текущею дату нажав CTRL+;.
2. В ячейке A2 введите промежуточный период в днях, например 127.
3. В ячейку A3 введите формулу:=A1+A2.

Обратите внимание, формат «Дата» автоматически присвоился для ячейки A3. Не сложно догадаться, чтобы посчитать разницу в датах в Excel нужно от новейшей даты отнять старшую дату. В ячейке B1 введите формулу:=A3-A1. Соответственно мы получаем количество дней между этими двумя датами.



Вычисление возраста по дате рождения в Excel

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

1. На новый лист в ячейки A1:A3 введите даты: 18.04.1985; 17.08.1977; 08.12.1992
2. В ячейки B1:B3 проставьте текущею дату.
3. Теперь нужно воспользоваться функцией по преобразованию количества дней в количество лет. Для этого вручную введите в диапазон C1:C3 следующее значение: =РАЗНДАТ(A1;B1;»y»).

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

Внимание! Чтобы перевести дни в года не достаточно формулы: =(B1-A1)/365. Более того даже если мы знаем что 1 сутки = 0,0027397260273973 года, то формула:=(B1-A1)*0,0027397260273973 так же не даст точного результата.

Дни в года наиболее точно преобразует функция:=РАЗНДАТ(). Английская версия =DATEDIF(). Вы не найдете ее в списке мастера функций (SHIFT+F3). Но если просто введете ее в строку формул, то она сработает.

Функция РАЗНДАТ поддерживает несколько параметров:

Проиллюстрируем пример использования нескольких параметров:

Внимание! Чтобы функция:=РАЗНДАТ() работала без ошибок, следите за тем, чтобы начальная дата была старше конечной даты.

Ввод даты в ячейку 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 день.

Способ отображения даты можно задать с помощью диалогового окна «Формат ячеек». Для его вызова нажмите: CTRL+1. На закладке «Число» выберите в списке «Числовые форматы» — «Дата». В разделе «Тип» отображаются самые популярные форматы для отображения дат.

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

Читайте также: Функции для работы с датами в Excel

На следующем уроке мы на готовых примерах будем работать со временем и периодами суток.

Вычислить возраст по дате рождения Excel

Как посчитать возраст или стаж в Excel?

Рассмотрим возможности расчета стажа или возраста

человека в Excel в полных годах, месяцах или днях.

РАЗДАТ(нач_дата; кон_дата; единица) Вычисляет количество дней, месяцев или лет между двумя датами.

• Начальная дата(обязательный аргумент) — дата начала периода;
• Конечная дата(обязательный аргумент) — дата окончания периода;
• Единица(обязательный аргумент) — тип возвращаемого значения:
  • «Y» — разница в полных годах;
  • «M» — разница в полных месяцах;
  • «D» — разница в полных днях;
  • «MD» — разница в днях без учета месяцев и годов;
  • «YM» — разница в месяцах без учета дней и годов;
  • «YD» — разница в днях без учета годов.

Как посчитать стаж в Excel?

Предположим, что у нас есть таблица с датами начала и окончания работы сотрудников в компании (если сотрудник еще работает в компании, то в поле с датой окончания работы вписываем формулу СЕГОДНЯ):

Если мы хотим рассчитать стаж в Excel в полном формате, например «1 г. 2 мес. 3 дн.», то запишем формулу стажа работы в следующем виде:

В результате подсчета стажа получаем:

Как посчитать возраст в Excel?

Предположим, что у нас есть таблица с датами рождения сотрудников в компании:

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

tutorexcel.ru

Как посчитать дни, возраст, стаж в excel

Excel предназначен для решения множества задач, которые связаны с вычислением периодов времени. Такие задачи как подсчет стажа работы, возраста, количества дней между двумя датами являются наиболее распространёнными. Поэтому в данной статье я постараюсь дать исчерпывающие ответы на эти и некоторые другие вопросы.

Как посчитать количество дней между двумя датами

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

Задача: посчитать сколько прошло дней с прошлого Нового Года и какое количество дней осталось до следующего Нового Года.

Прошлый Новый Год 01-01-2015, а следующий 01-01-2016. Что касается текущей даты, то выберите ее самостоятельно. Я выбрал 1 сентября.

Немного теории. Хочу напомнить, что Excel правильно работает с датами, начиная с 01-01-1900 года. Все даты можно представить в виде простого числа, например «1» соответствует дате 01-01-1900, а число «41640» соответствует дате 01-01-2014. Именно поэтому, если нам нужно посчитать дни между датами, достаточно от одной даты отнять другую. Стоит помнить, что если от большей даты отнять меньшую дату, то получим положительное число и наоборот.

В Excel я создал табличку как изображено на рисунке. Таблица разбита на две части, в первой части я подсчитываю количество дней прошедших с 01-01-2015 по 01-09-2015. При этом в колонке «Дата» я использую в ячейках формат данных «Дата», а в колонке «Число» я использую числовой формат данных. Во второй части таблицы я подсчитываю количество дней оставшихся до даты 01-01-2016 с01-09-2015.

Расчет разницы между датами

Формулы записанные в строке 6 очень простые.

B6 = B4 — B5 C6 = C4 — C5 D6 = D4 — D5 E6 = E4 — E5

Со второго рисунка лучше видно как считается количество дней. В ячейку B6 записали формулу B4-B5.

Расчет разницы между датами с дкмонстрацие формулы

Задача решена. В ней показано как очень просто можно посчитать количество дней между двумя датами.

Как посчитать стаж или возраст в excel

Теперь попробуем решить другую задачу и посчитать от даты 01.01.1980 до даты 01.01.2016 сколько прошло полных лет, полных месяцев или дней. Хочу сказать, что решать подобную задачу очень часто приходится бухгалтерам. Представителям данной профессии приходится регулярно считать стаж проработанный человеком и его полный возраст. Но как показывает практика, бухгалтера не умеют считать стаж в Excel только из-за того, что не знаю функцию РАЗНДАТ.

РАЗНДАТ(первая_дата;вторая_дата;тип_расчета) — функция предназначенная для расчета разницы между датами.

первая_дата — дата от которой нужно считать; вторая_дата — дата по которую нужно считать; тип_расчета — то как считать. Тип расчета может принимать следующие значения «y» -считать годы, «m» — считать месяцы, «d» — считать дни, «yd» — разница в днях с начала года без учета лет, «md» — разница в днях без учета месяцев и года, «ym» — разница полных месяцев без учета лет.

Скрин таблицы очень хорошо демонстрирует как работает данная формула с первыми тремя типами расчета.

Подсчет количества полніх лет, месяцев и дней с помощью функции =РАЗНДАТ

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

Расчет стажа и возраста с помощью функции =РАЗНДАТ

На рисунке видно как в колонке «Стаж» рассчитывается полный и правильный стаж человека. Туда записана достаточно длинная формула.

Также хочется отметить, что в колонке «Сегодняшняя дата» записана функция =СЕГОДНЯ(), которая определяет текущую дату. Таким образом, при каждом новом открытии документа сегодняшняя дата будет меняться, и мы будем точно определять стаж сотрудника в excel.

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

poznayit.ru

Срок, Возраст, Стаж в Excel. Бесплатные примеры и статьи.

Функция РАЗНДАТ() — Вычисление разности двух дат в днях, месяцах, годах в MS EXCEL

Расчет страхового (трудового) стажа в MS EXCEL

Рассчитаем страховой (трудовой) стаж в годах, месяцах и днях.

Функция ЧИСТРАБДНИ() — подсчет рабочих дней в MS EXCEL

Функция ЧИСТРАБДНИ( ) , английская версия NETWORKDAYS(), возвращает количество рабочих дней между двумя датами, т.е. при подсчете праздники и выходные не учитываются.

Функция СЕГОДНЯ() в MS EXCEL

Склоняем правильно в MS EXCEL число месяцев и дней (1 месяц, 2 месяца, 10 месяцев)

Учимся корректно склонять число месяцев: 1 месяц, 2 месяца, 10 месяцев, 1 день, 2 дня, 10 дней.

Диапазон дат в одной ячейке (в текстовом формате) в MS EXCEL

Выведем диапазон дат (начальная — конечная дата) в одной ячейке в формате 21-25.10.2012.

Сколько осталось дней до конца месяца в MS EXCEL

Найдем количество дней, оставшихся до конца месяца.

Подсчет рабочих дней при нестандарной рабочей неделе в MS EXCEL

Подсчитаем количество рабочих дней между двумя датами в случае нестандартной рабочей недели:

Подсчет отработанных месяцев в MS EXCEL

Подсчитаем количество отработанных месяцев в году. Месяц считается отработанным, если человек проработал в нем более 15 рабочих дней.

Проверка достижения требуемого возраста в MS EXCEL

Напишем формулу для проверки достижения 20-летнего возраста на сегодняшнюю дату. Формула должна возвращать значение ИСТИНА, если кому-то уже есть полных 20 лет, в противном случае формула должна возвращать значение ЛОЖЬ.

MS EXCEL: В какой рабочий день закончится выполнение работы

При планировании, длительность выполнения работ обычно задают в рабочих днях. Дату окончания работы можно рассчитать с помощью функции РАБДЕНЬ() .

В какой день закончится определенный срок (декретный отпуск) в MS EXCEL

Вычислим дату окончания срока, если задана дата начала периода и срок (срок может быть задан в днях, месяцах, годах)

MS EXCEL: Сколько полных месяцев прошло?

Определим, сколько полных месяцев прошло с определенной даты до сегодняшнего дня (или до заданной конечной).

Сколько лет, месяцев, дней прошло с конкретной даты в MS EXCEL

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

Возраст или стаж в годах в MS EXCEL

Определим возраст (или стаж) в полных годах.

excel2.ru

Как в excel посчитать возраст по дате рождения или стаж по дате трудоустройства

Для вычисления возраста или стажа мы будет использовать недокументированную функцию Excel — РАЗНДАТ() (в английской версии Excel — DATEDIF () )

Описание этой функции есть только в английской справке, ее невозмжно вызвать стандартным способом, она сохранена в Excel только для совместимости работы со старыми версиями Excel и Lotus 1-2-3

Но несмотря на это, если мы ее пропишем в Excel, то она отлично будет работать во всех версиях Excel без необходимости, что-то включать или устанавливать дополнительно.

Синтаксис функции РАЗНДАТ() с помощью которой мы будем вычислять возраст или стаж человека следующий:

=РАЗНДАТ( начальная_дата ; конечная_дата ; способ_измерения )

начальная_дата — это дата отсчета, если мы вычисляем возраст, то это дата рождения, если вычисляем стаж работы, то это дата трудоустройства.

конечная_дата — это дата на которую нам необходимо посчитать то или иное значение. Если рассматривать стаж и возраст, то как правило, требуется посчитать на сегодняшнее число. То есть какой стаж у сотрудника с начала даты трудоустройства и до сегодняшнего дня. Аналогично и для даты рождения.Текущую дату можно вставлять в ручную, но если открыть файл на следующий день, то сегодняшняя дата превратится во вчерашнюю, поэтому вы можете использовать функцию СЕГОДНЯ() , которая будет автоматически вставлять сегодняшнюю дату при любом обновлении файла. Таким образом у вас стаж работы или возраст будет считаться автоматически в Excel при любом открытии файла.

Особое внимание стоит обратить на самый последний аргумент — способ_измерения , который определяет тип и единицу измерения между начальной и конечными датами. Ниже перечислены все возможные варианты данного аргумента:

• «y» разница дат в полных годах
• «m» разница дат в полных месяцах
• «d» разница дат в полных днях
• «yd» разница дат в днях с начала года без учета лет
• «md» разница дат в днях без учета месяцев и лет
• «ym» разница дат в полных месяцах без учета лет

Таким образом, если нам необходимо посчитать возраст или стаж работы в полных лет, то мы можем использовать аргумент «y» , и формула будет выглядеть следующим образом:

=РАЗНДАТ( A2 ; B2 ; «y» )

В английской версии Excel =DATEDIF (A2;B2; «y»)

Либо можно удалить второй столбец и прописать формулу =РАЗНДАТ( A2 ; Сегодня() ; «y» )

Самое главное, что при этом будет учитываться дата и месяц рождения, например, если сегодня было бы 10.04.2014 (в примере на картинке), то возраст был бы не 31, а 30 лет, так как день рождение в 2014 году еще не наступило.

Если вы хотите указать стаж работы в полном варианте, например «2 г. 3 мес. 4 дня», то формула будет выглядеть следующим образом:

Тут мы сначала вычисляем количество полных лет » y» , затем количество полных месяцев без учета лет «ym» и последнее это «md» — разница дат без учета месяцев и лет. После этого все эти числа и текст мы склеиваем с помощью специального символа и получаем нужный нам результат.

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

sirexcel.ru

Посчитать стаж работы в excel

Проблемы с отображением видео:

ЁXCEL_Стаж

Данная функция определяет точный стаж работы сотрудника в виде: «2 года 3 месяца 10 дней» по начальной и конечной датам:

Чтобы воспользоваться данной функций используйте один из трех способов вставки функции.

Укажите соответствующие аргументы:

• «НачДата» — обязательный аргумент — ячейка с начальной датой;
• «КонДата» — обязательный аргумент — ячейка с конечной датой.

И нажмите кнопку «OK»:

Важно:

• НачДата и КонДата могут быть ссылками на ячейки или датами. Не могут быть диапазонами.

Если вместо начальной и конечной даты у вас есть, просто, количество дней, отработанных сотрудником. Для определения стажа воспользуйтесь функцией ЁXCEL_СтажД.

Если Вы хотите на основе стажа работника вычислить количество отработанных им дней, воспользуйтесь функцией ЁXCEL_СтажДней.

e-xcel.ru

Как посчитать возраст или стаж в Excel?

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

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

Для расчета интервала между двумя датами воспользуемся функцией РАЗНДАТ.
Данную формулу нельзя найти через мастер функций, однако если ввести ее непосредственно в ячейку, то она будет прекрасно работать.

РАЗДАТ(нач_дата; кон_дата; единица)
Вычисляет количество дней, месяцев или лет между двумя датами.

• Начальная дата (обязательный аргумент) — дата начала периода;
• Конечная дата (обязательный аргумент) — дата окончания периода;
• Единица (обязательный аргумент) — тип возвращаемого значения:
  • «Y» — разница в полных годах;
  • «M» — разница в полных месяцах;
  • «D» — разница в полных днях;
  • «MD» — разница в днях без учета месяцев и годов;
  • «YM» — разница в месяцах без учета дней и годов;
  • «YD» — разница в днях без учета годов.

Как посчитать стаж в Excel?

Предположим, что у нас есть таблица с датами начала и окончания работы сотрудников в компании (если сотрудник еще работает в компании, то в поле с датой окончания работы вписываем формулу СЕГОДНЯ):

Если мы хотим рассчитать стаж в Excel в полном формате, например «1 г. 2 мес. 3 дн.«, то запишем формулу стажа работы в следующем виде:

=РАЗНДАТ(B2;C2;»y») & » г. » & РАЗНДАТ(B2;C2;»ym») & » мес. » & РАЗНДАТ(B2;C2;»md») & » дн.»

В результате подсчета стажа получаем:

Как посчитать стаж с перерывами в Excel?

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

Для простоты рассмотрим, что сотрудник работал 2 периода и сначала для каждого из них рассчитаем стаж работы:

Для подсчета общего стажа за 2 периода воспользуемся простой математикой, а именно к концу первого периода (дате увольнения) прибавим длительность второго (разность между увольнением и приемом).

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

Если нужно посчитать длительность работы за 3 и более периода, то эту формулу можно модифицировать точно по такому же принципу.

Как посчитать возраст в Excel?

Предположим, что у нас есть таблица с датами рождения сотрудников в компании:

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

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:

Поиск по сайту:

Вычисление разности двух дат — Excel

Используйте ЧИСТРАБДНИ. INTL, если требуется вычислить количество рабочих дней между двумя датами. Кроме того, вы можете также исключить выходные и праздничные дни.

Прежде чем начать, выполните указанные ниже действия.Решите, нужно ли исключить даты праздников. Если это так, введите список дат праздников в отдельную область или на лист. Каждый день праздников помещается в отдельную ячейку. Затем выделите эти ячейки, а затем выберите формулы > задать имя. Назовите диапазон михолидайси нажмите кнопку ОК. Затем создайте формулу, выполнив указанные ниже действия.

1. Введите дату начала и дату окончания.

В этом примере Дата начала находится в ячейке D53, а Дата окончания — в ячейке E53.

2. в другой ячейке введите формулу, например:

Введите формулу, например приведенный выше пример. 1 в формуле определяет «Суббота» и «воскресенье» в качестве выходных дней и исключает их из итогового значения.

Примечание. в Excel 2007 нет ЧИСТРАБДНИ. МЕЖД. Однако у него есть ЧИСТРАБДНИ. Приведенный выше пример будет выглядеть следующим образом в Excel 2007: = ЧИСТРАБДНИ (D53, E53). Вы не укажете 1, так как ЧИСТРАБДНИ предполагает, что выходные дни — суббота и воскресенье.

3. при необходимости измените значение 1.

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

Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ в Excel 2007 всегда предполагает, что выходные дни — суббота и воскресенье.

4. Введите имя диапазона праздников.

Если вы создали имя диапазона праздников в приведенном выше разделе «Начало работы», введите его в конце, как показано ниже. Если у тебя нет праздников, вы можете покинуть запятую и Михолидайс. Если вы используете Excel 2007, вышеприведенный пример будет выглядеть следующим образом: = ЧИСТРАБДНИ (D53, E53, михолидайс).

ПероЕсли вы не хотите ссылаться на имя диапазона праздников, вы также можете ввести диапазон, например D35: E:39. Кроме того, вы можете ввести каждый праздник в формуле. Например, если праздничные дни – 1 января и 2 из 2016, введите их следующим образом: = ЧИСТРАБДНИ. Межд (D53, E53, 1, {«1/1/2016», «1/2/2016»}). В Excel 2007 оно будет выглядеть следующим образом: = ЧИСТРАБДНИ (D53, E53, {«1/1/2016», «1/2 . 2016″})

Как рассчитать возраст в Excel от дня рождения

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

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

Как рассчитать возраст по дате рождения в Excel

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

Основная формула Excel для определения возраста в годах

Как обычно можно определить возраст человека? Просто вычтя дату рождения из текущей даты.Эту обычную формулу возраста также можно использовать в Excel.

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

= (СЕГОДНЯ () - B2) / 365

Первая часть формулы (СЕГОДНЯ () — B2) возвращает разницу между текущей датой и датой рождения в днях, а затем вы делите это число на 365, чтобы получить количество лет.

Формула очевидна и легко запоминается, однако есть небольшая проблема.В большинстве случаев он возвращает десятичное число, как показано на снимке экрана ниже.

Чтобы отобразить количество полных лет, используйте функцию INT для округления десятичной дроби до ближайшего целого числа:

= ЦЕЛОЕ ((СЕГОДНЯ () - B2) / 365)

Недостатки: Использование этой формулы возраста в Excel дает довольно точные результаты, но не безупречные. Деление на среднее количество дней в году в большинстве случаев работает нормально, но иногда бывает неверно указать возраст.Например, если кто-то родился 29 февраля, а сегодня 28 февраля, формула сделает человека на день старше.

В качестве альтернативы вы можете разделить на 365,25 вместо 365, поскольку в каждом четвертом году 366 дней. Однако и этот подход не идеален. Например, если вы вычисляете возраст ребенка, который еще не переживал високосный год, деление на 365,25 дает неправильный результат.

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

Вычислить возраст от даты рождения с помощью функции ГОДА

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

Синтаксис функции ГОД имеет следующий вид:

YEARFRAC (начальная_дата, конечная_дата, [основание])

Первые два аргумента очевидны и не требуют дополнительных пояснений. Basis — это необязательный аргумент, определяющий используемую основу подсчета дней.

Чтобы получить абсолютно верную формулу возраста, укажите в функции ГОД РАСЧЕТ следующие значения:

• Start_date — дата рождения.
• End_date — Функция СЕГОДНЯ () для возврата сегодняшней даты.
• Основа — используйте основу 1 , которая указывает Excel разделить фактическое количество дней в месяце на фактическое количество дней в году.

Учитывая вышеизложенное, формула Excel для расчета возраста с даты рождения выглядит следующим образом:

YEARFRAC ( дата рождения , СЕГОДНЯ (), 1)

Предполагая, что дата рождения находится в ячейке B2, формула принимает следующий вид:

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

Как и в предыдущем примере, результат функции YEARFRAC также является десятичным числом. Чтобы исправить это, используйте функцию ROUNDDOWN с 0 в последнем аргументе, потому что вам не нужны десятичные знаки.

Итак, вот улучшенная формула YEARFRAC для расчета возраста в Excel:

= ОКРУГЛ ВНИЗ (YEARFRAC (B2, TODAY (), 1), 0)

Рассчитать возраст в Excel с РАЗНДАТОМ

Еще один способ конвертировать дату рождения в возраст в Excel — использовать функцию РАЗНДАТ:

РАЗНДАТ (начальная_дата, конечная_дата, единица измерения)

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

• Y — возвращает количество полных лет между начальной и конечной датами.
• M — возвращает количество полных месяцев между датами.
• D — возвращает количество дней между двумя датами.
• YM — возвращает месяцев без учета дней и лет.
• MD — возвращает разницу в дней без учета месяцев и лет.
• YD — возвращает разницу в дней без учета лет.

Поскольку мы стремимся вычислить возраст в годах , мы используем единицу «y»:

РАЗНДАТ ( дата рождения , СЕГОДНЯ (), «y»)

В этом примере DOB находится в ячейке B2, и вы ссылаетесь на эту ячейку в формуле возраста:

= РАЗНДАТ (B2; СЕГОДНЯ (); "y")

В этом случае дополнительная функция округления не требуется, поскольку формула РАЗНДАТ с единицей «y» вычисляет количество полных лет:

Как узнать возраст по дате рождения в годах, месяцах и днях

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

1. Чтобы получить количество лет: = РАЗНДАТ (B2, СЕГОДНЯ (), "Y")
2. Чтобы получить количество месяцев: = РАЗНДАТ (B2, СЕГОДНЯ (), "ГМ")
3. Чтобы получить количество дней: = РАЗНДАТ (B2, СЕГОДНЯ (), "MD")

Где B2 — дата рождения.

Затем объедините вышеуказанные функции в одну формулу, например:

= РАЗНДАТ (B2, СЕГОДНЯ (), «Y») и РАЗНДАТ (B2, СЕГОДНЯ (), «YM») и РАЗНДАТ (B2, СЕГОДНЯ (), «MD»)

Приведенная выше формула возвращает 3 числа (года, месяца и дня), объединенных в одну текстовую строку, как показано на снимке экрана ниже:

В этом нет особого смысла, а? Чтобы результаты были более значимыми, разделите числа запятыми и определите, что означает каждое значение:

= РАЗНДАТ (B2, СЕГОДНЯ (), «Г») и «Годы» & РАЗНДАТ (B2, СЕГОДНЯ (), «ГМ») и «Месяцы» & РАЗНДАТ (B2, СЕГОДНЯ (), «МД» ) & "Дни"

Результат выглядит намного лучше:

Формула отлично работает, но вы можете улучшить ее, скрыв нулевые значения.Для этого добавьте 3 оператора IF, которые проверяют наличие нулей, по одному для каждого DATEDIF:

.

= ЕСЛИ (РАЗНДАТ (B2, СЕГОДНЯ (), «y») = 0, «», РАЗНДАТ (B2, СЕГОДНЯ (), «y») & «годы») & ЕСЛИ (РАЗНДАТ (B2, СЕГОДНЯ ( ), "ym") = 0, "", DATEDIF (B2, TODAY (), "ym") & "months,") & IF (DATEDIF (B2, TODAY (), "md") = 0, "" , РАЗНДАТ (B2; СЕГОДНЯ (), «мд») & «дни»)

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

Наконечник. Если вы ищете формулу Excel для вычисления возраста в лет и месяцев, возьмите приведенную выше формулу и удалите последний блок IF (DATEDIF ()), который вычисляет дни.

Специальные формулы для расчета возраста в Excel

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

Как рассчитать возраст на определенную дату в Excel

Если вы хотите узнать возраст человека на определенную дату, используйте формулу возраста РАЗНДАТ, описанную выше, но замените функцию СЕГОДНЯ () в аргументе 2 ^nd конкретной датой.

Предполагая, что дата рождения находится в B1, следующая формула вернет возраст на 1 января 2020 года:

= РАЗНДАТ (B1, «01.01.2020», «Г») и «Годы» & РАЗНДАТ (B1, «01.01.2020», «ГМ») и «Месяцы» & РАЗНДАТ (B1 , «01.01.2020», «MD») и «Дни»

Чтобы сделать формулу возраста более гибкой, вы можете ввести дату в какую-либо ячейку и указать эту ячейку в формуле:

= РАЗНДАТ (B1, B2, «Y») и «Годы» & РАЗНДАТ (B1, B2, «YM») и «Месяцы» и РАЗНДАТ (B1, B2, «MD») и «Дни»

Где B1 — дата рождения, а B2 — дата, на которую нужно рассчитать возраст.

Вычислить возраст в определенном году

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

Допустим, вы работаете с медицинской базой данных, и ваша цель — узнать возраст пациентов на момент их последнего полного медицинского обследования.

Предполагая, что даты рождения находятся в столбце B, начиная со строки 3, а год последнего медицинского осмотра указан в столбце C, формула расчета возраста выглядит следующим образом:

= РАЗНДАТ (B3, ДАТА (C3, 1, 1), «y»)

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

Функция ДАТА извлекает год из ячейки B3, составляет полную дату, используя введенные вами числа месяца и дня (1 января в этом примере), и передает эту дату в DATEDIF. В результате вы получите возраст пациента на 1 января конкретного года:

Узнать дату, когда человеку исполнится N лет

Предположим, ваш друг родился 8 марта 1978 года. Как узнать, когда ему исполнится 50 лет? Обычно вы просто добавляете 50 лет к дате рождения человека.В Excel вы делаете то же самое, используя функцию ДАТА:

= ДАТА (ГОД (B2) + 50, МЕСЯЦ (B2), ДЕНЬ (B2))

Где В2 — дата рождения.

Вместо того, чтобы жестко указывать количество лет в формуле, вы можете указать определенную ячейку, в которой пользователи могут ввести любое количество лет (F1 на скриншоте ниже):

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

Когда дата рождения разделена на 3 разных ячейки (например, год в B3, месяц в C3 и день в D3), вы можете рассчитать возраст следующим образом:

• Получите дату рождения с помощью функций DATE и DATEVALUE:
  DATE (B3, MONTH (DATEVALUE (C3 & "1")), D3)
• Вставьте приведенную выше формулу в РАЗНДАТ, чтобы вычислить возраст от даты рождения в годах, месяцах и днях:

  = РАЗНДАТ (ДАТА (B3, МЕСЯЦ (ДАТАЗНАЧЕНИЕ (C3 & "1")), D3), СЕГОДНЯ (), "y") & "Годы" & РАЗНДАТ (ДАТА (B3, МЕСЯЦ (ДАТАЗНАЧЕНИЕ (C3 & "1 ")), D3), СЕГОДНЯ ()," гм ") и" Месяцы ", & РАЗНДАТ (ДАТА (B3, МЕСЯЦ (ДАТАЗНАЧЕНИЕ (C3 &" 1 ")), D3), СЕГОДНЯ ()," мд ") & «Дни»

Калькулятор возраста в Excel

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

Как создать калькулятор возраста в Excel

Теперь, когда вы знаете, как составить формулу возраста в Excel, вы можете создать собственный калькулятор возраста, например этот:

То, что вы видите выше, представляет собой встроенный лист Excel Online, поэтому не стесняйтесь вводить дату своего рождения в соответствующую ячейку, и вы сразу же узнаете свой возраст.

Калькулятор использует следующие формулы для вычисления возраста на основе даты рождения в ячейке A3 и сегодняшней даты.

• Формула в B5 вычисляет возраст в годах, месяцах и днях:

  = РАЗНДАТ (B2, СЕГОДНЯ (), «Г») и «Годы» & РАЗНДАТ (B2, СЕГОДНЯ (), «ГМ») и «Месяцы» & РАЗНДАТ (B2, СЕГОДНЯ (), «МД» ) & "Дни"

• Формула в B6 вычисляет возраст в месяцах:

  = РАЗНДАТ ($ B $ 3, СЕГОДНЯ (), «м»)

• Формула в B7 вычисляет возраст в днях:

  = РАЗНДАТ ($ B $ 3, СЕГОДНЯ (), "d")

Если у вас есть опыт работы с элементами управления Excel Form, вы можете добавить параметр для вычисления возраста на определенную дату, как показано на следующем снимке экрана:

Для этого добавьте пару кнопок выбора ( вкладка разработчика > Вставка > Элементы управления формы > Кнопка выбора ) и свяжите их с какой-либо ячейкой.А затем напишите формулу ЕСЛИ / РАЗНДАТ, чтобы получить возраст на сегодняшний день или на дату, указанную пользователем.

Формула работает по следующей логике:

• Если установлен флажок Сегодняшняя дата , в связанной ячейке (I5 в этом примере) отображается значение 1, и формула возраста рассчитывается на основе сегодняшней даты:

  ЕСЛИ ($ I $ 5 = 1, РАЗНДАТ ($ B $ 3, СЕГОДНЯ (), "Y") & "Годы" & РАЗНДАТ ($ B $ 3, СЕГОДНЯ (), "ГМ") & "Месяцы" & РАЗНДАТ ($ B $ 3, СЕГОДНЯ (), "MD") & "Дни")

• Если выбрана кнопка выбора Определенная дата И дата введена в ячейку B7, возраст рассчитывается на указанную дату:

  ЕСЛИ (ISNUMBER (B $ 7), РАЗНДАТ (3 $ B $, 7 $ B $, «Y») и «Годы» & РАЗНДАТ (3 $ B $, 7 B $, «YM») & «Месяцы» & РАЗНДАТ (3 $ B $, 7 $ B $, "MD") & "Days", ""))

Наконец, вложите указанные выше функции друг в друга, и вы получите полную формулу расчета возраста (в B9):
= ЕСЛИ ($ I $ 5 = 1, РАЗНДАТ ($ B $ 3, СЕГОДНЯ (), "Y" ) & "Годы" & РАЗНДАТ ($ B $ 3, СЕГОДНЯ (), "ГМ") & "Месяцы" & РАЗНДАТ ($ B $ 3, СЕГОДНЯ (), "МД") & "Дни", ЕСЛИ (ISNUMBER ( $ B $ 7), РАЗНДАТ ($ B $ 3, $ B $ 7, «Y») и «Годы» и РАЗНДАТ ($ B $ 3, $ B $ 7, «YM») и «Месяцы» & РАЗНДАТ ($ B $ 3 , $ B $ 7, "MD") и "Days", ""))

Формулы в B10 и B11 работают по той же логике.Конечно, они намного проще, потому что они включают только одну функцию РАЗНДАТ, чтобы вернуть возраст как количество полных месяцев или дней, соответственно.

Чтобы узнать подробности, я предлагаю вам загрузить этот калькулятор возраста в Excel и изучить формулы в ячейках B9: B11.

Скачать калькулятор возраста для Excel

Готовый калькулятор возраста для Excel

Пользователям нашего Ultimate Suite for Excel не нужно беспокоиться о создании своего собственного калькулятора возраста в Excel — это всего лишь пара щелчков мышью:

1. Выберите ячейку, в которую вы хотите вставить формулу возраста, перейдите на вкладку Ablebits Tools > Date & Time group и нажмите кнопку Date & Time Wizard .
2. Запустится мастер даты и времени, и вы перейдете прямо на вкладку Возраст .
3. На вкладке Age вы можете указать 3 параметра:
   • Дата рождения в виде ссылки на ячейку или дату в формате мм / дд / гггг.
   • Возраст на сегодняшнюю дату или конкретную дату .
   • Выберите, следует ли рассчитывать возраст в днях, месяцах, годах или точном возрасте.
4. Нажмите кнопку Вставить формулу .

Готово!

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

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

Если вы хотите избавиться от нулевых единиц, таких как «0 дней», установите флажок Не показывать нулевые единицы :

Если вам интересно протестировать этот калькулятор возраста, а также открыть для себя еще 60 надстроек для экономии времени для Excel, вы можете загрузить пробную версию нашего Ultimate Suite.Если вам нравятся инструменты и вы решили получить лицензию, не забудьте получить код купона на скидку 15%, который мы предоставляем нашим ценным читателям блога: AB14-BlogSpo

Как выделить определенный возраст (младше или старше определенного возраста)

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

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

• Чтобы выделить возраст, равный или более 18 лет: = $ C2> = 18
• Чтобы выделить возраст до 18 лет: = $ C2 <18

Где C2 — самая верхняя ячейка в столбце «Возраст» (без заголовка столбца).

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

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

• Чтобы выделить возраст до 18 (желтый):

  = РАЗНДАТ ($ B2, СЕГОДНЯ (), «Y») <18

• Чтобы выделить возраст от 18 до 65 лет (зеленый):

  = И (РАЗНДАТ ($ B2, СЕГОДНЯ (), «Y»)> = 18, РАЗНДАТ ($ B2, СЕГОДНЯ (), «Y») <= 65)

• Чтобы выделить возраст старше 65 (синий):

  = РАЗНДАТ ($ B2, СЕГОДНЯ (), «Y»)> 65

Чтобы создать правила на основе приведенных выше формул, выберите ячейки или целые строки, которые вы хотите выделить, перейдите на вкладку Home > Styles и нажмите Conditional Formatting > New Rule… > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать .

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

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

Вас также может заинтересовать:

.

Как рассчитать возраст по дате рождения в Excel

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

Для вычисления возраста по дате рождения мы будем использовать функцию СЕГОДНЯ () вместе с функцией INT в Excel 2016.

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

= Сегодня ()

Теперь давайте рассмотрим это на примере.

Здесь у нас есть список детей Неда Старка с указанием даты их рождения.

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

Формула для определения возраста в Excel:

= INT ((СЕГОДНЯ () - C4) / 365)

Пояснение:
СЕГОДНЯ () - C4 вычисляет номер. дней между сегодняшним днем ​​и датой рождения.

Делим число 365, чтобы получить количество лет между сегодняшним днем ​​и датой рождения.Функция INT вынимает из значения целую часть.

Да. Мы узнали возраст Джона Сноу ..

года.

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

Как видите, мы получили возраст детей Неда Старка с помощью простой формулировки. При каждом использовании листа возраст будет обновляться.
Есть еще один способ использования функции DATEIF .

Функция РАЗНДАТ возвращает количество лет, месяцев и дней между заданными двумя датами.
Синтаксис:

= РАЗНДАТ (начальная_дата, конечная_дата, единица)

Мы будем использовать формулу, чтобы получить возраст детей

Формула:

= РАЗНДАТ (C4; СЕГОДНЯ (); «y»)

Дата рождения - это начальная дата в ячейке C4.
Today () функция используется для получения сегодняшней даты в качестве даты окончания.
«Y», чтобы получить возраст в годах.

Да. Мы получили возраст Джона Сноу с помощью функции РАЗНДАТ .

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

Надеюсь, вы поняли, как рассчитать возраст по дате рождения. Эти задачи можно выполнять в книгах Excel 2013 и 2010. Пожалуйста, поделитесь любым вопросом ниже в поле для комментариев.Мы поможем вам.

Популярные статьи:

50 ярлыков Excel для повышения производительности

Как использовать функцию ВПР в Excel

Как использовать функцию СЧЁТЕСЛИ в Excel

Как использовать функцию СУММЕСЛИ в Excel

.

Excel YEAR функция - преобразовать дату в год и вычислить возраст от даты рождения

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

В нескольких недавних публикациях мы исследовали различные способы вычисления даты и времени в Excel и узнали множество полезных функций, таких как WEEKDAY, DAY, MONTH и WEEKNUM.Сегодня мы собираемся сосредоточиться на более крупной единице времени и поговорим о подсчете лет в ваших таблицах Excel.

Из этого руководства вы узнаете:

Функция

ГОД в Excel

Функция ГОД возвращает год из четырех цифр, соответствующий указанной дате, как целое число от 1900 до 9999.

Синтаксис функции Excel ГОД настолько прост, насколько это возможно:

ГОД (серийный_номер)

Где серийный_номер - любая допустимая дата года, который вы хотите найти.В формулах Excel YEAR вы можете указать даты несколькими способами:

• Использование ДАТЫ Например, следующая формула возвращает год для 28 апреля 2015 г .:
  = ГОД (ДАТА (2015,4,28))
• Как серийный номер , представляющий дату (для получения дополнительной информации о том, как даты хранятся в Excel, см. Формат даты Excel). 28 апреля 2015 г. сохраняется как 42122, поэтому вы можете ввести это число непосредственно в формулу:
  = ГОД (42122)

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

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

Простые формулы ГОДА могут даже понимать даты, введенные в виде текста, например = ГОД («28-апр-2015») . Однако Microsoft не гарантирует правильных результатов, если дата предоставляется в виде текстового значения.

На следующем снимке экрана показаны все вышеперечисленные формулы ГОДА в действии, все возвращают 2015 год, как и следовало ожидать 🙂

Как преобразовать дату в год в Excel

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

Пример 1. Извлечь год из даты с помощью функции ГОД

Фактически, вы уже знаете, как использовать функцию ГОД в Excel для преобразования даты в год. На приведенном выше снимке экрана показано несколько формул, а на снимке экрана ниже вы можете увидеть еще несколько примеров.Обратите внимание, что функция ГОД прекрасно понимает даты во всех возможных форматах:

Пример 2. Преобразование даты в месяц и год в Excel

Чтобы преобразовать заданную дату в год и месяц, вы можете использовать функцию ТЕКСТ, чтобы извлечь каждую единицу по отдельности, а затем объединить эти функции в одну формулу.

В функции ТЕКСТ вы можете использовать разные коды месяцев и лет, например:

• «ммм» - сокращенные названия месяцев, начиная с января по декабрь

г.

• «мммм» - полные названия месяцев, например, с января по декабрь.
• «yy» - год двузначный
• «гггг» - год из четырех цифр

Чтобы сделать вывод более читаемым, вы можете разделить коды запятой, дефисом или любым другим символом, как в следующих формулах Date to Month и Year :

= ТЕКСТ (B2, «мммм») & «,» & ТЕКСТ (B2, «гггг»)

или

= ТЕКСТ (B2, «ммм») & «-» & ТЕКСТ (B2, «гг»)

Где B2 - ячейка, содержащая дату.

Пример 3.Отображение даты в виде

года

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

В этом случае формула не требуется. Просто откройте диалоговое окно Format Cells , нажав Ctrl + 1, выберите категорию Custom на вкладке Number и введите один из приведенных ниже кодов в поле Type :

• гг - для отображения 2-значного года, как 00-99.
• гггг - для отображения 4-значного года, как 1900 - 9999.

Помните, что этот метод не изменяет исходную дату , он только изменяет способ отображения даты на вашем листе. Если вы ссылаетесь на такие ячейки в своих формулах, Microsoft Excel будет выполнять вычисления даты, а не года.

Вы можете найти более подробную информацию об изменении формата даты в этом руководстве: Как изменить формат даты в Excel.

Как рассчитать возраст по дате рождения в Excel

Есть несколько способов рассчитать возрастную форму даты рождения в Excel - с помощью функции РАЗНДАТ, ГОД или ЦЕЛОЕ в сочетании с СЕГОДНЯ ().Функция СЕГОДНЯ предоставляет дату для расчета возраста, гарантируя, что ваша формула всегда будет возвращать правильный возраст.

Вычислить возраст с даты рождения (DOB) в годах

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

INT ((СЕГОДНЯ () - DOB ) / 365)

Первая часть формулы (СЕГОДНЯ () - B2) вычисляет разницу в днях, и вы делите ее на 365, чтобы получить количество лет.В большинстве случаев результатом этого уравнения является десятичное число, и функция INT округляет его до ближайшего целого числа.

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

= INT ((СЕГОДНЯ () - B2) / 365)

Как упоминалось выше, эта формула расчета возраста не всегда безупречна, и вот почему. Каждые 4 ^-го года - это високосный год, содержащий 366 дней, тогда как формула делит количество дней на 365.Итак, если кто-то родился 29 февраля, а сегодня 28 февраля, эта формула возраста сделает человека на день старше.

Деление на 365,25 вместо 365 тоже не безупречно, например, при подсчете возраста ребенка, который еще не прожил високосный год.

Учитывая вышеизложенное, лучше сохранить этот способ расчета возраста для нормальной жизни и использовать одну из следующих формул для расчета возраста по дате рождения в Excel.

РАЗНДАТ ( ДОБ , СЕГОДНЯ (), "y")

КРУГЛЫЙ ВНИЗ (YEARFRAC ( DOB , TODAY (), 1), 0)

Подробное объяснение приведенных выше формул представлено в разделе Как рассчитать возраст в Excel.Следующий снимок экрана демонстрирует формулу расчета возраста в реальной жизни:

= РАЗНДАТ (B2; СЕГОДНЯ (); "y")

Расчет точного возраста по дате рождения (в годах, месяцах и днях)

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

• Y - для расчета количества полных лет.
• YM - чтобы получить разницу месяцев без учета лет.
• MD - для получения разницы между днями без учета лет и месяцев.

Затем объедините 3 функции DATEDIF в одну формулу, разделите числа, возвращаемые каждой функцией, запятыми и определите, что означает каждое число.

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

= РАЗНДАТ (B2, СЕГОДНЯ (), «Y») и «Годы» & РАЗНДАТ (B2, СЕГОДНЯ (), «ГМ») и «Месяцы» & РАЗНДАТ (B2, СЕГОДНЯ (), «МД» ) & "Дни"

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

Для получения дополнительных примеров формул, таких как расчет возраста на определенную дату или в определенный год, ознакомьтесь со следующим учебным пособием: Как рассчитать возраст в Excel - примеры формул.

Расчет високосных лет в Excel

Как известно, почти каждый 4 ^{-й год} имеет дополнительный день 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 - ячейка, содержащая дату.

Формула возвращает следующие результаты:

В качестве альтернативы вы можете использовать функцию EOMONTH, чтобы вернуть последний день февраля и сравнить это число с 29:

.

= ДЕНЬ (EOMONTH (DATE (YEAR (A2), 2,1), 0)) = 29

Чтобы сделать формулу более удобной для пользователя, используйте функцию ЕСЛИ, чтобы она возвращала, скажем, «Високосный год» и «Общий год» вместо ИСТИНА и ЛОЖЬ:

= ЕСЛИ (ДЕНЬ (ДАТА (ГОД (A2), 3,1) -1) = 29, «Високосный год», «Обычный год»)

= ЕСЛИ (ДЕНЬ (EOMONTH (ДАТА (ГОД (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. Благодарю вас за чтение и с нетерпением жду встречи с вами на следующей неделе.

Вас также может заинтересовать:

.

Как рассчитать возраст по дате рождения в Excel 2013

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

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

Краткое описание - Как определить возраст по дате рождения в Excel 2013

1. Откройте Excel 2013.
2. Введите дату рождения в ячейку в формате ММ / ДД / ГГГГ (если в США) или в формате ДД / ММ / ГГГГ, если в вашей стране используется этот формат.
3. Введите = РАЗНДАТ (XX, СЕГОДНЯ (), «Y») , затем нажмите . Введите на клавиатуре. Измените часть формулы «XX» на ячейку, содержащую дату рождения.

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

Expanded - Как рассчитать возраст в Excel 2013

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

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

Шаг 1. Откройте Excel 2013.

Шаг 2. Введите дату рождения в ячейку в формате ММ / ДД / ГГГГ (США) или ДД / ММ / ГГГГ, если в вашей стране используется этот формат даты.

Шаг 3. Введите = РАЗНДАТ (XX, Сегодня (), «Y») в ячейку, где вы хотите отобразить возраст, затем нажмите . Введите на клавиатуре. Обязательно замените «XX» в формуле на ячейку, содержащую дату рождения.

Дополнительная информация

• Если вы вводите дату рождения в указанном выше формате, Excel должен иметь возможность автоматически определить, что это дата.Однако, если вы используете другой формат даты, с которым Excel, похоже, борется, или если данные форматируются другим способом, щелкните правой кнопкой мыши ячейку с датой рождения, выберите Формат ячеек , затем выберите Дата в левой части окна и выберите правильный формат в зависимости от того, как вводятся ваши данные.
• Если вы хотите отображать возраст в месяцах, измените формулу на = РАЗНДАТ (XX, СЕГОДНЯ (), «M»)
• Если вы хотите отображать возраст в днях, измените формулу на быть = РАЗНДАТ (XX, СЕГОДНЯ (), «D»)
• Если вы хотите вычислить, какой был возраст человека на определенную дату, в прошлом или будущем, измените формулу на = РАЗНДАТ (XX, «ММ / ДД / ГГГГ», «Г»)
• Обратите внимание, что вычисление по этой формуле не будет работать, если начальная дата наступит до 01.01.1900.

Функция сортировки в Excel - еще один полезный инструмент, и вы можете использовать его для сортировки столбцов с датами. Узнайте, как сортировать по дате в Excel, если вам это необходимо.

.