Vacation Dashboard, Calculate accrued days YTD

[Suzanne Griffiths]

Good Afternoon. I have enjoyed re-creating the vacation dashboard recently provided by Chandoo. I have just completed the excel school so am still learning more and more daily. In our version of the dashboard it was important to illustrate the number of vacation days available to each employee. Here is where I have run into a mental roadblock.

Each employee has a hire date. Upon hire and for the first 5 years each employee gets 15 days vacation which accrues monthly over the year. At year six they get an additional day of vacation adding an additional day each year up until they reach a maximum of 25 days.

If the employees hire date falls in month 6 then the rate of accrual would change partway through the year. I need to create a formula that would recognize this change. If I could get help with how to set this formula up please.

For example each month is numbered 1-12 below, months 1-5 would receive an accrual of 1.25 days for that month, then months 6-12 would accrue at the rate of 1.3



Attached is my workbook (all data is fictional at this time). On the Tracker page the calculation for number of years worked is provided along with a calculation of number of vacation days allowed that year based on the hire date. On the Calculations page starting at cell CX43 is where I'd like to calculate the YTD hours accrued.

I sincerely appreciate the help.

 

[xld]

Suzanne,

A couple of points first.

You are using SUM unnecessarily a lot. SUM is only needed if you are addig things, not in every arithmetic operation. For instance you use

=SUM(TODAY()-[@[Hire Date]])/365

whereas you would only need

=(TODAY()-[@[Hire Date]])/365

and you certainly don't need two SUMs

=SUM(SUM('Vacation Tracker'!M5/12)*MONTH(TODAY()))

instead use

=('Vacation Tracker'!M5/12)*MONTH(TODAY())

To your problem. To calculate the number of days, you could use

M5: =N(MIN(15+MAX(0,(DATEDIF(K5,TODAY(),"Y")-5))+AND(MONTH(TODAY())>6,MONTH(K5)>6),25))

I am not sure what needs amending on the Calculations sheet.S

 

[Suzanne Griffiths]

@xld Thank you for your help. I appreciate the tips.

The calculation that I am trying to determine is on the Calculations page starting on cell CX44. I am trying to determine the number of days accrued year to date. Due to employees receiving more days based on their hire date I need to be able to calculate where there might be a change partway through the calendar year. This change is dependent on the month of their hire date.

I believe that my current setup is flawed based on when you view the dashboard. For example John was hired in June of 2009. For the 2015 calendar year he would receive 1.33 days for the first 5 months of the year. The last 7 months of the year he would receive 1.42 days per month for a total of 16.59 for the calendar year. I think that my current formula is correct for the first 5 months, but then when it is June and the increase happens it recalculates the first 5 months based on the new total of 17 days allowed.

My goal is that as we utilize the tracker throughout the year the accrual formula takes into consideration the hire dates and the increase in the rate of accrual as it becomes available.

 

[Suzanne Griffiths]

Hoping that others can add some input. All of the formulas I try "reset" the accrual value as of Jan 1. Our vacation days are tracked on the calendar year, but an employees accrual value may increase during that year based on their hire date.

I have not yet found an option that will allow the month's up until the change stay at the original accrual rate and the months after the change accrue at the new rate.

Is this something that won't be possible?

I am re-attaching the file as I have updated some of the formulas to me more simplified. The place where I want to put the accrual formula is on the Calculations page in cell CX44.