Как в эксель из иин сделать дату рождения
Дата и время в Excel – это числа сформатированные специальным образом. Датой является целая часть числа, а время (часы и минуты) – это дробная часть.
По умолчанию число 1 соответствует дате 01 января 1900 года. То есть каждая дата – это количество дней прошедших от 01.01.1900. На данном уроке детально рассмотрим даты, а на следующих уроках – время.
Как в Excel посчитать дни между датами?
Так как дата является числом, значит можно проводить с ним математические вычислительные и расчетные операции. Посчитать количество дней между двумя датами Excel не составляет особых проблем. Для наглядного примера сначала выполним сложение, а потом вычитание дат. Для этого:
- На чистом листе в ячейку А1 введите текущею дату нажав CTRL+;.
- В ячейке A2 введите промежуточный период в днях, например 127.
- В ячейку A3 введите формулу:=A1+A2.
Обратите внимание, формат «Дата» автоматически присвоился для ячейки A3. Не сложно догадаться, чтобы посчитать разницу в датах в Excel нужно от новейшей даты отнять старшую дату. В ячейке B1 введите формулу:=A3-A1. Соответственно мы получаем количество дней между этими двумя датами.
Вычисление возраста по дате рождения в Excel
Теперь мы научимся вычислять возраст по дате рождения:
- На новый лист в ячейки A1:A3 введите даты: 18.04.1985; 17.08.1977; 08.12.1992
- В ячейки B1:B3 проставьте текущею дату.
- Теперь нужно воспользоваться функцией по преобразованию количества дней в количество лет. Для этого вручную введите в диапазон C1:C3 следующее значение: =РАЗНДАТ(A1;B1;"y").
Таким образом, применение функции позволило нам точно вычислить возраст по дате рождения в 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 день.
Способ отображения даты можно задать с помощью диалогового окна «Формат ячеек». Для его вызова нажмите: CTRL+1. На закладке «Число» выберите в списке «Числовые форматы» - «Дата». В разделе «Тип» отображаются самые популярные форматы для отображения дат.
На следующем уроке мы на готовых примерах будем работать со временем и периодами суток.
На ближайшие 82 года :-) без расчета на рожденных в 19м веке
Если ничего не поменяют, то более универсально, хотя для 19го века не сработает, нижняя граница 1900 год. Тяжко аксакалам в excel.
Не дальновидно разработано. осталось 4 цифры незанятых или только до 23го века или . :-)
На ближайшие 82 года :-) без расчета на рожденных в 19м веке
Если ничего не поменяют, то более универсально, хотя для 19го века не сработает, нижняя граница 1900 год. Тяжко аксакалам в excel.
Не дальновидно разработано. осталось 4 цифры незанятых или только до 23го века или . :-) bmv98rus
Замечательный Временно просто медведь , процентов на 20.
На ближайшие 82 года :-) без расчета на рожденных в 19м веке
Если ничего не поменяют, то более универсально, хотя для 19го века не сработает, нижняя граница 1900 год. Тяжко аксакалам в excel.
Не дальновидно разработано. осталось 4 цифры незанятых или только до 23го века или . :-) Автор - bmv98rus
Дата добавления - 04.02.2018 в 18:37
Дата добавления - 04.02.2018 в 20:37 Pelena, вариант не учитывает, что в 7 символе век зашит , то есть 1701011 это 01.01.1817 а 2001015 уже 01.01.2017 Pelena, вариант не учитывает, что в 7 символе век зашит , то есть 1701011 это 01.01.1817 а 2001015 уже 01.01.2017 bmv98rus
Замечательный Временно просто медведь , процентов на 20.
Замечательный Временно просто медведь , процентов на 20.
При чём здесь обиды? Я вообще ни на кого никогда не обижаюсь, я делаю выводы
А по поводу формулы, так файл называется 2000-1991, под этот период моя формула подходит
При чём здесь обиды? Я вообще ни на кого никогда не обижаюсь, я делаю выводы
А по поводу формулы, так файл называется 2000-1991, под этот период моя формула подходит Pelena
А по поводу формулы, так файл называется 2000-1991, под этот период моя формула подходит Автор - Pelena
Дата добавления - 04.02.2018 в 22:33
= Мир MS Excel/Проверка ИИН - Мир MS Excel
Войти через uID
Войти через uID
Сделал проверку ИИН, данный проверка и расшифровка для Казахстана - остальных стран не знаю есть ли у них ИИН.Расшифровка ИИН: Так, 12 цифр ИИН можно разбить на 4 части. Первые шесть цифр - это год и дата рождения. Во второй, что состоит из одной цифры, зашифрованы пол и век рождения. Так, 1 - для мужчин, родившихся в XIX веке, 2 - для женщин, родившихся в XIX веке, 3 - для мужчин, родившихся в ХХ веке, 4 - для женщин, родившихся в ХХ веке, 5 - для мужчин, родившихся в XXI веке, и 6 - для женщин, родившихся в XXI веке Сделал проверку ИИН, данный проверка и расшифровка для Казахстана - остальных стран не знаю есть ли у них ИИН.
Расшифровка ИИН: Так, 12 цифр ИИН можно разбить на 4 части. Первые шесть цифр - это год и дата рождения. Во второй, что состоит из одной цифры, зашифрованы пол и век рождения. Так, 1 - для мужчин, родившихся в XIX веке, 2 - для женщин, родившихся в XIX веке, 3 - для мужчин, родившихся в ХХ веке, 4 - для женщин, родившихся в ХХ веке, 5 - для мужчин, родившихся в XXI веке, и 6 - для женщин, родившихся в XXI веке ABC
MS Excel 2007 and 2010.
-------------------------------
С Уважением, Даулет
В России и на Украине есть.
Думаю что везде есть, может только называется по другому.
В России и на Украине есть.
Думаю что везде есть, может только называется по другому. Serge_007
В России и на Украине есть.
Думаю что везде есть, может только называется по другому. Автор - Serge_007
Дата добавления - 28.07.2012 в 19:20 без пола
как время будет, добавлю пол и контрольный без пола
как время будет, добавлю пол и контрольный ABC
ужасно. Кто писал код?
ужасно. Кто писал код?
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук
YM 41001156540584 / WM WMR R21924176233
[/vba] Автор - nervДата добавления - 29.07.2012 в 11:06
nerv, я не программист, писал на других примерах
nerv, я не программист, писал на других примерах
[/vba] Автор - ABCДата добавления - 29.07.2012 в 12:07
почему сразу не программист? Получается, нормально, и как код переписать сообразил ) Хотя речь была не об этом, а о неправильно расставленных отступах. К примеру, если вам утром необходимо ехать на машине, вы же не прячете ключи вечером, так, чтобы не смогли их найти?) Здесь тоже самое )
почему сразу не программист? Получается, нормально, и как код переписать сообразил ) Хотя речь была не об этом, а о неправильно расставленных отступах. К примеру, если вам утром необходимо ехать на машине, вы же не прячете ключи вечером, так, чтобы не смогли их найти?) Здесь тоже самое ) nerv
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук
YM 41001156540584 / WM WMR R21924176233
почему сразу не программист? Получается, нормально, и как код переписать сообразил ) Хотя речь была не об этом, а о неправильно расставленных отступах. К примеру, если вам утром необходимо ехать на машине, вы же не прячете ключи вечером, так, чтобы не смогли их найти?) Здесь тоже самое ) Автор - nerv
Дата добавления - 30.07.2012 в 18:01 , не переписывать, а писать код подобно других примерах, пошарил форумы (изучал, переделывал, добавлял и т.д.),
добавил проверку контрольного номера----как смог так и написал
, не переписывать, а писать код подобно других примерах, пошарил форумы (изучал, переделывал, добавлял и т.д.),добавил проверку контрольного номера----как смог так и написал ABC
MS Excel 2007 and 2010.
-------------------------------
С Уважением, Даулет
добавил проверку контрольного номера----как смог так и написал Автор - ABC
Дата добавления - 30.07.2012 в 19:44
не может быть ИИН с номерами 00XXXX5. или 00ХХХ6.
т.к. данные даты рождения относятся к 21 веку, соответственно 2100 год еще не наступил.
На сколько помню, 2000 год - это 20 век, 2100 - это 21 век
Это если дословно руководствоватся фразой:
Так, 1 - для мужчин, родившихся в XIX веке, 2 - для женщин, родившихся в XIX веке, 3 - для мужчин, родившихся в ХХ веке, 4 - для женщин, родившихся в ХХ веке, 5 - для мужчин, родившихся в XXI веке, и 6 - для женщин, родившихся в XXI веке Даулет, мне кажется что проверка века не совсем корректна
не может быть ИИН с номерами 00XXXX5. или 00ХХХ6.
т.к. данные даты рождения относятся к 21 веку, соответственно 2100 год еще не наступил.
На сколько помню, 2000 год - это 20 век, 2100 - это 21 век
Это если дословно руководствоватся фразой:
Так, 1 - для мужчин, родившихся в XIX веке, 2 - для женщин, родившихся в XIX веке, 3 - для мужчин, родившихся в ХХ веке, 4 - для женщин, родившихся в ХХ веке, 5 - для мужчин, родившихся в XXI веке, и 6 - для женщин, родившихся в XXI веке Так, 1 - для мужчин, родившихся в XIX веке, 2 - для женщин, родившихся в XIX веке, 3 - для мужчин, родившихся в ХХ веке, 4 - для женщин, родившихся в ХХ веке, 5 - для мужчин, родившихся в XXI веке, и 6 - для женщин, родившихся в XXI веке Автор - MCH
Дата добавления - 31.07.2012 в 08:34 Ну да Михаил, я согласен с Вами, по викингу: так как нету 0 года выходить следующие
19 век - с 1 января 1801 года по 31 декабря 1900 года
20 век - с 1 января 1901 года по 31 декабря 2000 года
21 век - с 1 января 2001 года по 31 декабря 2100 года
но наши задумшики ИИН'а как та подумали и сделали 2000 год это 21 век
вот и ошибка:) :) (ошибка того кто придумал ИИН, считали 7 разряд на век делимым на год без остаток)
хотя ошибка их что расшифровку делают по веку, а надо было по годам, нечего страшного кто то додумается сказать им, и поменяют век на с . до . годов.
но я проверяю ИИН'ы на работе все правильно
------------------------
зря надо была 7 разряд
для 1,2 - с 1800 до 1900 г.г. а не 19 век
для 3,4 - с 1900 до 2000 г.г. а не 20 век
для 5,6 - с 2000 до 2100 г.г. а не 21 век
Ну да Михаил, я согласен с Вами, по викингу: так как нету 0 года выходить следующие
19 век - с 1 января 1801 года по 31 декабря 1900 года
20 век - с 1 января 1901 года по 31 декабря 2000 года
21 век - с 1 января 2001 года по 31 декабря 2100 года
но наши задумшики ИИН'а как та подумали и сделали 2000 год это 21 век
вот и ошибка:) :) (ошибка того кто придумал ИИН, считали 7 разряд на век делимым на год без остаток)
хотя ошибка их что расшифровку делают по веку, а надо было по годам, нечего страшного кто то додумается сказать им, и поменяют век на с . до . годов.
но я проверяю ИИН'ы на работе все правильно
------------------------
зря надо была 7 разряд
для 1,2 - с 1800 до 1900 г.г. а не 19 век
для 3,4 - с 1900 до 2000 г.г. а не 20 век
для 5,6 - с 2000 до 2100 г.г. а не 21 век
ABC
MS Excel 2007 and 2010.
-------------------------------
С Уважением, Даулет
ИНН – идентификационный номер налогоплательщика – присваивается физическим и юридическим лицам. Такие обозначения есть у граждан России, Украины и Беларуси (УНП, ИН). Аналоги существуют у граждан многих других государств.
В данном коде «спрятана» информация о дате рождения, поле человека, коде налоговой (для российских номеров).
Алгоритм расшифровки ИНН в Excel
Идентификационный номер налогоплательщика – это 10 (для юридических) или 12 (для физических лиц) цифр, которые содержат следующую информацию (слева направо):
- код налоговой инспекции, присвоившей ИНН (4 цифры);
- порядковый номер записи в едином государственном реестре налогоплательщиков территориального налогового органа (5 цифр – для организаций, 6 – для физических лиц);
- контрольное число, полученное в ходе применения специального алгоритма (для юридических лиц – 1 цифра, для физических – 2).
Алгоритм проверки ИНН предложен в сервисе «Консультант».
Для десятизначного цифрового кода:
- Найти контрольную сумму с весовыми коэффициентами 2, 4, 10, 3, 5, 9, 4, 6, 8, 0.
- Вычислить частное контрольной суммы и 11. Остаток от деления – контрольное число.
- Если результат предыдущего действия больше 9, то делим его на 10. Остаток от деления – контрольное число.
- Код налогоплательщика считается правильным, если контрольное число совпадает с десятым знаком идентификационного номера.
Для двенадцатизначного кода:
- Найти сумму первых одиннадцати знаков с весовыми коэффициентами 7, 2, 4, 10, 3, 5, 9, 4, 6, 8, 0.
- Контрольное число – остаток от частного суммы и 11.
- Если результат деления больше 9, то находим остаток от частного контрольного числа и 10. Получаем первое контрольное число.
- Найти сумму 12 чисел с весовыми коэффициентами 3, 7, 2, 4, 10, 3, 5, 9, 4, 6, 8, 0.
- Остаток от частного второй контрольной суммы и 11 – второе контрольное число.
- Если оно больше 9, то находим остаток от деления на 10.
- Первое контрольное число правильного ИНН совпадает с 11 знаком номера, второе – с 12.
Проверку правильности ИНН лучше автоматизировать. Неизвестно, реально существующий контрагент выставляет счет к оплате или мошенник. Данная проблема особенно актуальна на крупных предприятиях.
Есть готовые функции для проверки актуальности идентификационного номера: для 10 знаков и для 12. Проверить правильность указания ИНН можно с помощью данной функции.
Реализуем алгоритм проверки с помощью встроенных функций Excel:
- Список номеров введем в столбец А. Перед каждым номером ставим апостроф:
- В ячейку В1 вводим формулу Копируем ее на весь столбец.
Реальные ИНН – «истина», неправильные – «ложь». Данная формула работает только в отношении десятизначных номеров. Если организация работает с физическими и юридическими лицами, то в базу будут заноситься 10-тизначные и 12-тизначные коды.
Просто копируйте и вставляйте – формула рабочая.
Дата рождения по ИНН (Россия) в Excel
Для физических лиц ИНН включает 12 цифр:
где ХХХХ – данные о налоговой инспекции;
ZZZZZZ – порядковый номер налогоплательщика;
YY – контрольный код.
Налогоплательщик получает порядковый номер, когда становится на учет в ФНС. Привязки к дате рождения нет.
Дата рождения по ИНН (Украина) в Excel
Идентификационный номер налогоплательщиков Украины состоит из 10 цифр. Первые 5 – количество дней, которые прошли с 01.01.1900 до рождения.
Например, человек родился 20.09.1976. Первые пять цифр – 28022. Столько дней до его рождения с 31.12.1899 года.
Чтобы найти дату рождения по первым пяти цифрам 28022, нужно 01.01.1900 + 28022 (дней) = 20.09.1976.
Воспользуемся табличным процессором Excel для извлечения даты рождения из идентификационного кода.
Чтобы найти дату рождения, нужно ввести в соседнюю ячейку пять первых цифр плюс 1. И установить формат «Дата». Сделать это можно с помощью ряда функций. Подойдут формулы:
- =ЗНАЧЕН(ЛЕВСИМВ(ТЕКСТ(A2;0);5))+1
- =ЛЕВСИМВ(A2;5)+1
- =ПСТР(A2;1;5)+1
Все три варианта выдают одинаковые результаты.
Данный алгоритм срабатывает не всегда. Четыре цифры за датой рождения в ИНН – порядковый номер плательщика в налоговой службе. Формируется он по мере поступления данных в базу.
Если в какой-то день рождается слишком много людей, то четырех цифр для образования номера не хватает. В данном случае номер начинается с 7 или 8. Цифра указывает на будущее время – алгоритм определения даты неприменим.
Дата рождения по ИНН (Беларусь) в Excel
Граждане Беларуси при постановке на учет в налоговых органах получают УНП (учетный номер плательщика). Это девятизначный код, где:
- первая цифра обозначает регион (у физ. лиц – буква);
- вторая-восьмая – порядковый номер плательщика;
- девятая – контрольная.
С датой рождения УНП не связан.
Зато по идентификационному (личному) номеру, который есть у каждого в паспорте, можно определить дату рождения.
- первая цифра – пол;
- вторая-седьмая – день, месяц и год;
- буква – код региона;
- три цифры за буквой – порядковый номер лица, обратившегося за паспортом;
- две буквы – признак гражданства;
- последняя цифра – контрольная.
Чтобы быстро извлечь дату из личного номера, можно использовать функцию =ПСТР(A2;2;6).
С идентификационными номерами приходится работать бухгалтерам, инженерам по кадрам и другим специалистам. В некоторых организациях такая информация представлена тысячами значений. Обрабатывать вручную и принимать в базу такие коды бесконтрольно – нехорошо. Ряд процессов можно автоматизировать простейшими средствами Microsoft Excel.
Если у вас есть список идентификационных номеров, которые включают дату рождения в каждом идентификаторе, как вы можете узнать дату рождения по этим идентификационным номерам, как показано на скриншоте ниже в Excel? На самом деле, вы можете использовать формулу, чтобы быстро извлечь дату рождения.
Получить возраст по дню рождения с помощью Kutools for Excel
Получить день рождения по идентификационному номеру с помощью формул
Предположим, что идентификационный номер показан как 7908050074059, и первые два числа указывают год рождения, третье и четвертое числа - день рождения, а затем следующие два числа - месяц рождения, вы можете использовать эту формулу: =MID(A2,5,2)&"/"&MID(A2,3,2)&"/"&MID(A2,1,2) , Нажмите Enter и перетащите маркер заполнения вниз, чтобы заполнить ячейки формулой.
Наконечник:
В формуле вы можете изменить ссылку по своему усмотрению. Например, если номер идентификатора показан как 797198909230072, а день рождения - 19890923, вы можете изменить формулу на =MID(A7,8,2)&"/"&MID(A7,10,2)&"/"&MID(A7,4,4) чтобы получить правильный результат.
Получить возраст по дню рождения с помощью Kutools for Excel
Если есть список дней рождения, как узнать возраст по датам рождения? С участием Kutools for Excel , есть утилита, позволяющая быстро рассчитывать возрасты по датам.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите ячейку, в которую вы поместите возраст, и нажмите Kutools > Formula Helper > Date & Time helper .
2. в Date & Time Helper диалог, проверьте Возраст вариант в Тип раздел, затем щелкните, чтобы выбрать дату, по которой вы хотите рассчитать возраст, и установите флажок Cегодня Вариант или Указанная дата вариант, как вам нужно, в Тип результата вывода В раскрывающемся списке выберите один возрастной тип, который вы хотите отобразить.
3.Click Ok, а затем отобразится возраст, перетащите маркер заполнения вниз, чтобы заполнить ячейки формулой.
Читайте также: