How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4046
Experience:  Computer Software Specialist for more than 10 years
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I need help creating a spreadsheet that tracks our vacation,

Customer Question

I need help creating a spreadsheet that tracks our vacation, holiday and paid time off based on hire date

Vacation - Each employee is entitled to 5 days in year one of employment and then from year 2-5 they get 10 days, then year 6 on you get 15 days. Other employees may get more days due to negotiations at time of hire but just want to enter how much that person is entitled to each year OR have a formula where is will increase it it they follow the standard policy.

Holiday - each employee gets 9 days and 1 floating holiday. I don't need to track dates I just need to track hours so I know how much they have used.

Paid Time off - each employee is entitled to 5 days each benefit year (hire date)

I would love to speak to someone about this - XXX-XXX-XXXX ex 680
Submitted: 5 months ago.
Category: Microsoft Office
Expert:  Jess M. replied 5 months ago.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX X am glad to assist you today.

Do you have a sample template where I can just provide the formula? If this is possible, you can send a sample Excel file to me. Just remove sensitive data and share it to me by uploading it in this link below:

http://wikisend.com

After uploading, give me the download link or the 6-digit File ID so that I can get your file.


Best regards,
Jess
Customer: replied 5 months ago.

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

Attachment: 2013-11-04_200332_vacation_pto.xlsx

Customer: replied 5 months ago.

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.


 

Expert:  Jess M. replied 5 months ago.
LeeAnn,

I got your file. Please give me a moment to work on this. I will get back to you with the final file or with few inquiries.

Best regards,
Jess
Expert:  Jess M. replied 5 months ago.
LeeAnn,

Which data or part of the table you want to automate using a formula?

Is it Allowed Vacation based on Hire Date?
Is it Vacation Used? You need a place in the workbook or worksheet to enter their availed vacation so that it will be tracked in column D.

The same conditions apply when you want to use a formula for columns E to I.

Please confirm.

Regards,
Jess
Customer: replied 5 months ago.

is there anyway you can call me so I can look at while discussing with you. If so my number is XXXXX ext 680

Expert:  Jess M. replied 5 months ago.
LeeAnn,

I am sorry but I don't provide phone support but I will do my best to provide a template file for your Excel requirements.

I believe you want to use formulas for columns D to I, right? For columns D, F and H, the calculations will be based on the criteria you mentioned with respect to hire date while the other columns will require tracking of used vacation, PTO and holidays. This requires another sheet or form where you enter the date when they availed the vacation so that it will be reflected in the "Used" column.

Please give me a moment to work on this.

Jess
Customer: replied 5 months ago.

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.

Expert:  Jess M. replied 5 months ago.
LeeAnn,

Thank you for that information. I am working on this now. Please give me a moment to complete the Excel file based on the information you just posted.

Jess
Expert:  Jess M. replied 5 months ago.
LeeAnn,

In order to use formulas, we need to separate hours and days entities. For this sample template, I will be using days only. We can add extra columns and translate the days to hours.

I am almost done.

Jess
Expert:  Jess M. replied 5 months ago.
LeeAnn,

Thank you for patiently waiting. It took me a while to complete the template that you can use.

First, you need to use another sheet, which I called Criteria. In this sheet I created a table for the vacation earned based on hiring date and company policy.

Second, I created a new sheet called Employee_Record which is a copy of the Employee sheet. I retained Employees information but then created columns where you enter the dates of the vacation, PTO or Holiday.

For column D, I used this formula:
=IFERROR(IF(Employees_Record!K3="",VLOOKUP(Employees_Record!D3,Criteria!$A$3:$B$22,2,FALSE),Employees_Record!K3),"")

It means that if there is no Special Instructions, then the company policy with respect to hire date will take effect. If there is a special instruction, like 30 days of vacation, just enter it in the Employee_Record sheet under column K and that it will be shown in column D.

To display the vacation days using company policy on hide date, I then use the VLOOKUP formula to.

Here is the completed file:
http://wikisend.com/download/218816/vacation_pto_rev1.xlsx

Regarding Employees sheet, do not enter any data there since that sheet will just report all relevant data. You enter the dates for vacation, PTO or holiday per employee in the Exployee_Record sheet.

Please remember to rate my answer positively (with 3 or more stars/faces) if this helped.

If you have further question, please do not rate me negatively. Instead, please reply to me so that I can help you further.

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4046
Experience: Computer Software Specialist for more than 10 years
Jess M. and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 5 months ago.

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.


 

Customer: replied 5 months ago.

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.

Expert:  Jess M. replied 5 months ago.

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

Expert:  Jess M. replied 5 months ago.
LeeAnn,

Thank you for patiently waiting. Here is the modified file:
http://wikisend.com/download/833438/vacation_pto_rev2.xlsx

In monitoring the vacation count as per hire date and company rule, I used this formula:
=IF(Employees_Record!K3="",IF(B2="","",IF(TODAY()-B2<=365,5,IF(AND(TODAY()-B2>365,TODAY()-B2<=1286),10,15))),Employees_Record!K3)

Here is what it does:
The first or outer IF will test if the K column in Employees_Record sheet has a special instructions on how many days the employee has. If the company provides the employee a spacial number of days for vacation, enter that in K column so that it will be reflected in Employees sheet under Allowed Vacation column. This outer IF will test if column K is blank. If it is, it will calculate the number of allowed vacation based on the rule you mentioned.

The second IF is the application of the company rule on vacation.
IF(B2="","",IF(TODAY()-B2<=365,5,IF(AND(TODAY()-B2>365,TODAY()-B2<=1286),10,15)))

