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

Splatme

unread,

Oct 3, 2006, 3:16:01 PM10/3/06

to

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.

Aqib Rizvi

unread,

Oct 3, 2006, 3:32:42 PM10/3/06

to

Splatme wrote:
> 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.

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

Bob Phillips

unread,

Oct 3, 2006, 3:34:46 PM10/3/06

to

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)

"Splatme" <Spl...@discussions.microsoft.com> wrote in message
news:45285778-4B34-44F8...@microsoft.com...

BFC

unread,

Oct 3, 2006, 3:47:03 PM10/3/06

to

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.

Jen

unread,

Aug 5, 2009, 12:12:02 PM8/5/09

to

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

DOUG

unread,

Sep 3, 2009, 1:44:02 PM9/3/09

to

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

Patrick Blanc

unread,

Feb 2, 2021, 2:12:42 PM2/2/21

to

Does anyone know how to highlight dates that are older than 30 days?
For example, today is 2/2/2021.
If I have the date 1/1/2021 in a cell, I want to use conditional formatting to highlight this date because at least 30 days have passed since 1/1/2021. Can anyone help with this?

Claus Busch

unread,

Feb 2, 2021, 2:19:18 PM2/2/21

to

Hi,

if your date is in A1 then try in conditional formatting:
=A1<=Today()-30

Regards
Claus B.
--
Windows10
Microsoft 365 for business

shreyasi prasad

unread,

Jan 10, 2023, 2:03:36 AM1/10/23

to

Hi all,

I have a situation where, A=follow UP, Invite Sent, B= Last contacted Date, I need to highlight the cell in B which is more than/ above
1. 30 days,
2. 60 days,
3. 90 days,
4. 15 days,
5. 3 days.

What formula could be used in each case. to assign different colors in cell B