Conditional formating to highlight dates 30, 60, and 90 days out?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What would the formula look like to highlight cells, by date (month and
year), as the date approaches? ie... 90, 60 or 30 days out.
 
Splatme said:
What would the formula look like to highlight cells, by date (month and
year), as the date approaches? ie... 90, 60 or 30 days out.
Click to expand...


Assuming:
You are trying to age your receivables
Column A has Due Dates, B has a calculation for overdue days, C has
Aging
Data is from A3 downwards
You have Today's date in Cell C1

Write the following formula in Cell B3
=IF(A3>=$C$1,"Not Due",$C$1-A3)

Write the following formula in Cell C3
=IF(B3="Not
Due","Current",IF(AND(B3>0,B3<31),"00-30",(IF(AND(B3>30,B3<61),"31-60",IF(AND(B3>60,B3<91),"61-90","Over
90")))))

Copy both formulas along your due dates in Column A

Is that what you are looking for?

Regards
Aqib Rizvi
 
Select column A
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(A1<>"",A1<TODAY()+30)
Click the Format button
Select the Pattern Tab
Select red
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =AND(A1<>"",A1<TODAY()+60)
Click the Format button
Select the Pattern Tab
Select an amber
OK
OK

Third condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =AND(A1<>"",A1<TODAY()+90)
Click the Format button
Select the Pattern Tab
Select an green
OK
OK


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Try this: highlight the cells you want to include, then go to Format,
Conditional Formatting, choose Cell Value Is less than or equal to, and then
in the formula bar, enter: =NOW()+30 Be sure to choose a format (i.e.
applicable cells with have yellow shading, etc.). For 60 days or less, the
formula would be =NOW()+60, etc. If you wanted to take it a step further and
break it into 0-30, 31-60, 61-90, etc., you could use "between" instead of
less than or equal to. Hope this helps.
 
How would this be handled across multiple columns? I have a sheet that needs
this on every other column across more than 20 columns.

~Jen
 
Dear Splatme: THEN, how would one add a 3-circle icon set symbol (stop
light) in column A to say "If any of the dates in this row are coming due or
are overdue, show yellow or red"?

DOUG
 
Back
Top