Date related IF query

Copper Contributor

Date related IF query

This is a bit fiddly but I'm hoping someone can help.

I want to work out the date someone is eligible for promotion, relating to the date they joined the company. The eligibility date is joining date + 3 years, BUT it has to be at least 3 years on 30 Jun (of that year).

So, let's take the below examples:

 Employee Joined Company Joining date + 3 years Date eligible for promotion Employee A 20 Jan 21 20 Jan 24 1 Jun 24 Employee B 20 Mar 21 20 Mar 24 1 Jun 24 Employee C 20 Jun 21 20 Jun 24 1 Jun 25 Employee D 20 Sep 21 20 Sep 24 1 Jun 25

So, A&B are eligible on year before because their 3 year point is BEFORE 1 Jun. Whereas C&D are a year later because they are 1 Jun.

I am trying to make a formulate so that using hundreds of joining dates over many years, I can calculate eligibility dates (not manually). I understand that I can use an IF clause for a date being beyond 1 Jun, but I don't know how to embed 1 Jun as a constant into a column of many years.

I understand I can use this formula to add 3 years:

=DATE(YEAR(O44)+3,MONTH(\$A\$1),DAY(\$A\$1))

A1 in this case says 1 Jun 2000. However, this gets it wrong when it actually needs to be 4 years, because the date/month are later than 1 Jun.

Can I add an IF clause in there, saying that IF the date in (O44) is later in the year than 1 Jun, to add 4 years?

Really hoping someone can cut through this and help me so that I don't have to calculate it all in my head! I have been trying to do it but my mind is frazzled.

Thank you in advance!

2 Replies

Re: Date related IF query

I hope I understood everything correctly, so here is my proposal:

in C2: =DATE(YEAR(B2)+3,MONTH(B2)DAY(B2))

in D2: =IF(MONTH(C2)>=6,DATE(YEAR(C2)+1,6,1);DATE(YEAR(C2),6,1))

And then just copy the formulas down.

Kind regards,

Martin

Re: Date related IF query

Thank you so much!! This has worked :)