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:

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Jul 03, 2018

Not very many people are familiar with the DateDif() function seeing as it doesn't autocomplete when typing in the formula, making it a little "hidden".  Here's a good article describing what it's parameters are, what unit options are available as well as a few limitations to be aware of:

 

https://support.office.com/en-us/article/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 03, 2018

    Matt, thank you, but there is no DATEDIF() used for the conditional formatting. Only EDATE().

    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      Jul 03, 2018

      I must have missed that.  Thanks for pointing that out.  Just saw DateDif() and it stood out.

Resources