Время отпуска автоматически рассчитывается с помощью Excel

Question

Я создаю электронную таблицу для Managed Time Off. Вот сумма начисления, основанная на годах работы:

Accrual Periods 40 Hours per Week
0-1.99 years earn 136 hours (5.23 hrs per pay period)
2 – 5 years ear 176 hours (6.77 hrs per pay period)
6 – 10 years ear 208 hours (8 hrs per pay period)
11-15 years earn 248 hours(9.54 hrs per pay period)
16-20 years earn 288 hours (11.08 hrs per pay period)
21 or more years earn 312 hours (12 hrs per pay period)

Наши периоды оплаты каждые 2 недели. Наш последний период оплаты был 9/3/16 - 16/16/16
Сотрудникам разрешено переносить 1 год МТО. Часто они спрашивают, сколько MTO им нужно использовать к своей годовщине, чтобы не превышать лимит выполнения. Я запустил электронную таблицу, но не могу ее правильно заполнить. У кого-нибудь есть предложения?
Это ручной расчет, который я использую.

D. Ernzen 
Hire Date: 10/28/1991
MTO/yr = 312 hrs per year
MTO/pay period = 12.0
Carryover allowed on aniv date = 312
Current MTO (as of 9/17/16)      389.5
MTO used 9/17-present day        (20.0)
MTO accruing (9/17-10/28)       + 34.8   
MTO balance on Anniversary      = 404.3
Carryover                      - 312.0
Needs to be used by 10/28       =92.3

Я бы хотел, чтобы ввод данных был минимальным. Самая большая проблема, с которой я сталкиваюсь, - это периоды оплаты до количества лет. Любые предложения или помощь с благодарностью! Спасибо :)

Answer 1

Предполагая, что ячейка A2 содержит дату проката:

Годы с момента найма можно рассчитать, используя:

=INT((TODAY()-A2)/365.25)

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

=INT(26 - MOD((TODAY()-A2),365.25)/14) + 1

Скорость начисления, максимальный переход и т.д. Можно легко найти с помощью VLookup основываясь на годах, прошедших с момента найма.