You are here: HomeExcelTutorials

# Calculate years of service

## Calculate years of service

Price: FREE

I am looking if there is an Excel formula or macro that can calculate the years of service for each employee based on periods of employment and seniority date. In order for a year to be considered as a year of service, an employee must work a complete calendar year (January 1st through December 31st); otherwise, that period of employment (months) do not count toward someone's years of service. The problem comes in because we have quite a few employees that have been terminated and rehired. Also, once obtaining the years of service, I am using VLOOKUP to obtain the vacation days from information located on the VacationTable tab. However, I can't figure out the vacation hours, monthly accrual and daily accrual based on the vacation days.
Can anyone help me? I've been stuck for a while and can't get this to work.
Please refer to attached file for better understanding of what I am trying to accomplish.

Visit publisher's web-site: Calculate years of service

Category and Tags: ExcelTutorials :

## Related Tutorials

### calculate years of service

Free: Yes
Tags: Excel ›

Does anyone know how to calculate years of service in Excel? (ex. 3 years, 4

### Multiple conditions using greater than or less than.

Free: Yes
Tags: Excel ›

I am trying to create a formula to help me with the below scenario.
I need to calculate how many days of PTO time someone would get based on years of service.
the years of service cell is less than 2, the employee gets 11 days of pto if the years of service =2 or anything up to 3, the employee gets 16 days of pto if the years of service is =3 or anything up to 4, the employee gets 17 days of pto if the years of service is =4 or anything up to 5, the employee gets 18 days of pto if the years of service is from 5-19 years, the employee gets 22 days of pto
I think you can see the pattern from this partial information.

### Employment Years of Service for future date formula

Free: Yes
Tags: Excel ›

I was hoping someone would be able to please give me the formula to calculate someone's years and months of service with a company assuming a future end date.
For example if someone was hired 12/2/02 and was terming the company as of 06/30/12, what formula can I plug in to get the years of service and months with the organization?

### Calculation using a date

Free: Yes
Tags: Excel ›

I have worksheets where I have a formula to calculate the number of years and months of service. I would like to use that date to calculate excess annual leave hours. (Example: calculation shows at 23 years 4 months, I need to use this to calculate the following: 0 to 4 years =360; 5 to 9 years=624; 10-19 years=640; 20+ years=680) Note: The calculation for years and months will be different on each employee.

### Years of Service

Free: Yes
Tags: Excel ›

I'm trying to create a "database" for our team so that we can then create a mail merge for our certificates for anniversaries with the company. I found a formula that will give me the years of service with some text.
=DATEDIF(G10,\$C\$1,"y") & " years of service "
G10 = the start date C1 = today's date
This result gives me "2 years of service" which is great for all but 1 year of service. Can anyone help me with an if statement for the instance when it is the first anniversary and I want it to say "1 year of service".

## Related Applications & Scripts

### Calculate Date Difference

Free: Yes
Tags: PHP › Excel Date and Time

This is a simple script to calculate the difference between two dates and express it in years, months and days. Use as in: "my age is 40 years, 2 month and 17 days old"
Feel free to use this script for whatever you want.

### PHP Reverse auction software

Free: No
Tags: PHP › Excel Auction Systems

PHP Reverse auction software is an Extensive and Powerful Software written in PHP to launch your own Freelancers website. Create your own global talent search marketplace with this software. Service seekers can post their requirements through this Software and the service providers can place their bids for doing the job. Highly configurable features and fully customizable colors, styles, fonts and graphics make this Software very very special.
How it works?
PHP Reverse auction software works as a reverse auction platform and is very easy to use. Process is as follows
Service seekers / webmasters post their requirements / projects
Service providers / programmers/ designers place their bids
Both sort out any issues through project discussion board
Then service seeker chooses the service provider that they feel is best suited for their job.
Service providers complete the job and service seekers pay them
Then they place review for each other.

### Free web ring service

Free: Yes
Tags: Web Services › Excel Web Rings

Bravenet SiteRings provides a free remotely hosted service for creating your own Webring. By linking to sites with similar interests or themes, you can attract visits from the kind of people you're looking for.

### Multiple Choice Questions generator

Free: Yes

Testak.org is a service that helps you create multiple choice questions for use in the classroom or other purposes:
- It is a free service.
- It can be used anonymously.
- The test creation system is very intuitive
- This service will create a dynamic webpage

### IP Address Geolocation Service

Free: Yes

InfoSniper is a free service to geolocate IP addresses and domain names and providing additional information like provider, country, city and even whois information. Just fill in the form and use the different display modes by adding url parameters.