As we get used to the new year we may want to perform some calculations based on the old year. A recent inquiry requested a formula that could calculate the percentage of a year that an employee had been employed. He suggested using an IF function. See the solution below, but it doesn’t involve the IF function.

In the image below we have a Hire date in column B and a Termination date in column C. Based on the year being 2022 we want to work out the percentage worked of the year for each employee.

The yellow cells in column G allow you to set the start and end dates for the year. This means you could use this with a financial year.

The formula in cell D2 is.

=(MIN($G$2,C2)-MAX(B2,$G$1)+1)/($G$2-$G$1+1)

This formula uses the MIN and MAX functions to determine the correct dates to use for the calculation.

The first date needs to be either the end of the year or the termination date. We choose the earlier of those two dates. Note column C has an empty cell if there is no termination date.

The dollar signs $ used in the formula fix the cell references so that when the formula is copied down, the references don’t change.

In Excel dates are numbers. Each date has an underlying number associated with it. We can use the MIN and MAX functions with dates because of these underlying numbers. Let’s split the formula up and examine the individual components.

MIN($G$2,C2)

Because we need to find the earliest date of either the end of the year or the termination date, we use the MIN function. MIN stands for minimum, and it finds the lowest (earliest) of the two dates. MIN ignores blank cells.

MAX(B2,$G$1)

We then need to use either the first day of the year or the hire date, whichever is the latest date. Because we want to find the highest (latest) date we use the MAX function with those two dates. MAX stands for maximum and it also ignores blank cells.

If we deduct the result of the MAX function from the result of the MIN function we end up with the number of days that the employee has been employed in the year. This days calculation is exclusive. We need to add 1 to the result to make sure it includes both the first and last day. This calculation is surrounded by parentheses so that we can then divide the result by the total number of days in the year.

($G$2-$G$1+1)

We could divide the number of days employed by 365 which is correct 75% of the time or we can calculate how many days in the year by a simple subtraction.

To find out how many days in the whole year we subtract the first day from the last day and again add one to make the calculation exclusive. This calculation is also surrounded by parentheses. This formula handles leap years.

To make sure the cell displays correctly you need to make sure the format of the cell is a percentage.