Forum Discussion
VACATION DAYS EXCEL FORMULA
I am trying to make an excel formula to calculate PTO days. Our employees get a prorated amount on their anniversary date and then everything resets on Jan 1. For example if they started July 2, 2022...
Hi Kyle_Doering
You did not mention the Excel version in used => 2021 or 365 is assumed:
In attached file play with Simul. Today's date and check this gives you what you expect (column [Days] in green table) given the [Start date] of David, Ella...John
Good morning, Lorenzo Yes I am using 365. is there a way to prorate days for the time in between their anniversary date and Jan 1 when all vacation resets?
Is there a way to prorate days for the time in between their anniversary date and Jan 1 when all vacation resets?
Maybe. Could you post a picture with a couple of expected results and explain how you calc. those results please?
- we add PTO days for employees on their 1st & 3rd year anniversary date of full-time employment. The prorated amount added on their anniversary date is:
JAN- FEB: 5 DAYS
MAR-MAY: 4 DAYS
JUNE-AUG: 3 DAYS
SEP-NOV: 2 DAYS
DEC: 1 DAY
Then on Jan 1 after 1 year they start with a fresh 5 days and then Jan 1 after 3 years they start with a fresh 10 days. No PTO days roll overI'm obviously very slow today 😞 Thanks for the explanation, though I also asked a picture showing a few expected results to make sure I understand and we don't waste time
I'll let other community contributors see if they can help you...