Дата выхода на пенсию в эксель

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

1. Выберите пол и дату рождения.
2. Если у вас есть стаж, дающий право на досрочную пенсию, то укажите и его.
3. Нажмите «РАССЧИТАТЬ». Из полученного результата вы узнаете: дату выхода на пенсию, сколько вам будет в этом момент лет и сколько лет вам нужно ещё проработать до пенсии.

О калькуляторе выхода на пенсию

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

ВАЖНО! Наш пенсионный калькулятор создан уже с учетом внесенных поправок в законодательство РФ, известных как Пенсионная реформа, принятая Госдумой РФ и подписанная Президентом 03.10.2018 года.

Сфера применения

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

Различные виды пенсионных калькуляторов весьма распространены в Интернете, что объясняется повышенным интересом граждан к вопросам начисления пенсий. Однако следует помнить, что не существует калькулятора, который хотя бы приблизительно достоверно мог рассчитать размер ваших будущих пенсионных выплат. Это связано с множеством причин, основной из которых является крайняя непрозрачность системы расчета пенсионных накоплений, практикуемой ПФР.

Законодательно к факторам, непосредственно влияющим на размер начисления страховой пенсии, относятся:

1. Заработная плата будущего пенсионера либо задекларированный доход при самозанятости.
2. Длительность стажа.
3. Социально значимые периоды жизни, к которым относятся служба в рядах ВС, отпуска по беременности и родам, отпуска по уходу за детьми.
4. Избранный будущим пенсионером способ пенсионного обеспечения в системе обязательного пенсионного страхования.
5. Продолжение трудовой деятельности после наступления возраста пенсионирования.

Все эти факторы постоянно претерпевают изменения, а коренная ломка пенсионной системы РФ привносит изменения практически ежедневно.

Поэтому единственные достоверные расчеты калькулятора могут быть предоставлены лишь в части:

1. прогнозируемого для выхода на пенсию года;
2. возраста выхода на пенсию;
3. минимального объема стажа, необходимого для выхода на пенсию.

Пенсионный возраст

Пенсионная реформа, одновременно с поднятием возрастной планки, предусмотрела и поэтапное пенсионирование тех, кто на момент введения реформы либо уже достиг нужного возраста, либо приблизился к нему. В соответствии с этапами реформы, предусмотренное реформой возрастное повышение будет происходить по формуле 1/1, то есть раз в год на год. Общий период введения условий реформирования займет 9 лет.

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

Предельно упрощенно поэтапность отражена в следующих схемах для мужчин и для женщин.

Мужчины:

Женщины:

То есть, начиная с 2028 года (разумеется, если все пойдет так, как предполагает Правительство и Госдума), закончится вводный период и россияне станут выходить на заслуженный отдых сразу по достижении ими соответствующего возраста.

Стаж для пенсии

Пенсионное законодательство предусматривает включение в пенсионный стаж не только времени, непосредственно занятого трудовой деятельностью, но и периоды, относимые к так называемым «социально значимым». К последним относятся годы жизни гражданина, занятые делами, важными для социума и государства в целом – служба в ВС, рождение и воспитание детей, уход за больным или престарелым родственником и т.д.

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

Балльная система

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

Рассчитать количество баллов сложно еще и потому, что ежегодно необходимый минимум баллов увеличивается на 2, 4 балла. Если в 2015 году для выхода на пенсию было достаточно около 7 баллов, то к 2025 году потенциальные пенсионеры должны будут обладать как минимум 30-ю баллами.

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

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

Исключения

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

