Holiday Entitlement - extra days for service years

Hi all

I'm in the process of updating our old holiday spreadsheet and pretty sure there is an easier way to work out certain fields.

Our holiday year starts 1st Jan every year. We don't use this spreadsheet to work out the standard entitlement, the administrator just mass fills them out (no more than 20 employees) on the first.

What I do need the sheet to calculate though is extra days. For every 3 years, we gain an extra day holiday.

So far I have worked out the "Years Service" from the holiday start year subtracting the employee start date. =DATEDIF(A7,$B$3,"y")

From this, I need to work out the extra day entitlement. If the employee is 3,4 or 5 years then it's 1 extra day. 6,7,8 then 2 extra days etc.

To be honest I could use this to work out the standard entitlement and have a separate column for signed off carry over days etc.

Any help appreciated. (date format is UK - dd/mm/yyyy)

thanks for the reply. There is no standard rule within Excel - the administrator just simply adds the number. We have various contracts so there is no set number as standard (some are 20, 21 or somehow 25!)

I'll give that a go, thank you.

that worked great thanks. Speaking to the administrator, I don't need to worry too much as we are looking to put in some HR software that will do holiday/sickness calculations.