Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default Conditional formating to highlight dates 30, 60, and 90 days out?


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.



  #4   Report Post  
Old October 3rd 06, 08:47 PM posted to microsoft.public.excel.worksheet.functions
BFC BFC is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 8
Default 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   Report Post  
Old August 5th 09, 05:12 PM posted to microsoft.public.excel.worksheet.functions
Jen Jen is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 119
Default 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
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.






  #6   Report Post  
Old 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
Default 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   Report Post  
Old February 2nd 21, 08:12 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2021
Posts: 1
Default 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   Report Post  
Old February 2nd 21, 08:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default 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
Microsoft 365 for business


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dates in Conditional Formatting help!!! Corey Excel Worksheet Functions 1 July 27th 06 10:08 AM
Conditional Formating using Dates Will Excel Discussion (Misc queries) 15 July 13th 06 06:49 PM
CONDITIONAL FORMATING DATES tomklem Excel Discussion (Misc queries) 3 October 7th 05 04:24 PM
Conditional Formating Jeff Excel Discussion (Misc queries) 2 October 5th 05 05:49 PM
Formating Dates for production schedule dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 09:43 PM


All times are GMT +1. The time now is 01:15 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017