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
Deleted's avatar
Deleted
Jul 01, 2018

Hi Sian,

 

I actually had a fun hour working this out (BING is your friend)..

 

Formatting is UK based but this should be enough to help you work it out..

 

As for the first part you didn't provide information about exactly what you wanted but use the DATEDIF function - everything you need is here:

 

https://support.office.com/en-us/article/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38

 

The key to the formatting question is calculating the review date. I guess you  are looking for something like this

 

The key formula is the Review date which had to take account of the fact that if a date was in the past we needed to increment the year by one so you were always looking at the next review..

 

I used this formula - I am not an Excel expert so there may be a more subtle and elegant way to achieve this.. but hopefully enough to get you started.. Use the DATE function to construct a date. For cell C4 

 

=IF(MONTH(B4)>MONTH(NOW()),DATE(YEAR(NOW()), MONTH(B4),DAY(B4)),DATE(YEAR(NOW()) +1, MONTH(B4),DAY(B4)))

 

I also used NOW() formula for the current date, you may want to put a 'Review date'  into a cell and refer to that instead so the calculation is fixed based on the 'Review Date'

 

The conditional formatting rules were as follows

 

Hope that helps,

 

 

Andrew

 

 

 

 

 

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.

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

Resources