Forum Discussion

Sian D's avatar
Sian D
Copper Contributor
Jul 01, 2018

Excel Formula help needed please

Hello, I am creating a spreadsheet showing date of joining and when annual reviews are due.  I've searched the web but still can't quite work out how to use the correct formulas; I have two questio...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 01, 2018

As a comment:

 

Next annual anniversary date could be calculated as

=EDATE(B2,(DATEDIF(B2,TODAY(),"y")+1)*12)

see https://exceljet.net/formula/next-anniversary-date

 

When to check if the date anniversary is more that 4 months from today and date in not blank we may use

=(EDATE($B2,(DATEDIF($B2,TODAY(),"y")+1)*12)>=EDATE(TODAY(),4))*($B2>1)

same is for more than for 2 months, and for less that two months

=(EDATE($B1,(DATEDIF($B1,TODAY(),"y")+1)*12)<=EDATE(TODAY(),2))*($B1>1)

Thus we don't need any additional columns, not to forget proper rules order:

Deleted's avatar
Deleted
Jul 01, 2018

Thanks,

Suspected there would be a more elegant solution - don't use Excel enough to pick a lot of these up - thanks for the update,

 

Andrew

  • Sian D's avatar
    Sian D
    Copper Contributor
    Jul 02, 2018

    Thanks both, I'm still struggling for the second part, I don't know why but its just not working for me - i'm now not getting any colours at all - I'm clearly not cut out for Excel and trying to make things easier for myself!!

      • Sian D's avatar
        Sian D
        Copper Contributor
        Jul 03, 2018

        Thanks but it's still not working, the rules are only showing on one of the dates for some reason which is in January next year- don't worry I'll just have to not bother using it. Just wanted an easy visual way of seeing who was due their annual review quickly :( 

Resources