Forum Discussion
Excel Formula help needed please
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
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:
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
Matt, thank you, but there is no DATEDIF() used for the conditional formatting. Only EDATE().
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
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!!
Thank you so much. I've literally just managed to sort the length of service bit out. and will definitely have a look at the second part now.
I'm in the UK so UK based is perfect for me! I think the problem was that i'd put the dates in in 01.01.13 instead of 01/01/13 which was causing a lot of the problems.
Thanks again for a quick response
No problem - you need to make sure your cells are formatted as Short Date too.. otherwise calculations return an integer - good luck,
Andrew
