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.

To highlight cells based on dates that are 30, 60, or 90 days out, you can use conditional formatting in Microsoft Excel. Here are the steps:

1. Select the range of cells that you want to apply the conditional formatting to.
2. Click on the "Conditional Formatting" button in the "Home" tab of the ribbon.
3. Select "New Rule" from the drop-down menu.
4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
5. In the "Format values where this formula is true" field, enter the following formula for 30 days out: =TODAY()+30=A1 (assuming your dates are in column A)
6. Click on the "Format" button and choose the formatting you want to apply to the cells that meet the criteria.
7. Click "OK" to close the "Format Cells" dialog box.
8. Repeat steps 5-7 for the 60 and 90 day formulas, changing the number in the formula to reflect the number of days out you want to highlight.

That's it! Now, any cells that meet the criteria you set will be highlighted with the formatting you chose. Note that you can also adjust the formatting for each rule by clicking on the rule in the "Conditional Formatting Rules Manager" and clicking "Edit".

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(B30,B3<31),"00-30",(IF(AND(B330,B3<61),"31-60",IF(AND(B360,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 FormatConditional 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" wrote in message
...
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.

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.

"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.

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

"Bob Phillips" wrote:


Select column A
Menu FormatConditional 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" wrote in message
...
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.

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

"BFC" wrote:

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.

"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.

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?

Hi,

Am Tue, 2 Feb 2021 11:12:40 -0800 (PST) schrieb Patrick Blanc:

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?

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

Regards
Claus B.
--
Windows10
Microsoft 365 for business