Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date formulas | Excel Discussion (Misc queries) | |||
Help to create a date formula | Excel Worksheet Functions | |||
formula for lenght of employment based on hire date | Excel Worksheet Functions | |||
Update a formula based on date worksheet was saved | Excel Worksheet Functions | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) |