1. Накопительные пенсии. Право воспользоваться накопленными средствами возникнет у предпенсионеров с момента достижения ими пенсионного возраста по старому законодательству, то есть с 55 и 60 лет в зависимости от пола. В данном случае имеется небольшое ограничение. Право на использование накоплений может возникнуть лишь при условии наличия минимального стажа 15 лет и количества баллов не менее 30.
2. Страховые пенсии для льготников, сохранивших право досрочного выхода на пенсию. Помимо занятых на вредных производствах (списки 1, 2), к ним будут относиться также водители большегрузов на рудниках и шахтах, полевые геологи, лесоповальщики, женщины-асфальтоукладчицы и крановщицы, машинисты-железнодорожники (в том числе и метро), осуществлявшие непосредственную транспортировку пассажиров, портовые докеры, водители общественного транспорта, рыбаки, шахтеры, работники гражданской авиации, непосредственно занятые в обеспечении перелетов, оперативные сотрудники МЧС, работники исправительно-трудовых колоний.
3. Государственные пенсии. Изменения не коснутся лиц, ликвидировавших чернобыльскую аварию, лиц, пострадавших в результате чернобыльской аварии, а также их близких родственников – членов семей.
4. Пенсии госслужащих. Для этой категории будущих пенсионеров с 2018 года будет введен собственный поэтапный график, предполагающий увеличение возраста пенсионирования на шесть месяцев каждый год до достижения предельных величин.

Медицинские и педагогические работники

Существенные изменения ждут работников медицинской и преподавательской сферы. Если до введенных изменений в законодательство эти категории работников могли выйти на пенсию по достижении ими особого преподавательского или медицинского стажа, то, начиная с 2019 года, своим правом на досрочную пенсию они смогут воспользоваться только через 5 лет после накопления ими все тех же 25-30 лет стажа по специальности.

В отношении медиков и педагогов также введен поэтапный режим повышения пенсионного возраста в соответствии с таблицей, приведенной ниже:

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

Северяне

Свои «пятилетку» получили и граждане, осуществлявшие трудовую деятельность в условиях Крайнего Севера или приравненных к ним. Теперь их возраст пенсионирования увеличится именно на эту цифру и составит 55 и 60 лет (в зависимости от пола).

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

Мужчины-северяне:

Женщины-северянки:

Многодетные матери

Пенсионные льготы по новому законодательству будут предоставлены женщинам, относящимся к категории многодетных, вне зависимости от статуса детей – родных или усыновленных. Каждый ребенок, начиная с третьего, подарит своей матери 1 льготный год.

Трое детей дадут матери 3 льготных года, то есть возможность выйти на пенсию в 57 лет, если не имеется других льгот.

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

Пенсионирование женщин, родивших либо усыновивших пятерых и более детей, не претерпит каких либо изменений и будет иметь место в возрасте 50 лет.

Стаж по Списку 1 для мужчин и женщин

При наличии такого стажа на пенсию можно выйти досрочно, согласно таблице ниже.

Стаж по Списку 2 для мужчин и женщин

При наличии такого стажа также на пенсию можно выйти досрочно, согласно таблице ниже.

Стаж по Крайнему Северу для мужчин и женщин

При наличии «северного» стажа на пенсию можно выйти досрочно, согласно таблице ниже.

Для педагогов, медиков, артистов и жителей Крайнего Севера

Постепенное повышение пенсионного возраста производится согласно таблице ниже.

Самый простой способ вычисления возраста или трудового стажа реализуется с помощью функции =РАЗНДАТ(). Эта таинственная функция долгое время не была описана в справке Excel. Более того ее нет ни в одном списке формул, поэтом чтобы использовать эту функцию необходимо вводить только вручную в строку формул или непосредственно в саму ячейку. Несмотря на это она доступна еще с 2000-го года версий Excel и прекрасно справляется со своей основной задачей.

Примеры формул с использованием функции РАЗНДАТ в Excel

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

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

Для расчета трудового стажа примените такую формулу:

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

1. Начальная дата.
2. Конечная дата.
3. Формат даты – в какой единице измерения времени посчитать и отобразить разницу между начальной и конечной датой ("y" – год, "m" – месяц, "d" – день и др.).

Список всех форматов отображения разницы между датами приведен в следующей таблице:

