У роботі з персональними даними часто виникає потреба швидко визначити дату народження людини. Особливо це актуально при обробці великих масивів інформації у кадровому обліку, анкетуванні, статистичному аналізі або під час складання цільових списків. Якщо у вас є таблиця з ідентифікаційними номерами (ІПН) і ви хочете розшифрувати їх, то в 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Цією формулою ми отримуємо порядковий номер дати народження людини на основі її ідентифікаційного номеру (ІПН), де перші 5 цифр — це кількість днів, що пройшли від 01.01.1900 до дня народження.
Пояснення:
- За допомогою функції TEXT відбувається перетворення значення з комірки A2 у текстовий формат без десяткових знаків.
- Функція LEFT витягує перші 5 символів з отриманого тексту.
- Функція VALUE перетворює перші 5 символів в число (бо зараз вони мають текстовий формат).
- В Excel відлік починається з 01.01.1900 і цей день дорівнює одиниці (1), але іноді під час розшифрування ІПН вважається, що 01.01.1900 — це нульовий день (0). Тому, щоб компенсувати зміщення і отримати правильну дату, в кінці формули є +1.
Увага!
- Наведена вище формула працює в українській та англомовній європейській версії Excel.
- У російській версії Excel формула =VALUE(LEFT(TEXT(A2;0);5))+1 має інший вигляд: =ЗНАЧЕН(ЛЕВСИМВ(ТЕКСТ(A2;0);5))+1.
- В англомовній версії Excel для США та деяких інших регіонів, щоб розділити аргументи, замість крапки з комою (;) використовують кому (,) =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)).
Пригостити автора кавою