При работе с персональными данными часто возникает необходимость быстро узнать дату рождения человека. Это особенно актуально при обработке больших массивов информации в кадровом учёте, анкетировании, статистическом анализе или при составлении целевых списков. Если у вас есть таблица с идентификационными номерами (ИНН) и вы хотите расшифровать их, то в Excel есть три формулы, каждая из которых рассчитывает дату рождения по коду. Это позволяет быстро узнать, сколько лет человеку, без сложных ручных вычислений.
В статье вы найдете не только формулы, но и объяснение, как они работают. Это сэкономит ваше время и сделает работу в Excel ещё эффективнее.
ТОП-3 способа определить дату рождения по ИНН в Excel
Идентификационный номер (ИНН), или Регистрационный номер учетной карточки налогоплательщика (РНУКПН) в Украине, состоит из 10 цифр. Первые пять цифр кода — это количество дней от 01.01.1900 года до дня рождения человека. Таким образом, зная эти пять цифр, мы можем прибавить их к 01.01.1900 и получить дату рождения.
Пример.
Если у человека такой регистрационный номер: 3317514741, то 33175 — это количество дней от 1900 года до дня его рождения. Если прибавить 33175 дней к 01.01.1900, получим 30.10.1990. Это и есть дата рождения человека.
Однако делать такие расчеты вручную довольно сложно. Можно допустить ошибку. А если у вас большие списки, их обработка займет много времени. Поэтому проще всего использовать формулы Excel, которые автоматически расшифруют идентификационный номер и определят дату рождения.
Формула Excel №1 для определения даты рождения по ИНН
=VALUE(LEFT(TEXT(A2;0);5))+1Эта формула возвращает порядковый номер даты рождения на основе первых пяти цифр ИНН, которые обозначают количество дней, прошедших с 01.01.1900.
Пояснение:
- Функция TEXT преобразует значение из ячейки A2 в текстовый формат без десятичных знаков.
- Функция LEFT извлекает первые 5 символов.
- Функция VALUE преобразует эти символы в число (так как они в текстовом формате).
- В Excel отсчет начинается с 01.01.1900 = 1, но иногда при генерации ИНН считается, что 01.01.1900 = 0. Поэтому +1 компенсирует это смещение.
Внимание!
- Формула работает в украинской и англоязычной (европейской) версии Excel.
- В русской версии Excel формула выглядит так =ЗНАЧЕН(ЛЕВСИМВ(ТЕКСТ(A2;0);5))+1
- В англоязычной версии для США аргументы разделяются запятыми =VALUE(LEFT(TEXT(A2,0),5))+1
Формула Excel №2 для расчёта даты рождения по РНУКПН
=TEXT(VALUE(LEFT(A2;5))+1;"dd.mm.yyyy")Пояснение:
- Функция LEFT(A2;5) извлекает пять первых символов из кода который содержит ячейка A2.
- Функция VALUE преобразует эти символы в число.
- С помощью +1 компенсируется смещение на один день, если Excel воспринимает 01.01.1900 как 0.
- Функция TEXT преобразовывает полученное число в текст в формате dd.mm.yyyy.
Внимание!
- Приведенная выше формула работает в украинской и англоязычной европейской версии Excel.
- В русской версии Excel формула =TEXT(VALUE(LEFT(A2;5))+1; «dd.mm.yyyy») имеет другой вид: =ТЕКСТ(ЗНАЧЕН(ЛЕВСИМВ(A2;5))+1;"дд.мм.гггг").
- В англоязычной версии Excel для США и некоторых других регионов, чтобы разделить аргументы, вместо точки с запятой (;) используют запятую (,) =TEXT(VALUE(LEFT(A2,5))+1,"dd.mm.yyyy").
Формула Excel №3 для расчета даты рождения по коду
=DATE(1900;1;1) + VALUE(LEFT(A2;5))Пояснение:
- С помощью функции LEFT(A2;5) получаем первые пять символов из кода который содержится в ячейке A2.
- Функция VALUE преобразует эти символы в число.
- Функция DATE(1900;1;1) возвращает дату 01.01.1900 .
- Последним происходит сложение левой и правой части формулы (к 01.01.1900 добавляется количество дней).
Внимание!
- Приведённая выше формула работает в украинской и англоязычной европейской версии Excel.
- В русской версии Excel формула =DATE(1900;1;1) + VALUE(LEFT(A2;5)) имеет другой вид: =ДАТА(1900,1,1) + ЗНАЧЕН(ЛЕВСИМВ(A2;5)).
- В англоязычной версии Excel для США и некоторых других регионов, чтобы разделить аргументы, вместо точки с запятой (;) используют запятую (,) =DATE(1900,1,1) + VALUE(LEFT(A2,5)).
Угостить автора чашечкой кофе