Formula column not calculating time period correctly

Hi Community

I’m building an HR Employee Details board and want to calculate an employees length of service. I’ve created Start Date and End Date columns, then a formula column called Length of Employment.

I’ve used the AI feature to write a formula and so far have managed to get the formula to:

  1. Return the value in years, months and days, rather than just days.
  2. Stop putting millions (slight exaggeration!) of decimal places on the days by rounding these.

But I’ve not been able to get the days to calculate properly. The formula doesn’t seem to account for a calendar year or leap years so is over calculating. Can someone help fix this formula please?

Cheers, Lou

This one is tripping me up, but I got part of the way there. This is working IF the end date is filled in. I’m having issues if the End Date is empty.

IF({End Date}=“”, CONCATENATE(YEAR(TODAY()) - YEAR({Start Date}), " years, ", MONTH(TODAY()) - MONTH({Start Date}), " months, “, DAY(TODAY()) - DAY({Start Date}), " days”), CONCATENATE(YEAR({End Date}) - YEAR({Start Date}), " years, ", MONTH({End Date}) - MONTH({Start Date}), " months, “, DAY({End Date}) - DAY({Start Date}), " days”))

image

Try:

IF({End Date}="", CONCATENATE(YEAR(TODAY()) - YEAR({Start Date}), " years, ", MONTH(TODAY()) - MONTH({Start Date}), " months, ", DAY(TODAY()) - DAY({Start Date}), " days")
, CONCATENATE(YEAR(IF({End Date}="","2000-01-01",{End Date}) )- YEAR({Start Date}), " years, ", MONTH(IF({End Date}="","2000-01-01",{End Date})) - MONTH({Start Date}), " months, ", DAY(IF({End Date}="","2000-01-01",{End Date})) - DAY({Start Date}), " days"))

This removes any error icon if End Date is empty.

You could do it more easily with a 3rd-party app called the Advanced Formula Booster which not only does not use the formula column but allows you to create formulas using multiple lines.

Here is the formula that you would use:

You could even easily handle the case when {Start Date} is empty (see the 1st line) which is almost impossible to handle in a formula column.

Hope it helps.

Worked perfectly! Thanks so much, really appreciate your help :slight_smile:

Haha must be a ‘McCarthy’ thing :slight_smile: Thanks for having a try though.

Haha, I guess so!