Conditional Formatting - DATEDIF

[DarrylBurge]

In the following post I had help with a Nested IF DATEDIF Function
https://www.excelforum.com/excel-for...f-datedif.html

Background:
I am creating a worksheet to allow me to calculate home much Annual Leave to give staff for the coming fiscal year.

My IF DATEDIF function allows me to give either 0, 2 or 6 extra days Annual Leave

What I want to be able to have is Conditional Formatting to know if someone's DATEDIF becomes either 5 or 10 years in the next fiscal year.

i.e. If someone's current DATEDIF is 4 years and 6 months, then in the next 12 months this will become 5 years, and so as such they will need to have a Pro Rata addition of 2 days - I am happy to calculate this manually, but I need their DATEDIF cell to change colour to alert me to the fact that I will need to run a manual calculation

[Croweater]

Assuming you are from Nottingham UK, Google said your fiscal year runs from 6th April to 5th April. Is that what you want in this sheet?

If so, the current financial year is;

=YEAR($K$1)+(MONTH($K$1)>3)-AND((MONTH($K$1)=4),(DAY($K$1)<6))

and the financial year of the staff members start date is;

=YEAR($J2)+(MONTH($J2)>3)-AND((MONTH($J2)=4),(DAY($J2)<6))

So you want to highlight if the difference of those two years = 9 or =4 with two different colours?

So CF1 formula

=(YEAR($K$1)+(MONTH($K$1)>3)-AND((MONTH($K$1)=4),(DAY($K$1)<6))) - (YEAR($J2)+(MONTH($J2)>3)-AND((MONTH($J2)=4),(DAY($J2)<6)))=9

CF2 formula

=(YEAR($K$1)+(MONTH($K$1)>3)-AND((MONTH($K$1)=4),(DAY($K$1)<6))) - (YEAR($J2)+(MONTH($J2)>3)-AND((MONTH($J2)=4),(DAY($J2)<6)))=4

Give them 2 different colours, Bob's your uncle.

PS. If you want the 1st of the month, you just need to remove

-AND((MONTH($K$1)=4),(DAY($K$1)<6)) ... from the formulae...BOTH occurrences in each. (makes it much easier)

[Croweater]

Despite having put up at least two more problems since, I see that you haven't bothered to respond to my questions or the suggested solution, so I assume I guessed all of your requirements correctly?

This is the second time you haven't bothered to respond to a solution from me. Normally people only get away with this once.

I won't be wasting any more of MY time on any more of YOUR problems. A simple 'Thanks for the help' is not too much to ask, so you will NOT be getting any more help from me.