No I do not have sample template per se. All I have is spreadsheet with the vital information on it. I have attached. I would like the spreadsheet to be in this type of format
One more thing, how do I enter the hours used so that it will automatically add them rather them always manually adjusting the total ours used.
is there anyway you can call me so I can look at while discussing with you. If so my number is XXXXX ext 680
Columns D would be based on a formula using hire date and company policy Year 1 - 5 days, Year 2-5 10 days and Year 6 onward 15 days
Vacation Used, Column E, would have to use like you said a separate page where I can enter dates in and calculate total
Column F is set everyone gets 5 days period
Column G would need again a separate sheet to enter in dates to calculate total
Column H is set everyone gets 10 days of vacation
Column I again would need a separate sheet to enter in dates to calculate total used.
As for the employees who are HCE's (highly compensated employees) who already get more the company policy allots for, I guess that would be a seperate spreadheet . some get 3 weeks now but then 4 weeks after 3 or 4 years of employment.
You just helped me with a vacation tracking excel spreadsheet but it is not quite right. After an employee finishes one year of service they get 10 days. For example me, I was hired June 8, 2012. I have worked for the company 1 yrs 4 months so I am entitled to 10 days of vacation now as of June 8, 2013. But you have me listed with still 5 days.
You also said you could add..
"We can add extra columns and translate the days to hours."
I would like to do this and I assume I can just use this from year to year and just simply change employees renewal date.
Lastly, with the new employees, I assume I can just cut and paste the formulas correct or is there an easier way to add an employee to this spreadsheet.
LeeAnn,Thank you for writing back. Please give me a moment to correct the error on the vacation tracking part. This time, I need to consider the months not just the year. Thank you for that clarification.Regarding the adding of columns to facilitate the dates for used vacation, PTO and holidays, YES, you can do that. The formulas will automatically adjust. Just ADD rows or COLUMNS. DO NOT cut and paste data to give or provide new columns or rows.Regarding new employees, you can add them and just DRAG the fill handle of the cells containing formulas to copy the formulas to the newly added employees. The "fill handle" of a cell is the lower right corner of the cell that shows a + sign when you mouse over to it. Here is my screen shot:I will send you the new revision of the file with the vacation tracking corrected in a while.Thank you for your patience.Best regards,Jess
I tried using your newer version but there is one problem...the formula in adding vacation and PTO does not allow for half days only whole days taken.
How can that be fixed.
Please not that I did ask for my free trial to be cancelled. I would really phone support along with this service. If you had phone support I would totally sign up.
Please let me know if you are able to come up with a solution.
Ok, and why doesn't the year and months service change when I change the renewals date so that I have running total of the years served.
This is the formula in the Months served column
I understand how I need to enter in 1 or .5 in the vacation, PTO and holiday and how I need to add notes etc.
I am sorry. This is where I wish you all had phone support.
In order for me to have this as a "running" spreadsheet, I need have the months and years shown then caclulated properly. One I need the spreadsheet to calculate the vacation for sure but two, I need to also know I know how much that person how many years they have served for other HR related benefits that are not vacation, PTO and holiday.
So every time I change the renewal date say for example Employee 1, he renews 11/16/2013. His vacation, PTO and Holiday should automatically reset to zero USED for all three categories because he has not used any yet or do I simply just change the renewal date, the spreadsheet calculate the service time in years and months, the spreadsheet calculate the number of days for vacation earned and I just manually clear out the entries in the Vacation, PTO and Holiday Used columns.
Ok, with manually clearing the data on the Employee record sheet but would still like the years served in years and months to be cumulative when I enter in the new renewal date.
I want the year and month to adjust based on that date so instead of it being 5 years 11 months based on the 11/16/2012 date it would now be 6 years and 11 months with the 11/16/13 date.
Each time I enter an updated renewal date the years served should also be adjusted to reflect the year pasted that was served. So in essence this employee would have just competed his 6th year now going into 7th year.
Yes it does. Thank. I will let you know if I have more clarification need as I now begin to enter in data.
Jess, I have finally gotten to use and start to populate the spreadsheet and I have two entries that are not calculating the allowed vacation properly. I have checked the cell assignments and formula and can not figure it out.
Can you please look at it asap.
I can only attached an image to this email. how do i get it to you.
The two entries in question are highlighted in Red.
They should both calculate to have 10 days of vacation and it doesn't - it calculates 15.
Also please make sure I have not affected any of the formulas. I double checked but want to have you review. I added 2 additional lines /rows for each employee rather then the 5 you originally set for vacation and PTO entries.
Lastly what is the last work book for with the formula - A2-A1. I assume its another criteria page to calculate the vacation days allowed.