due dates

[canman]

I'm a Newbie that's been missiled into a job that requires man
spreadsheets to track recertification dates. The sheets are no proble
but I'd like to be able to make due date cells change colour to yello
if in month of recert and red if past recert date.
Doable????? thanks for any inpu

 

[Max]

Perhaps try this ..

Assuming your due dates are listed in col B,
header in row1, dates in B2 down

Due date
31-Aug-04
12-Sep-04
01-Dec-04
etc

Select col B

Click Format > Conditional Formatting
Make these settings:

Condition 1
Formula Is | =B2=""
Leave it as "No format set"

Click Add >> (this adds condition 2)

Condition 2
Formula Is | =MONTH(TODAY())=MONTH(B2)
Click Format button > Patterns tab > Yellow? > OK

Click Add >> (this adds condition 3)

Condition 3
Formula Is | =TODAY()>B2
Click Format button > Patterns tab > Pale red? > OK

Click OK at the main dialog

 

[Guest]

Quote:

Condition 1
Formula Is | =B2=""
Leave it as "No format set"

Actually, Condition 1 is redundant, this can be the default condition and
format for blank cells and cells which have dates before the due dates.

Cheers.

Perhaps try this ..

Assuming your due dates are listed in col B,
header in row1, dates in B2 down

Due date
31-Aug-04
12-Sep-04
01-Dec-04
etc

Select col B

Click Format > Conditional Formatting
Make these settings:

Condition 1
Formula Is | =B2=""
Leave it as "No format set"

Click Add >> (this adds condition 2)

Condition 2
Formula Is | =MONTH(TODAY())=MONTH(B2)
Click Format button > Patterns tab > Yellow? > OK

Click Add >> (this adds condition 3)

Condition 3
Formula Is | =TODAY()>B2
Click Format button > Patterns tab > Pale red? > OK

Click OK at the main dialog
--
Note that you can also simultaneously format
the font colour/bold (in the Font tab), etc
besides just formatting the fill colour in the Patterns tab

 

[Max]

Actually, Condition 1 is redundant, this can be the default
condition and format for blank cells and cells which have
dates before the due dates.

Agreed, but only if the conditions above were stated ...

IMHO, it was prudent here as the suggested construct
involves selecting the entire column and was also
meant to illustrate the importance in the order of formula constructs in
conditional formatting to get correct results

... and OP did state that s/he's new <g>

so .. there's still value in the "redundancy"

 

[Guest]

I appreciate your prudence for completeness.

Actually, the 3 conditions are mutually exclusive. A date can only be:
1. < due date, or
2. = due date, or
3. > due date

As long as 2 of the conditions are not satisfied, it is save to conclude
that the third condition is satisfied. Therefore, there is no need to monitor
all 3 conditions. The default formatting will always be applied when the
other 2 conditions are not met.

From time complexity point of view, it will take less time to evaluate 2
conditions than to evaluate 3. Excluding redundant steps will help the
computer to reduce the time required to generate the desired result. The
delay may not be noticeable if the record size is small, but will become
significant as the record size grows.

Let say the PC need 1 unit of time to evaluate one unnecessary condition for
1 record. When the record size grow from 1 to 1000, the time taken actually
increases 1000 times. Hence, by removing that 1 unnecessary condition you
will speed up computation 1000 times.

Cheers.

 

[Max]

As the suggestion stands,
think condition 3: =TODAY()>B2
would evaluate to TRUE for blank cells in col B
which would then return incorrect cond. formatting results
if not for condition 1 ..

but of course, the suggestion (with 3 conditions) is
only one way of framing it up to work a/c to OP's specs
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

I appreciate your prudence for completeness.

Actually, the 3 conditions are mutually exclusive. A date can only be:
1. < due date, or
2. = due date, or
3. > due date

As long as 2 of the conditions are not satisfied, it is save to conclude
that the third condition is satisfied. Therefore, there is no need to monitor
all 3 conditions. The default formatting will always be applied when the
other 2 conditions are not met.

From time complexity point of view, it will take less time to evaluate 2
conditions than to evaluate 3. Excluding redundant steps will help the
computer to reduce the time required to generate the desired result. The
delay may not be noticeable if the record size is small, but will become
significant as the record size grows.

Let say the PC need 1 unit of time to evaluate one unnecessary condition for
1 record. When the record size grow from 1 to 1000, the time taken actually
increases 1000 times. Hence, by removing that 1 unnecessary condition you
will speed up computation 1000 times.

Cheers.

 

[Guest]

Max, you are right. =TODAY()>B2 does evaluate to TRUE when B2 is blank. It
seems like having condition 1 is inevitable.

Since blank cell is a special case, then condition 1 is a must. Which means
that the default format should be set to either for condition 2 or condition
3.

OP, its your call. I hope we have provided you with some useful info in
using Conditional Formatting.

Cheers.

We all learn something new everyday!

As the suggestion stands,
think condition 3: =TODAY()>B2
would evaluate to TRUE for blank cells in col B
which would then return incorrect cond. formatting results
if not for condition 1 ..

but of course, the suggestion (with 3 conditions) is
only one way of framing it up to work a/c to OP's specs
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

I appreciate your prudence for completeness.

Actually, the 3 conditions are mutually exclusive. A date can only be:
1. < due date, or
2. = due date, or
3. > due date

As long as 2 of the conditions are not satisfied, it is save to conclude
that the third condition is satisfied. Therefore, there is no need to monitor
all 3 conditions. The default formatting will always be applied when the
other 2 conditions are not met.

From time complexity point of view, it will take less time to evaluate 2
conditions than to evaluate 3. Excluding redundant steps will help the
computer to reduce the time required to generate the desired result. The
delay may not be noticeable if the record size is small, but will become
significant as the record size grows.

Let say the PC need 1 unit of time to evaluate one unnecessary condition for
1 record. When the record size grow from 1 to 1000, the time taken actually
increases 1000 times. Hence, by removing that 1 unnecessary condition you
will speed up computation 1000 times.

Cheers.

 

[Guest]

I am so sorry, the second paragraph of the last post should not be included.

Max, you are right. =TODAY()>B2 does evaluate to TRUE when B2 is blank. It
seems like having condition 1 is inevitable.

Since blank cell is a special case, then condition 1 is a must. Which means
that the default format should be set to either for condition 2 or condition
3.

OP, its your call. I hope we have provided you with some useful info in
using Conditional Formatting.

Cheers.

We all learn something new everyday!

 

[Max]

... Agreed <g>.
cheers

 

[Guest]

Dear Max: I jumped for joy when I saw this thread. Alas, following the
directions exactly - I cannot get it to work. I am tracking expiration dates
also. Have formatted my dates as 1/1/2005 - it just ignores all the
formatting I do. What could possibly be wrong. Appreciate any feedback at
all.
Niki

Perhaps try this ..

Assuming your due dates are listed in col B,
header in row1, dates in B2 down

Due date
31-Aug-04
12-Sep-04
01-Dec-04
etc

Select col B

Click Format > Conditional Formatting
Make these settings:

Condition 1
Formula Is | =B2=""
Leave it as "No format set"

Click Add >> (this adds condition 2)

Condition 2
Formula Is | =MONTH(TODAY())=MONTH(B2)
Click Format button > Patterns tab > Yellow? > OK

Click Add >> (this adds condition 3)

Condition 3
Formula Is | =TODAY()>B2
Click Format button > Patterns tab > Pale red? > OK

Click OK at the main dialog
--
Note that you can also simultaneously format
the font colour/bold (in the Font tab), etc
besides just formatting the fill colour in the Patterns tab

 

[Kevin H. Stecyk]

Hi Niki,

I've tested this so you should be okay.

Assume your dates are in column A.

Select your dates in col A

Click Format > Conditional Formatting
Make these settings:

Condition 1
Formula Is |
=(AND(TODAY()>=DATE(YEAR(A1),MONTH(A1),1),TODAY()<(DATE(YEAR(A1),MONTH(A1)+1,1)-1)))
Click Format button > Patterns tab > Yellow? > OK
Click Add >> (this adds condition 2)

Condition 2
Formula Is | =TODAY()>=DATE(YEAR(A1),MONTH(A1),1)-1
Click Format button > Patterns tab > Red? > OK

Done.

Condition 1 checks to see if today's date is greater than the first day of
this the month referenced in Col A AND less than the last day of the month
referenced in Col A.
Condition 2 checks to see if today's date is greater than the last day of
the prior month referenced in Col A.

I hope this helps.

Best regards,
Kevin

 

[Max]

Hi, if you'd like to, send me a sample book at :

demechanik <at> yahoo <dot> com
or,
xdemechanik <at>yahoo<dot>com

I'll see what can be done ..

 

[Guest]

If it completely ignored all the formattings, then what was shown?

I track due dates too, to resolution in term of days before and after a due
to track.

Presented here, is an improved version to what was previously discussed. It
eliminated the condition explicitly written to check whether cell is empty.

Assume due dates in column A.
Condition 1
Formula Is | =AND(B2<>"",B2=TODAY())
=> Today is the due date

Condition 2
Formula Is | =AND(B2<>"",B2-TODAY()<=7)
=> Date due in one week's time

Condition 3
Formula Is | =AND(B2<>"",TODAY()>B2)
=> Overdue

If the above 3 conditions are not satisfied (i.e. date is due in more than a
week's time or cell is empty), the default format will be used.
The advantages of this method over the previous one are:
1. It allows one more condition.
2. Blank cells are ignored.

The disadvantage of this method over the previous one is that it cannot
differentiate blank cells from cells with due dates longer than a week.

Regards.

Dear Max: I jumped for joy when I saw this thread. Alas, following the
directions exactly - I cannot get it to work. I am tracking expiration dates
also. Have formatted my dates as 1/1/2005 - it just ignores all the
formatting I do. What could possibly be wrong. Appreciate any feedback at
all.
Niki

 

[Guest]

Sorry, the dates are in column B instead of column A.