Перейти к содержимому

Как в экселе посчитать стаж работы на текущую дату

  • автор:

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

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

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

update Опубликовано: 08 апреля 2013

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

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

update Опубликовано: 08 апреля 2013

Вычисление возраста или стажа функцией РАЗНДАТ (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 работает с датами
  • Как сделать так, чтобы текущая дата автоматически вводилась в ячейку.
  • Как выяснить, пересекаются ли два интервала дат и на сколько дней

25.03.2013 20:47:43

Здравствуйте. Подскажите, как нужно считать стаж работы в этом случае:
с 01.09.2004г.
по 01.03.2006г.
с 14.03.2006г.
по 01.08.2006г.
с 17.08.2006г.
по 05.09.2011г.
с 05.12.2011
по 02.07.2012г.
Т.е. как учитывать разрывы? и второе, как посчитать, например не все, а пару-тройку «промежутков»?

16.04.2013 00:41:08

Дата ухода — Дата прихода по каждому рабочему месту отдельно. Потом все сложить. Посмотрите видео тут — будет понятнее.

26.08.2013 04:56:32

Николай, здравствуйте. Подскажите пожалйста следующий момент. Стаж работы по каждому месту работы в днях я подсчитал и просуммировал. допустим получилось 4231. Как сделать так чтобы представление количества полученных дней было в виде «3 г. 4 мес. 12 дн.» — как в вашем примере. Т.е. как таковой разницы дат нет — есть полученное количество дней.
Спасибо.

28.08.2013 08:50:39
Тогда встречный вопрос — сколько дней в месяц будем брать? 30?
01.09.2013 09:42:10

Если подсчитанное количество дней стажа в А1, то:
A2=ЦЕЛОЕ(A1/365) — количество лет
A3=ЦЕЛОЕ(ОСТАТ(A1;365)/30,5) — количество месяцев
A4=A1-A2*365-A3*30,5 — количество дней

02.09.2013 08:06:19
08.09.2013 13:15:24

Здравствуйте!
Подскажите пожалуйста, как быть если необходимость подсчитать количество полных календарных месяцев. Например, основное средство принято в эксплуатацию 14 января 2013 г., отчёт по данному ОС готовится по состоянию на 27 августа 2013 г. Количество амортизируемых месяцев — 6 (февраль, март, апрель, май, июнь, июль). Если применить РАЗНДАТ используя способ измерения «ym» , то результат будет — 7, что в данном случае не прав-но, так как требуется подсчитать не полные месяцы , а полные календарные месяцы. Как быть?
Спасибо за помощь.

13.09.2013 23:22:43

Леонид, если в А1 лежит дата приема в эксплуатацию, а в А2 — дата, на которую готовится отчет, то количество полных месяцев можно посчитать просто как =МЕСЯЦ(A2)-МЕСЯЦ(A1)

15.09.2013 09:45:33

Николай, спасибо за подсказку. В моём случае, дополнительно к приведённой формуле, пришлось применить поправочную константу » -1 «. Формула приняла вид =(МЕСЯЦ(A2)-МЕСЯЦ(A1))-1 .

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

Ещё раз спасибо.

16.09.2013 00:08:59
Если даты из разных лет, то можно =ЦЕЛОЕ((A2-A1)/30,5)-1
18.09.2013 11:11:10
Спасибо за подсказку.
25.09.2013 21:55:59

Николая доброго времени суток. Вопрос в следующем для учета дней отпуска необходимо что бы после 1 год 2 мес и 15 (и более) дн таблица давала значение 1год и 3 месяца, в случае если менее 15 календарных дней то 1 год и 2 месяца. Заранее спасибо за ответ.

28.10.2013 16:39:36

Николай! Аллилуйа! у меня заработало
=РАЗНДАТ(СЕГОДНЯ();C3;»y»)&» г. «&РАЗНДАТ(СЕГОДНЯ();C3;»ym»)&» мес. «&РАЗНДАТ(СЕГОДНЯ();C3;»md»)&» дн.», это для списка задач.
А как теперь к этой красоте приделать условное форматирование? чтобы, допустим, меньше двух недель осталось — красный, от двух недель до месяца желтый, больше месяца до шести зеленый, больше шести синий? заранее спасибо!

22.02.2014 09:44:29
Для начала, можно посмотреть вот это видео про условное форматирование .
22.02.2014 17:24:38

Николай, добрый день! Помогите пожалуйста.Есть два столбца: в одном фамилия сотрудника, в другом уже посчитанный медицинский стаж работы, уже в виде, допустим 22г. 2м. 13д. Хотелось бы, чтобы в третьем столбце можно было считать стаж на каждый месяц, учитывая, что по истечении 30 дней они переводятся в месяц, соответственно 12 месяцев добавляют к годам. Функцию РАЗНДАТ не использую, так как для 500 сотрудников трудоёмко вводить отдельно года их работы, тем более у многих идет прерывный стаж, поэт.сразу же на первое января 2014 года стаж посчитан и введен в столбцы. Заранее спасибо.

28.12.2022 12:24:27
Светлана, как вы вышли из ситуации? Мне тоже такой расчет нужен

16.04.2014 14:07:55

Доброго времени суток! Подскажите как написать формулу по следующим критериям: расчет количества дней между двумя датами, при условии что полный календарный месяц, входящий в разницу между датами, должен считаться как 30 дней?
Заранее спасибо!

покупка

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

Рассчитайте стаж работы с даты найма с помощью функции ГОД

Чтобы рассчитать стаж работы с даты найма и конкретной даты, вам может помочь функция ГОД.

Введите или скопируйте следующую формулу в пустую ячейку:

=INT(YEARFRAC(B2,C2))

Внимание: В приведенной выше формуле B2 это ячейка даты найма и C2 — ячейка даты окончания.

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

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

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

Посчитайте стаж работы в годах:

Введите или скопируйте следующую формулу в пустую ячейку:

=DATEDIF(B2, C2, «y»)& » Years»

Рассчитайте стаж работы в годах и месяцах:

Пожалуйста, примените следующую формулу:

=DATEDIF(B2,C2,»y»)&» Years, «&DATEDIF(B2,C2,»ym»)&» Months»

Рассчитайте стаж работы в годах, месяцах и днях:

Примените следующую формулу:

=DATEDIF(B2,C2,»y») & » Years, » & DATEDIF(B2,C2,»ym») & » Months, » & DATEDIF(B2,C2,»md») & » Days»

Внимание: В приведенной выше формуле B2 это ячейка даты найма и C2 — ячейка даты окончания.

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

Иногда вы можете рассчитать продолжительность службы с даты найма до сегодняшнего дня, поэтому каждый день, когда вы открываете рабочий лист, продолжительность службы будет обновляться автоматически. В этом случае вам следует объединить функцию СЕГОДНЯ с функцией РАЗНДАТ.

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

=DATEDIF(B4, TODAY(), «y»)& » Years»
= РАЗНДАТ (B4; СЕГОДНЯ (); «y») & «Годы» & РАЗНДАТ (B4; СЕГОДНЯ (), «ym») & «Месяцы»
= РАЗНДАТ (B6, СЕГОДНЯ (), «y») & «Годы» & РАЗНДАТ (B4, СЕГОДНЯ (), «ym») & «Месяцы» & РАЗНДАТ (B4, СЕГОДНЯ (), «мд») & «Дни»

Рассчитайте стаж работы с даты найма с помощью удивительной функции

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

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

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Щелкните ячейку, в которой вы хотите найти результат, а затем щелкните Кутулс > Формула Помощник > Помощник по дате и времени, см. снимок экрана:

2. В Помощник по дате и времени диалоговом окне выполните следующие операции:

  • Проверьте разница из файла Тип раздел;
  • В Ввод аргументов текстовые поля, выберите ячейки, которые содержат дату найма и дату окончания отдельно;
  • Затем выберите нужный тип результата вывода. Например, вы можете получить стаж работы в годах, месяцах, годах + месяцах + днях, годах + месяцах и т. Д.

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

Больше относительных статей:
  • Рассчитать дату выхода на пенсию с даты рождения
  • Предположим, сотрудник выйдет на пенсию в возрасте 60 лет, как вы можете рассчитать дату выхода на пенсию с даты рождения в Excel?
  • Подсчитайте количество дней / рабочих дней / выходных между двумя датами
  • Вам когда-нибудь приходилось подсчитывать количество дней между двумя датами в Excel? Может быть, иногда вам просто нужно рассчитать рабочие дни только между двумя датами, а иногда вам нужно считать выходные дни только между двумя датами. Как можно подсчитать количество дней между двумя свиданиями при определенных условиях?
  • Рассчитать будущую дату на основе заданной даты в Excel
  • Если вам нужно добавить несколько дней к заданной дате для расчета будущей даты, как вы можете справиться с этим в Excel?
  • Расчет дней, часов и минут между двумя датами
  • Предположим, у вас есть два столбца ячеек даты и времени, и теперь вы хотите вычислить разницу в днях, часах и минутах между этими двумя ячейками даты и времени, как показано на следующем снимке экрана. В этой статье я расскажу о некоторых методах решения этой задачи за вас.

Лучшие инструменты для офисной работы

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

Вкладка Office: интерфейс с вкладками в Office и упрощение работы
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Newest First
Sort comments by
Comments ( 20 )
Rated 5 out of 5 · 1 ratings
about 1 year ago
This comment was minimized by the moderator on the site

First, thank you very much for helping us!

I am trying to use the formula for years and months of service (=DATEDIF(B2,C2,»y»)&» Years, «&DATEDIF(B2,C2,»ym»)&» Months») which works great, but I would like to add that line and another line that just has numbers in it. Is that possible? For instance: The formula =DATEDIF(B2,C2,»y»)&» Years, «&DATEDIF(B2,C2,»ym»)&» Months» is located in D2 and I would like to add number of years and months of service from another agency in E2 with the total sum in F2.

All assistance is great appreciated.

Rated 5 out of 5
about 1 year ago
This comment was minimized by the moderator on the site

If I have a mixed list of employees still with the company (with start date only) and employees no longer with the company (with start and end date), is there a formula for calculating length of service using current date unless there is an end date, in which case the end date would be used in place of the current date to calculate total length of service from start to end?

about 1 year ago
This comment was minimized by the moderator on the site
what version of Excel can i get this? I am unable to get the function on Excel 2016
about 1 year ago
This comment was minimized by the moderator on the site

Hello,
The Excel version in the example is Excel 2010. The DATEDIF function is not available in Excel 2013 and later versions.
DATEDIF is not a standard function and hence not part of functions library and so no documentation. Microsoft doesn’t promote to use this function as it gives incorrect results in few circumstances.

This function does not appear in the ‘Insert Function’ dialog box and is not recognised in the ‘Function Arguments’ dialog box.
The accuracy of this function was improved slightly in Excel 2010 but some of the bugs were never fixed.

Hope it helps. Have a nice day.
Sincerely,
Mandy

about 2 years ago
This comment was minimized by the moderator on the site

After the years of service has been determined. How do you calculate the persons service computation date (SCD). Is there a formula to subtract the years of service from the persons new appointment date to get their SCD leave date?

about 2 years ago
This comment was minimized by the moderator on the site

i m in problem; I am new joining to Govt. dept. as Junior Clerk in Accounts site: I want to calculate to make service end period, and after to add extra months years days in service period given by government relaxation period to complete service 9 to 10 years as (the total service is 8 year, 8 months, 18 days) but govt. give 10 months and 28 days to compile the service period for allowing the come in to as pensioner. Thanks to you for solve that problem.

about 2 years ago
This comment was minimized by the moderator on the site

Dear have a good health to you. I requesting for adjust more months days year in less service length 9 years to make complet 10 years. please tell about it.

about 2 years ago
This comment was minimized by the moderator on the site

How can I just highlight the cells if the yearly anniversary is within a week? For example They started on 01/10/2020 and I want the start date to be highlighted yellow if the date is 24/09 in each year and then turn red if the anniversary has passed, so turn red 7 days after the 1st?

about 2 years ago
This comment was minimized by the moderator on the site
Work Services Calculation 1 month different in formula
about 2 years ago
This comment was minimized by the moderator on the site

If service is 25 Year 07 Months 05 Days than i calculate no. of year and the result is 25, but need to calculate/consider full year if months is equal to 06 months or exceed. After calculation

about 3 years ago
This comment was minimized by the moderator on the site

For calculating the length of services in years, months and days with start date and end date, I noted that the result for James is 20 days. I would like to confirm if the end date means separation effective date (Day 1 after termination/separation) or last employment date.
Both start (hire date) and end date (last employment date «LED») should be inclusive in the calculation. It is expected to return 21 days but not 20 days as it counted no. of days from 12/11/2025 to 12/31/2025.
How to change the formula? Or should it be used termination effective date (LED + 1 day) as cell C2, instead of input «LED» as end date?

about 2 months ago
This comment was minimized by the moderator on the site

Florence, you are correct, and the tutorial above is simply wrong. When calculating years of service/length of service, the calculation must be inclusive of the last day worked. Using basic date subtraction formulas tell the *difference* between 2 dates (which will short the length of service by 1 day). For example, if someone stated the 1st, and ended the following day (2nd), taking the date difference would be 1 day (when they actually worked 2 days). Another example: If you use the method above for someone who started work on January 1st, and their last day was December 31st, it will not show that they’ve worked a complete year — which is of course an error. The way to get the correct answer is to add +1 to the end date. So, everywhere you see C2 above should be C2+1. This is a significant problem where people everywhere are using this incorrect formula for calculating years of service. The formula is typically correct except the edge case of the last day of employment being the exact day before their start day. I’m in a dispute with my former employer over this very issue. Formulas like this should always be checked for edge cases like worked 1 day, 2 days, worked exactly 1 year, the anniversary date, day before/after, etc. When doing this, you will see all sorts of problems like the one you described.

Программа для расчета стажа работы в Excel скачать

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

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

Автоматизированная таблица расчета стажа сотрудников

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

Шапка.

Столбцы ЛЕТ, МЕСЯЦЕВ и ДНЕЙ подсветили цветом, т.к. именно в них будут подсчитываться конечные результаты, которые потребуются бухгалтеру.

Создадим несколько позиций, заполнив часть таблицы. С ФИО все понятно, а вот при заполнении столбцов ПРИНЯТ и УВОЛЕН нужно будет изменить формат ячеек. В новых версиях Excel они меняются автоматически, если вводить дату стандартным образом: ЧЧ.ММ.ГГГГ. На всякий случай лучше заранее поменять формат. Для этого выделяем все ячейки в колонках «Принят» и «Уволен» (кроме шапки таблицы), правой кнопкой вызываем контекстное меню и выбираем ФОРМАТ ЯЧЕЕК (CTRL+1) – ДАТА.

Формат ячеек.

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

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

Формула в Excel для подсчета стажа РАЗНДАТ

Подсчитаем количество дней, которые отработали сотрудники. Т.к. трудовой стаж учитывается независимо от перерывов (выходных, больничных и праздничных дней), нужно просто вычесть из конечной даты начальную Excel позволяет это сделать. Записываем в ячейку D2 формулу =С2-В2.

Отработанные дни.

Получилось 6580 дней. Переведем их в годы при этом не достаточно просто поделить на 365, ведь в году может быть и 366 дней. Так же когда мы будем переводить в количество полных месяцев без учета лет, то следует учитывать тот факт что один месяц может иметь 30 и 31 день. Поэтому для точного анализа следует использовать специальную функцию РАЗНДАТ:

РАЗНДАТ.

  • для ячейки E2(Лет): =РАЗНДАТ(B2;C2;»y»);
  • для ячейки F2(Месяцев): =РАЗНДАТ(B2;C2;»ym»);
  • для ячейки G2(Дней): =РАЗНДАТ(B2;C2;»md»);

Внимание! Данная функция не содержится в мастере функций, поэтому ее нужно вводить вручную.

Таблица параметров функции РАЗНДАТ:

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

ИТОГ.

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

  • Excel Formula Examples
  • Создать таблицу
  • Форматирование
  • Функции Excel
  • Формулы и диапазоны
  • Фильтр и сортировка
  • Диаграммы и графики
  • Сводные таблицы
  • Печать документов
  • Базы данных и XML
  • Возможности Excel
  • Настройки параметры
  • Уроки Excel
  • Макросы VBA
  • Скачать примеры

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *