Remember Me?

#1
October 3rd 06, 08:16 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 1
Conditional formating to highlight dates 30, 60, and 90 days out?

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.

#2
October 3rd 06, 08:32 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 9
Conditional formating to highlight dates 30, 60, and 90 days out?

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

#3
October 3rd 06, 08:34 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 10,593
Conditional formating to highlight dates 30, 60, and 90 days out?

Select column A
Change Condition 1 to Formula Is
Click the Format button
Select the Pattern Tab
Select red
OK
OK

Second condition

Change Condition 2 to Formula Is
Click the Format button
Select the Pattern Tab
Select an amber
OK
OK

Third condition

Change Condition 2 to Formula Is
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.

#4
October 3rd 06, 08:47 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 8
Conditional formating to highlight dates 30, 60, and 90 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.

#5
August 5th 09, 05:12 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 119
Conditional formating to highlight dates 30, 60, and 90 days o

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
Change Condition 1 to Formula Is
Click the Format button
Select the Pattern Tab
Select red
OK
OK

Second condition

Change Condition 2 to Formula Is
Click the Format button
Select the Pattern Tab
Select an amber
OK
OK

Third condition

Change Condition 2 to Formula Is
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.

#6
September 3rd 09, 06:44 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 460
Conditional formating to highlight dates 30, 60, and 90 days o

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.

#7
February 2nd 21, 07:12 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2021 Posts: 1
Conditional formating to highlight dates 30, 60, and 90 days o

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?
#8
February 2nd 21, 07:19 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,866
Conditional formating to highlight dates 30, 60, and 90 days o

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Corey Excel Worksheet Functions 1 July 27th 06 10:08 AM Will Excel Discussion (Misc queries) 15 July 13th 06 06:49 PM tomklem Excel Discussion (Misc queries) 3 October 7th 05 04:24 PM Jeff Excel Discussion (Misc queries) 2 October 5th 05 05:49 PM dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 08:43 PM

All times are GMT +1. The time now is 08:47 PM.