Forum Discussion

excel_luddite's avatar
excel_luddite
Copper Contributor
Jul 04, 2023

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:

 

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:

 

=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!

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor

    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,

    Martin

Resources