Dec 23 2018 03:37 PM
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
Dec 23 2018 10:14 PM
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.
Dec 24 2018 07:35 AM
Dec 25 2018 07:30 AM