If you’re working in a human resources department, you may need to find an employee’s months of service. For example, an employee may become eligible to participate in a company’s retirement plan after twelve months or health insurance after three whole months.
However, personnel manuals typically define months of service as the number of full calendar months, not the number of months elapsed between start and end dates. We’ll keep this in mind as we design the formula.
Formula Steps for Months of Service
- First, enter the employee’s start date in one cell (A2 in the example below) and the desired end date in another cell (B2).
- Next, use the EOMONTH function to change the start date to the end of the month. This ensures the counting doesn’t start until the first whole month. Use
0for thenumber_of_months. The image below shows two different days in January, both resolving to January 31.
- Next, combine the EOMONTH function with DATEDIF to complete the calculation of months between dates. The formula is as follows:
=DATEDIF(EOMONTH(start_date,0)+1,end_date,"M").
Now, you can calculate the months of service starting from the first full month using this formula. Using the same sheet, copy the formula from the Months column as far down as you need.
Output as Text
Optionally, you can output the result in a text string by merging text. The formula is as follows: =CONCATENATE(DATEDIF(EOMONTH(start_date,0)+1,end_date,"M"))," months of service").
Tips
💡If you use the last day of a month as an end date, the formula will not count that month. Use the next day instead.
💡The DATEDIF function does not deal with durations in increments less than days. Consider the TIMEDIF add-on to calculate time durations in hours, minutes, etc.
Link to Live Template
Get a copy of the template with this example here.