Сразу стоит отметить что 3-тий вариант формата особо не понадобиться так как при обычном математическом вычитании от младшей даты старшей мы получаем тот же результат в днях. Например, 2 способа чтобы узнать сколько прошло дней между двумя датами:

Ведь в Excel, как и во всех других программах, входящих в пакет MS Office, используется система 1900 для нумерации дат. И каждая дата — это число дней, прошедших от 01.01.1900г.

Особое внимание требуют 4-й, 5-й, 6-й коды. Данные коды позволяют получить остатки месяцев неполных лет и остатки дней с неполных месяцев. Их принцип действия легче показать на примере чем описать. Обратите внимание как ведет себя функция с этими кодами в ее третьем аргументе.

Для расчета точного срока беременности от даты зачатия до даты рождения ребенка лучше применить такую формулу:

А чтобы рассчитать дату зачатия по родам нам не нужно прибегать к функции РАЗНДАТ:

Пример более сложной формулы для РАЗНДАТ в комбинации с другими функциями для преобразования дробной части числа лет в месяцы и дни:

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

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

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

Как видно на примере функция РАЗНДАТ не решает любые вычислительные задачи с датами. Иногда следует прибегать к другим узконаправленным функциям.

Сегодня очень много говорится о накопительной пенсии. Правительство готовит к запуску Гарантированный пенсионный план (ГПП). Основное нововведение – это «добровольность» накопительной пенсии. Это значит, что каждый из нас будет сам решать, довольствоваться ли в преклонном возрасте исключительно социальной пенсией или дополнительно создавать пенсионные накопления.

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

После решения заниматься своими пенсионными накоплениями немедленно встает вопрос. А сколько нужно накопить, чтобы не почувствовать себя «у разбитого корыта»?

Пенсионный калькулятор в EXCEL

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

• Размер необходимых пенсионных накоплений (к моменту выхода на пенсию)
• Размер ежегодных отчислений, необходимых для формирования накоплений

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

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

Для правильного расчета потребуется:

• Планируемый возраст выхода на пенсию
• Текущий возраст
• Планируемый размер ежегодной пенсии
• Срок дожития
• Доходность инвестиционного портфеля ДО пенсии
• Доходность инвестиционного портфеля ПОСЛЕ пенсии
• Средняя инфляция

Срок дожития

Отдельного комментария требует срок дожития. Необходимо указать, сколько лет вы планируете получать пенсию из своих накоплений. Что будет после того, как эти деньги закончатся? Ничего особенного … вы продолжите жить, довольствуясь только социальной пенсией и, возможно, опираясь на взаимопомощь внутри семьи. В любом случае можно указать срок достаточно длинный. Типичными сроками дожития являются 20 или 30 лет.

Пример расчета пенсионных накоплений

Предположим, будущий пенсионер собирается получать на пенсии 600 тыс. руб. ежегодно (50 тыс. в меся) за счет своих накоплений. Сегодня ему 35 лет. Выйти на пенсию он собирается в 65 лет, как и положено всей мужской части населения.

Для этого ему потребуется инвестировать ежегодно до выхода на пенсию 114 тыс. рублей.

Предположения, на которых основывается расчет:

• Доходность инвестиционного портфеля до выхода на пенсию – 14%
• Доходность портфеля после выхода на пенсию – 11%
• Средняя инфляция – 8% (мы всегда стараемся брать несколько завышенные цифры)
• Стартовые накопления – 100 тыс. руб. (в 35 лет)

В возрасте 45 лет для достижения аналогичных доходов на пенсии придется откладывать ежегодно уже 245 тыс. руб. В 55 лет – 674 тыс. в год.

Калькулятор подтверждает, что время – главный союзник инвестора. Начинайте инвестировать как можно раньше!

Досрочный выход на пенсию

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

Например, в том же случае (возраст - 35 лет) будущий пенсионер решил выйти на пенсию раньше - уже в 55 лет. Что для этого необходимо сделать?

