How to Calculate Months of Service Using Google Sheets

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 the company’s retirement plan after twelve months or health insurance after three full 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 formulas.

Formula Steps for Months of Service

  1. First, enter the employee’s start date and end date of the service in two separate cells.
  2. Next, you can use the DATEDIF and EOMONTH functions to calculate the months between two dates. The formula is as follows: =DATEDIF(EOMONTH(start_date,0)+1,end_date,"M").
    • The EOMONTH function ensures the counting doesn’t start until the first whole month.
  3. Finally, 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").
Formula for months of service
Months of Service

The DATEDIF function does not deal with durations under a date. If you need to calculate duration, including hours, minutes, etc., consider the TIMEDIF add-on.

Link to Live Template

Get a copy of the template with this example here.