In this formula, I used the the number of days to represent the years. If it is less than or equal to 365, vacation is 5. If it is more than 365 but less than 1286 (5 years), it will be 10. Otherwise, it will be 15.

Now, regarding the entering of the USED vacation, PTO or holidays, just enter the dates in the correct employee name and it will be tracked in the Employees worksheet.

I hope this helped.

If you have further question, please reply to me so that I can help you further.

Best regards,
Jess
Customer: replied 5 months ago.

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.

Expert:  Jess M. replied 5 months ago.
LeeAnn,

Regarding the subscription, I believe you can easily turn ON and OFF your subscription. The steps are described in this link:

http://ww2.justanswer.com/help/how-can-i-cancel-my-subscription

Now regarding the vacation and pto monitoring,the formula used is counting every entry in the cell. If it is blank, it is not counted. So there is no way to differentiate between a whole day and a half day.

If you want to track vacation and PTO numerically, then you need to enter actual figures, no text since Excel cannot calculate a figure with a text. For example, you can enter 1 for 1 day, 2 for 2 days and so on. For 1/2 day, you can enter 0.5.

If you want to track them per hours, you can as well. Just enter the data in figures and be consistent. If you are entering days, then do it in all the cells. If you are entering hours, do the same. Having this consistent data type is important because Excel is just taking the figures, no textual description.

Now, how would you know the DATE that they took the PTO or vacation? Well, you cannot enter it in the cell since you need to enter figures in the cell. I suggest then that you INSERT Comments in each cell and write there in the comments the dates when they took the vacation of PTO. I hope that makes sense.

Here is the completed file, revision 3:
http://wikisend.com/download/353912/vacation_pto_rev3.xlsx

Please remember to rate my answer positively (with 3 or more stars/faces) if this helped.

If you have further question, please do not rate me negatively. Instead, please reply to me so that I can help you further.

Best regards,
Jess
Customer: replied 5 months ago.

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


 


=IF(B3="","",DATEDIF(B3,TODAY(),"y"))


 


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.


 


 

Expert:  Jess M. replied 5 months ago.
LeeAnn,

The renewal date was never mentioned in the problem, thus the formula was only taking into account the HIRE date.

Where must the Year and Month be based on the calculation? Is it in the Hire Date (column B) or on the Renewed Date (column C)?
Customer: replied 5 months ago.

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.

Expert:  Jess M. replied 5 months ago.
The number of years in service determines the vacation the employee gets. Now you want vacation, pto and holidays to reset on renewal date. Is this correct?

The problem that I can forsee is this. When the employee is renewed, if the 3 data are reset, then you will lose track of those information. Besides, in order for the USED columns (vacation used, pto used, etc), all cells in Employees_Record sheet in columns F to J must be cleared since thse are the cells that are calculated for the monitoring.

So I believe the easier approach to this is to manually clear the data in Employees_Record sheet under columns F to J so that the values are reset to zeros. Besides, you need to enter NEW usage data for this employee.

So, to summarize, when an employee is renewed, you need to delete the figures you entered in Employees_Record sheet for his/her vacation, pto and holidays and the monitoring sheet will automatically reset to zero for that employee.

If you need further assistance, please let me know.

Best regards,
Jess
Customer: replied 5 months ago.

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.

Expert:  Jess M. replied 5 months ago.
Can you explain that further? The current formula calculates based on the hire date. Let us take employee 1 as an example. Hire date is 11/16/2007.

What do you want achieved when you enter the renewal date of 11/16/2013?
Customer: replied 5 months ago.

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.

Expert:  Jess M. replied 5 months ago.
LeeAnn,

I am a bit confused. Now, it is displayed as 5 because it is calculated by this formula as per your instruction:

Today's Date - Hire Date

So

11/11/2013 minus 11/16/2007 is equal to 5 years and 11 months.

Now, when you enter renewal date as 11/16/13, it will NOT affect service year. However, if the DATE today is 11/16/13, it will automatically adjust to 6.

Again, the year and month is calculated based on the current date (today's date) so when today's date is equal to the renewal date, the year in service is incremented. It is not dependent on the renewal date. This is what you mentioned before and I believe it is still correct. The only problem is when the employee is not renewed or terminated.

I hope this clarifies.

Best regards,
Jess
Customer: replied 5 months ago.

Yes it does. Thank. I will let you know if I have more clarification need as I now begin to enter in data.


 


thank you.

Expert:  Jess M. replied 5 months ago.
LeeAnn,

You're welcome. If you need assistance in the future, you can request me any time.

Best regards,
Jess
Customer: replied 5 months ago.

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.

Expert:  Jess M. replied 5 months ago.
LeeAnn,

If you want to send the Excel file to me, I suggest that you share it using the site below:

http://wikisend.com

Upload the file and then give me the download link or the 6-digit File ID.

Regards,
Jess
Customer: replied 5 months ago.

  • File ID: 721194


 


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.


 


Thank you.

Expert:  Jess M. replied 5 months ago.
LeeAnn,

Thank you for writing back. I checked the monitoring formulas and they are updated with the additional rows you created per employee.

Here is the corrected file:
http://wikisend.com/download/174880/Benefits Tracking v3_Kershaw_rev1.xlsx

The cause of the problem is the number 1286 in the formula to mean 5 years. It should have been 1826.

Regarding the last worksheet, please disregard it. I only used it for testing the calculations of two dates. Also, the "Creteria" worksheet is also unused so you can delete it as well.

I hope this helped.

Best regards,
Jess

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    270
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    260
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    120
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    116
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    94
    Bachelor's Degree in Information Technology, Microsoft Certified Professional