Достаточно инвестировать ежегодно 328 тыс. руб. (примерно 27 тыс. руб. в месяц) и через 20 лет можно заниматься любимым делом, получая 60 тыс. в месяц от инвестиций собственных пенсионных накоплений.

При расчете досрочного выхода на пенсию не забудьте увеличить срок дожития - ведь пенсионных накоплений должно будет хватить на более длинный срок.

Ограничения калькулятора

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

• Неизвестна точная доходность инвестиционного портфеля
• Неизвестен точный размер инфляции

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

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

UPDATE 17.02.2020

Опубликована новая версия калькулятора с расчетом Процента снятия средств на момент выхода на пенсию.

Калькулятор размера пенсионных накоплений в EXCEL
Файл: retirement_calculator_rev2.xlsx
Размер: 58185 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

Рассчитать совпадение дат в днях

= МАКС(МИН(конец1; конец2) -МАКС(начало1; начало2) +1;0)

Чтобы вычислить количество дней, которые перекрываются в двух диапазонах дат, вы можете использовать арифметику с базовыми датами вместе с функциями МИН и MAКС.

В показанном примере формула в D5:

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

Вот что происходит в основе формулы здесь:

Здесь просто вычитают более раннюю дату из более поздней даты. Чтобы определить, какие даты использовать для каждого сравнения диапазона дат, мы используем MИН для получения самой ранней даты окончания, а MAКС — для последней даты окончания.

Мы добавляем 1 к результату, чтобы убедиться, что мы считаем «столбы забора», а не «промежутки между столбами забора» (аналогия с Джоном Уокенбахом из Библии Excel 2010).

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

Рассчитать оставшиеся дни

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

В показанном примере формула в D5:

Даты в Excel — это только серийные номера, которые начинаются 1 января 1900 года. Если вы введете 1/1/1900 в Excel и отформатируете результат в формате «Общий», вы увидите цифру 1.

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

В показанном примере формула решается следующим образом:

Если вам необходимо рассчитать оставшиеся дни, используйте функцию СЕГОДНЯ следующим образом:

Функция СЕГОДНЯ всегда возвращает текущую дату. Обратите внимание, что после того, как Конец даты прошел, вы начнете видеть отрицательные результаты, потому что значение, возвращаемое СЕГОДНЯ, будет больше, чем конечная дата.

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

Рассчитать истекшее рабочее время

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

В Excel один день (24 часа) обозначается цифрой 1. Таким образом, 1 час — это 0.041666667 (то есть 1/24), 8 часов — 0,333, 12 часа — 0,50 и так далее. Короче говоря, вы можете думать о часах как о дробных частях дня.

Когда время начала и окончания одновременно в один и тот же день, тогда время начала по определению меньше времени окончания, и вы можете использовать простое вычитание, чтобы определить прошедшее время. Например, при стартовом времени 9:00 и в конце времени 17:00, вы можете просто использовать эту формулу:

конец — начало = прошедшее время

Для форматирования прошедших часов может быть полезно использовать пользовательский формат, например ч: мм или [ч]: мм

Специальный синтаксис квадратных скобок [ч] указывает Excel на то, что продолжительность более 24 часов. Если вы не используете скобки, Excel просто обнулится, когда продолжительность будет 24 часа (например, часы).

