Home

Conditional formatting with colour change dependent on date range

andyd
Occasional Contributor
Hi,

 

I hope someone can help, I would like to create a spreadsheet that uses colours to indicate if something is in date, is due or overdue.

 

For instance, if I see a client on one day and don't need to see them for 4, 6 or 12 weeks how can I display this using red amber green?

 

I am ok with basic formula like =sum(c2+84) but much more then this I struggle and cant find out how to do this, I have been trying to do this for weeks messing about with conditional formatting but don't understand what the $ means in excel.

 

Kind Regards

 

Andy

3 Replies

you can use TODAY() function to get the current date and then subtract the last visit date from it to find number of days passed. you can also use WEEKNUM to find the week number of both dates and subtract them also. However in this case you need to check for negative numbers or year difference with an IF formula.

 

For example if you have dates on B column you can use below formula:

=IF(WEEKNUM(TODAY())-WEEKNUM($B2)>0;WEEKNUM(TODAY())-WEEKNUM($B2);52+WEEKNUM(TODAY())-WEEKNUM($B2))

 

This will give you the total of weeks past between today and the date of visit.

Thank you dry much I will try this and get back.

Andy
Thanks Erol,
I have just tried this and it is not what I am looking for, really appreciate your response though.

Thanks again