Date related IF query

Copper Contributor

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:


EmployeeJoined CompanyJoining date + 3 yearsDate eligible for promotion
Employee A 20 Jan 2120 Jan 241 Jun 24
Employee B20 Mar 2120 Mar 241 Jun 24
Employee C20 Jun 2120 Jun 241 Jun 25
Employee D20 Sep 2120 Sep 241 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:




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

Hi @excel_luddite 


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,


Thank you so much!! This has worked :)