• 409K Members
• 9,820 Online
• 465K Conversations

## Conditional formatting with colour change dependent on date range

Occasional Contributor

# Conditional formatting with colour change dependent on date range

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

# Re: Conditional formatting with colour change dependent on date range

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.

# Re: Conditional formatting with colour change dependent on date range

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

Andy

# Re: Conditional formatting with colour change dependent on date range

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

Thanks again

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
18 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies