Expired Dates

I have a simple Excel spreadsheet with about 50 rows and 8 columns of dates.
Each row is a different activity entry and all columns in each row have a
date cell with a simple + or - formula relating to a base date in the final
column.

Although it is not important for the logic, the final column is the starting
date for works on a construction site and the preceding dates are all
critical events leading up to the starting date. Each row has a different
activity. Each row is stand alone and is not linked by formulas to any
other row.

How do I get Excel to automatically highlight dates that have expired or are
about to expire?

A simple automatic shading of the cells for dates which have past would be
an excellent start.

This is Windows XP and Excel Xp.

Any help would be appreciated.

Axel

Select all the date cells, assume the first is A1
Goto to Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =A1<TODAY()
Click Format and set a pattern colour
OK
Add Condition
Change Condition 1 to Formula Is
Add a formula of =A1<TODAY()+10
Click Format and set a pattern colour
OK
OK

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Axel" <[email protected]> wrote in message
news:[email protected]...
> I have a simple Excel spreadsheet with about 50 rows and 8 columns of
dates.
> Each row is a different activity entry and all columns in each row have a
> date cell with a simple + or - formula relating to a base date in the
final
> column.
>
> Although it is not important for the logic, the final column is the
starting
> date for works on a construction site and the preceding dates are all
> critical events leading up to the starting date. Each row has a different
> activity. Each row is stand alone and is not linked by formulas to any
> other row.
>
> How do I get Excel to automatically highlight dates that have expired or
are
> about to expire?
>
> A simple automatic shading of the cells for dates which have past would be
> an excellent start.
>
> This is Windows XP and Excel Xp.
>
> Any help would be appreciated.
>
> Axel
>
>
>
>

Hi axel,

Select the cell with the due date and under Format > Conditional Format >
Formula Is > =A1>B1 > Format > Pattern > Choose color > OK > OK.

Where A1 represents the due date and B1 has the formula =NOW()

HTH
Regards,
Howard

"Axel" <[email protected]> wrote in message
news:[email protected]...
>I have a simple Excel spreadsheet with about 50 rows and 8 columns of
>dates. Each row is a different activity entry and all columns in each row
>have a date cell with a simple + or - formula relating to a base date in
>the final column.
>
> Although it is not important for the logic, the final column is the
> starting date for works on a construction site and the preceding dates are
> all critical events leading up to the starting date. Each row has a
> different activity. Each row is stand alone and is not linked by formulas
> to any other row.
>
> How do I get Excel to automatically highlight dates that have expired or
> are about to expire?
>
> A simple automatic shading of the cells for dates which have past would be
> an excellent start.
>
> This is Windows XP and Excel Xp.
>
> Any help would be appreciated.
>
> Axel
>
>
>
>

Woops...> =A1>B1

I belive that should be =A1<B1

"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> Hi axel,
>
> Select the cell with the due date and under Format > Conditional Format >
> Formula Is > =A1>B1 > Format > Pattern > Choose color > OK > OK.
>
> Where A1 represents the due date and B1 has the formula =NOW()
>
> HTH
> Regards,
> Howard
>
> "Axel" <[email protected]> wrote in message
> news:[email protected]...
>>I have a simple Excel spreadsheet with about 50 rows and 8 columns of
>>dates. Each row is a different activity entry and all columns in each row
>>have a date cell with a simple + or - formula relating to a base date in
>>the final column.
>>
>> Although it is not important for the logic, the final column is the
>> starting date for works on a construction site and the preceding dates
>> are all critical events leading up to the starting date. Each row has a
>> different activity. Each row is stand alone and is not linked by
>> formulas to any other row.
>>
>> How do I get Excel to automatically highlight dates that have expired or
>> are about to expire?
>>
>> A simple automatic shading of the cells for dates which have past would
>> be an excellent start.
>>
>> This is Windows XP and Excel Xp.
>>
>> Any help would be appreciated.
>>
>> Axel
>>
>>
>>
>>
>
>

Hi,

Thanks for the response, but I am struggling to set this up. My initial
query was probably too simplistic. Extract from actual sheet:

D
E F G H I
J K

WP Nr Work Package Final Tender Assessment Pre-order Meeting
Acceptance Letter Prepare Order Docs Place Official Order Pre Start Meeting
Lead-in (Wks) Site Start
9 Fire Protection 09/01/2006 16/01/2006 23/01/2006 2
06/02/2006 20/02/2006 6 06/03/2006


Existing cell entries =E12-7 =F12-7
=K12-(j12*7) 2 =F12+14 =K12-14 6 06/03/2006



Can you possibly assist further with actual cell entries?

Many thanks,

Axel


"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> Hi axel,
>
> Select the cell with the due date and under Format > Conditional Format >
> Formula Is > =A1>B1 > Format > Pattern > Choose color > OK > OK.
>
> Where A1 represents the due date and B1 has the formula =NOW()
>
> HTH
> Regards,
> Howard
>
> "Axel" <[email protected]> wrote in message
> news:[email protected]...
>>I have a simple Excel spreadsheet with about 50 rows and 8 columns of
>>dates. Each row is a different activity entry and all columns in each row
>>have a date cell with a simple + or - formula relating to a base date in
>>the final column.
>>
>> Although it is not important for the logic, the final column is the
>> starting date for works on a construction site and the preceding dates
>> are all critical events leading up to the starting date. Each row has a
>> different activity. Each row is stand alone and is not linked by
>> formulas to any other row.
>>
>> How do I get Excel to automatically highlight dates that have expired or
>> are about to expire?
>>
>> A simple automatic shading of the cells for dates which have past would
>> be an excellent start.
>>
>> This is Windows XP and Excel Xp.
>>
>> Any help would be appreciated.
>>
>> Axel
>>
>>
>>
>>
>
>

Hi,

This looked great on the draft but is a heap of mince when sent! Not sure
of you can make any sense of this jumbled mess!

Axel


"Axel" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Thanks for the response, but I am struggling to set this up. My initial
> query was probably too simplistic. Extract from actual sheet:
>
> D E
> F G H I J K
>
> WP Nr Work Package Final Tender Assessment Pre-order Meeting
> Acceptance Letter Prepare Order Docs Place Official Order Pre Start
> Meeting Lead-in (Wks) Site Start
> 9 Fire Protection 09/01/2006 16/01/2006 23/01/2006 2
> 06/02/2006 20/02/2006 6 06/03/2006
>
>
> Existing cell entries =E12-7 =F12-7
> =K12-(j12*7) 2 =F12+14 =K12-14 6
> 06/03/2006
>
>
>
> Can you possibly assist further with actual cell entries?
>
> Many thanks,
>
> Axel
>
>
> "L. Howard Kittle" <[email protected]> wrote in message
> news:[email protected]...
>> Hi axel,
>>
>> Select the cell with the due date and under Format > Conditional Format >
>> Formula Is > =A1>B1 > Format > Pattern > Choose color > OK > OK.
>>
>> Where A1 represents the due date and B1 has the formula =NOW()
>>
>> HTH
>> Regards,
>> Howard
>>
>> "Axel" <[email protected]> wrote in message
>> news:[email protected]...
>>>I have a simple Excel spreadsheet with about 50 rows and 8 columns of
>>>dates. Each row is a different activity entry and all columns in each row
>>>have a date cell with a simple + or - formula relating to a base date in
>>>the final column.
>>>
>>> Although it is not important for the logic, the final column is the
>>> starting date for works on a construction site and the preceding dates
>>> are all critical events leading up to the starting date. Each row has a
>>> different activity. Each row is stand alone and is not linked by
>>> formulas to any other row.
>>>
>>> How do I get Excel to automatically highlight dates that have expired or
>>> are about to expire?
>>>
>>> A simple automatic shading of the cells for dates which have past would
>>> be an excellent start.
>>>
>>> This is Windows XP and Excel Xp.
>>>
>>> Any help would be appreciated.
>>>
>>> Axel
>>>
>>>
>>>
>>>
>>
>>
>
>

It WAS pretty scrambled.
Send me a sample workbook and I will give it a go. 'splain what you are
shooting for.

Howard

"Axel" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> This looked great on the draft but is a heap of mince when sent! Not sure
> of you can make any sense of this jumbled mess!
>
> Axel
>
>
> "Axel" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Thanks for the response, but I am struggling to set this up. My initial
>> query was probably too simplistic. Extract from actual sheet:
>>
>> D E F
>> G H I J K
>>
>> WP Nr Work Package Final Tender Assessment Pre-order Meeting
>> Acceptance Letter Prepare Order Docs Place Official Order Pre Start
>> Meeting Lead-in (Wks) Site Start
>> 9 Fire Protection 09/01/2006 16/01/2006 23/01/2006 2
>> 06/02/2006 20/02/2006 6 06/03/2006
>>
>>
>> Existing cell entries =E12-7 =F12-7
>> =K12-(j12*7) 2 =F12+14 =K12-14 6 06/03/2006
>>
>>
>>
>> Can you possibly assist further with actual cell entries?
>>
>> Many thanks,
>>
>> Axel
>>
>>
>> "L. Howard Kittle" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hi axel,
>>>
>>> Select the cell with the due date and under Format > Conditional Format
>>> > Formula Is > =A1>B1 > Format > Pattern > Choose color > OK > OK.
>>>
>>> Where A1 represents the due date and B1 has the formula =NOW()
>>>
>>> HTH
>>> Regards,
>>> Howard
>>>
>>> "Axel" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>I have a simple Excel spreadsheet with about 50 rows and 8 columns of
>>>>dates. Each row is a different activity entry and all columns in each
>>>>row have a date cell with a simple + or - formula relating to a base
>>>>date in the final column.
>>>>
>>>> Although it is not important for the logic, the final column is the
>>>> starting date for works on a construction site and the preceding dates
>>>> are all critical events leading up to the starting date. Each row has
>>>> a different activity. Each row is stand alone and is not linked by
>>>> formulas to any other row.
>>>>
>>>> How do I get Excel to automatically highlight dates that have expired
>>>> or are about to expire?
>>>>
>>>> A simple automatic shading of the cells for dates which have past would
>>>> be an excellent start.
>>>>
>>>> This is Windows XP and Excel Xp.
>>>>
>>>> Any help would be appreciated.
>>>>
>>>> Axel
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>