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!
