Formula for Calculating (Accrual) Vacation Time

**rishisood** · 05-22-2012, 07:35 PM

Thanks in advance for any help offered. I'm looking for help creating a formula to accrue PTO as well as track available PTO as it's used. And, not go negative if possible... if an employee reaches 0 PTO, they just take time off without pay, so a negative balance would cause issues with the calculations. Here are the specifics:

Pay periods are from the 1st of the month through the 15th. and the 16th through the end of the month. Each employee receives 3.33 hours of PTO for each pay period. Employees are eligible to START accruing PTO after 6 months of employment.

I found a similar forum post at: http://www.excelforum.com/excel-gene...tion-time.html

That seemed close, however I'm just not efficient enough in Excel to understand exactly what the formulas are doing... specifically the formula for available vacation. I think I have the Rate formula figured out by using: =IF((TODAY()-C2)>160,3.33,IF((TODAY()-C2)<160,0)) However would love someone to check the logic on that one as well and give me their feedback.

Thank you very much for your help folks.

**haru** · 05-22-2012, 07:39 PM

Hi rishisood

Welcome to the forum, I would love to help, but could you upload a dummy file to give us a better idea of what you are looking for. It's good to have the layout so you can just copy/paste the formula in.

**rishisood** · 05-22-2012, 07:49 PM

Thank you! I believe I have now uploaded the file I am attempting to use.

**rishisood** · 05-24-2012, 10:56 AM

Bump.
Thank you for your help.

**haru** · 05-24-2012, 06:40 PM

Sorry for the delay, it was the end of the day on 22nd and I was out of town yesterday. Please look at the attachment, I hope it's something you can work with. I added a row for start accural date. And added, total number of days in that year divide by 2 for 6 months. It's a bit crude but later we round the number of pay periods. And I got 15.208333 from dividing 365 days by 24 pay periods in a year.

Amount of vacation accured will be zero if they use more than they have, will not appear as negative number. Though I'm not sure what to do, you may need to manage that vacation taken and only put in the amount they used up and make another list of leave without pay. So it may be better to have a negative number so you can see you need to make an adjustment, because it would be unfair for them to be deducted vacation (future ones) and leave without pay. Either that, or make an conditional format that will light up when you have a problem and then make the adjustment.

Let me know if you need any further help.

**Showtyme** · 01-31-2013, 04:16 PM

Haru,

Can you help me with that formula on my spreadsheet? They get 7 Days of vacation after working for a year, then 14 days a year after working 3 years. I attached a sample of what my spreadsheet would look like.

**Showtyme** · 01-31-2013, 04:22 PM

If hours are easier, I could convert days to 8 hours that start accruing after 365 days.

**arlu1201** · 02-01-2013, 01:14 AM

Showtyme,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.