Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 107
Default Create a formula based on date

I have a worksheet that contains the following:
*Subcontractors
*Their Liability insurance
*The insurance dates
What I do weekly is, manually go through the entire worksheet highlighting
the insurance that is expiring (within 30 days) or is already expired. I
highlight the 30days to expire, yellow & the already expired, I highlight red.
Would a formula work best to automate this?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 620
Default Create a formula based on date

Highlight your coulmn of dates
Format/ Conditional Formatting
Cell Value is/ less than or equals to/ =TODAY()/ set your appropriate red
format
Add
Cell Value is/ less than or equals to/ =TODAY()+30/ set your appropriate
yellow format.
--
David Biddulph

"Jenn" wrote in message
...
I have a worksheet that contains the following:
*Subcontractors
*Their Liability insurance
*The insurance dates
What I do weekly is, manually go through the entire worksheet highlighting
the insurance that is expiring (within 30 days) or is already expired. I
highlight the 30days to expire, yellow & the already expired, I highlight
red.
Would a formula work best to automate this?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Create a formula based on date

Use conditional formatting with a formula of

=A1=TODAY()-30

See http://www.contextures.com/xlCondFormat01.html

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jenn" wrote in message
...
I have a worksheet that contains the following:
*Subcontractors
*Their Liability insurance
*The insurance dates
What I do weekly is, manually go through the entire worksheet highlighting
the insurance that is expiring (within 30 days) or is already expired. I
highlight the 30days to expire, yellow & the already expired, I highlight

red.
Would a formula work best to automate this?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Create a formula based on date

Use conditional formatting. If a date is in A1, then pick

condition1 formula is =A1-TODAY()<0
condition2 formula is =A1-TODAY()<30
--
Gary's Student


"Jenn" wrote:

I have a worksheet that contains the following:
*Subcontractors
*Their Liability insurance
*The insurance dates
What I do weekly is, manually go through the entire worksheet highlighting
the insurance that is expiring (within 30 days) or is already expired. I
highlight the 30days to expire, yellow & the already expired, I highlight red.
Would a formula work best to automate this?

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
Date formulas DRondeau Excel Discussion (Misc queries) 7 September 6th 06 09:53 PM
Help to create a date formula S S Excel Worksheet Functions 6 July 24th 06 07:16 AM
formula for lenght of employment based on hire date Mharper Excel Worksheet Functions 1 March 27th 06 09:51 PM
Update a formula based on date worksheet was saved Kevin McQuain Excel Worksheet Functions 0 October 6th 05 02:06 PM
How to create a calculated field formula based on Pivot Table resu dha17 Excel Discussion (Misc queries) 1 December 15th 04 06:39 AM


All times are GMT +1. The time now is 12:19 PM.

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

About Us

"It's about Microsoft Excel"