Вычисление пройденного времени более сложно, если время пересекает дневную границу. Например, если время начала — 22:00 один день, а время окончания — 5:00 на следующий день, время окончания на самом деле меньше времени начала, а приведенная выше формула вернет отрицательное значение, которое вызовет Excel для отображения строки символов хеша (т. Е. ########).

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

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

Эта формула не будет работать для раз в тот же день, поэтому мы можем обобщить и комбинировать обе формулы внутри оператора ЕСЛИ следующим образом:

= ЕСЛИ(конец> начало; конец-начало; 1-начало + конец)

Теперь, когда оба времени в один и тот же день, где конец больше времени начала, поэтому используется простая формула. Но когда время на дневной границе используется вторая формула.

Это можно еще более упростить до этой элегантной формулы:

Здесь функция ОСТАТ заботится о негативной проблеме, используя функцию ОСТАТ для «переворачивания» отрицательных значений в требуемое положительное значение.

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

Если вам не нравится сложность вышеперечисленных решений или вам нужно рассчитать затраченное время, которое занимает более одного дня, просто исправить это просто добавить значение даты как в начале, так и в конце. Например, вы можете ввести 1 сентября 2016 года в 9:00, как показано ниже, с единственным промежутком времени между датой и временем:

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

Например, чтобы рассчитать истекшие часы с 1 сентября 2016 года в 9:00 и 3 сентября в 10:00, введите оба значения как даты плюс время, затем вычтите начало с конца и используйте [ч]: мм для форматирования результата.

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

В этом примере используется формула в D8. Формула проста:

Результат равен 2.042, который при форматировании с использованием [ч]: мм составляет 49:00 часов.

Рассчитать срок годности

Чтобы вычислить срок действия в будущем, вы можете использовать различные формулы. В показанном примере формулы, используемые в столбце D:

= КОНМЕСЯЦА (B7;0) +1 // 1-го числа следующего месяца

В Excel даты — это просто серийные номера. В стандартной системе дат для окон, основанной в 1900 году, где 1 января 1900 года является номером 1. Это означает, что 1 января 2050 года серийный номер 54789.

Если вы рассчитываете дату n дней в будущем, вы можете добавлять дни непосредственно, как в первых двух формулах.

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

Для расчета срока годности в конце месяца, используйте функцию КОНМЕСЯЦА, которая возвращает последний день месяца, n месяцев в будущем или в прошлом.

Самый простой способ рассчитать 1-й день месяца — использовать КОНМЕСЯЦА, чтобы получить последний день предыдущего месяца, а затем просто добавить 1 день.

Рассчитать дату выхода на пенсию

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

В показанном примере формула в D5:

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

В этом случае мы хотим, чтобы дата в будущем составляла 60 лет, начиная с даты рождения, поэтому мы можем написать такую формулу для данных в примере:

Дата начинается с даты рождения в столбце С (дат рождения). Нам требуется эквивалент 60 лет в месяцах. Поскольку вы, вероятно, не знаете, сколько месяцев в 60 годах, хороший способ решения данного вопроса — встроить математику для этого вычисления непосредственно в формулу:

Excel решит это (720), а затем отправит в ДАТАМЕС. Вклад расчетов, таким образом, может помочь сделать предположения и цель аргумента понятными.

Примечание: ДАТАМЕС возвращает дату в формате серийного номера Excel, поэтому убедитесь, что вы применяете форматирование даты.

Формула, используемая для получения оставшихся лет:

Вы можете использовать этот же подход для расчета возраста от даты рождения.

Что, если вы просто хотите знать год выхода на пенсию? В этом случае вы можете отформатировать дату, возвращенную ДАТАМЕС, в формате пользовательских чисел «гггг», или, если вы действительно хотите только год, вы можете заключить результат в функцию ГОД следующим образом:

Эта же идея может быть использована для расчета дат для широкого спектра вариантов использования:

• Срок действия гарантии
• Срок действия членства
• Дата окончания рекламного периода
• Истечение срока годности
• Даты осмотра
• Срок действия сертификата

Рассчитать года между датами

Если вы хотите вычислить количество лет между двумя датами, вы можете использовать функцию ДОЛЯГОДА, которая будет возвращать десятичное число, представляющее долю года между двумя датами. Вот несколько примеров результатов, которые ДОЛЯГОДА вычисляет:

дата начала (1/1/2015) дата окончания ( 1/1/2016) результат ДОЛЯГОДА(1)

дата начала (6/1/2000) дата окончания (6/25/1999) результат ДОЛЯГОДА(0.9333)

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

Вы также можете сохранить только целую часть результата без дробного значения, так что вы будете считать только целые годы. В этом случае вы можете просто обернуть ДОЛЯГОДА в функцию ЦЕЛОЕ:

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

• Основная формула Excel расчета для возраста в годах.
• Как определить возраст с функцией ДОЛЯГОДА.
• Как узнать возраст в Excel при помощи РАЗНДАТ.
• Рассчитываем в днях, месяцах и годах.
• Сколько вам будет на определённую дату?
• Сколько вам будет лет в определенный год?
• Узнайте дату, когда человек достигает N лет

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

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

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

Если предположить, что день рождения (ДР) записан в ячейке B2, то вычислить возраст в годах можно следующим образом:

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

=(TODAY()-B2)/365

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

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

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

Функция ЦЕЛОЕ округляет в меньшую сторону до ближайшего целого.

Округляет количество прожитых лет по стандартным правилам в зависимости от значения дробной части (более 0,5 — в большую сторону).

Округляет в меньшую сторону.

Отбрасывает дробную часть, оставляя только целое.

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

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

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

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

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

Как определить возраст с функцией ДОЛЯГОДА.

Более надежный способ расчета, чем те, о которых мы говорили ранее, - это использование функции ДОЛЯГОДА (YEARFRAC в английском варианте), которая возвращает часть года, то есть количество целых дней между двумя датами.

Синтаксис здесь следующий:

YEARFRAC (дата_начала, дата_окончания, [базис])

Первые два аргумента очевидны и вряд ли требуют какого-либо дополнительного объяснения. А вот Базис — это аргумент, который определяет основу для подсчета дней.

Чтобы составить абсолютно точную формулу, передайте в ДОЛЯГОДА следующие значения:

• Дата_начала - дата рождения.
• Дата_окончания - функция СЕГОДНЯ() для использования текущего момента времени.
• Базис - используйте 1, которая предписывает Excel делить фактическое количество дней в месяце на фактическое количество дней в году. Никаких усредненных цифр типа 360 или 365 здесь не используется.

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

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

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

Итак, вот улучшенный вариант:

Метод округления можете выбрать сами.

Особенности применения этих функций мы уже рассмотрели выше.

Как узнать возраст в Excel при помощи РАЗНДАТ.

Еще один способ - использование функции РАЗНДАТ (DATEDIF):

дата_нач и дата_кон - две даты, разницу между которыми необходимо рассчитать,

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

• Y - возвращает количество лет.
• M - количество месяцев.
• D - количество дней.
• YM - возвращает месяцы, игнорируя дни и годы.
• MD - разница в днях, игнорируя месяцы и годы.
• YD - разница в днях, игнорируя годы.

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

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

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

Рассчитываем в днях, месяцах и годах.

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

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

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

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

Ну а если нужно сделать совсем красиво, используйте более сложное выражение:

=ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();"y");РАЗНДАТ(B2;СЕГОДНЯ();"y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();"y")-11;100);9);10);"[

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

Сколько вам будет на определённую дату?

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

Кстати, аналогичным образом можно определять и величину трудового стажа.

Итак, вот как это может выглядеть:

=ЕСЛИ(РАЗНДАТ(B2;C3;"y");РАЗНДАТ(B2;C3;"y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;C3;"y")-11;100);9);10);"[

Функция ДАТА извлекает номер года из ячейки С2, создает полную дату, используя введенные вами номера месяца и дня (в этом примере 1 января), и передает ее в РАЗНДАТ. В результате вы получаете сумму прожитых пациентом лет по состоянию на 1 января определенного года.

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

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

Где B2 - день рождения.

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

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

Читайте также:

  
• Расчет накопительной пенсии при досрочном выходе на пенсию
  
• Пенсионный фонд отказал так как ребенок умер
  
• Пластиковые карточки для пенсионеров проезд
  
• Имущество предназначенное для обеспечения уставной деятельности пенсионного фонда
  
• Ежемесячные платежи рассчитаны с учетом перехода через пенсионный возраст