Forum Discussion
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 questions:
1. I would like a formula that would calculate length of time worked at the company; so from date of joining to today
2. i would like to have the cells colour coded for the annual review; so if the review is due in more than 4 months its green, 2-4 months its yellow, less than 2 months red
I clearly getting something wrong as I either up with all the cells the same colour or an error function, I am using Microsoft office 2016.
Thanks in advance
15 Replies
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:
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
