I'm getting confused as to where you count days from.
The date difference between Thurs-Mon in the same week is 3.
My original understanding this is within the 0 to 3 difference, Thurs is the
3rd day 'after' Mon, and within the span that triggers your yellow CF. If
this the correct interpretation of what you want test my previous
suggestion.
However below you imply a date difference of 2 is the largest that satisfies
the condition (not withstanding Fri-Sun which will need tb excluded).
Perhaps give unambiguous pairs of examples with dates in same week and
spanning a weekend that just pass and just fail the condition.
Regards,
Peter T
"Ed" wrote in message
...
or am I missing something
I'm only counting Mon-Thurs. If the time range for a particular item is
three days, an incoming date of Tues 2/20/07 will have the third day on
Thurs 2/22/07, but an incoming date of Wed 2/21/07 will have the third day
on Mon 2/26/07.
Maybe this is not as hard as I think it is, and I'm missing a simple
little
thing that will do the trick.
Ed
"Peter T" <peter_t@discussions wrote in message
...
So isn't that simply
(date2 - date1) < 7 yellow condition
or
(date2 - date1) 6 white condition
where date2 is the later date
or am I missing something
Regards,
Peter T
"Ed" wrote in message
...
In your example B1 is not in the formula so changing B1 will do
nothing.
Thanks, Peter. _Now_ I see that!
It's not clear under what condition(s) you want your CF in B1 to
flag.
I'm trying to get B1 to turn yellow if the date in B1 is less than 3
days
from A1, but only counting days Mon-Thurs. So a date of 2/20/2007 in
A1
should give me a yellow cell up to 2/26/2007 in B1, and a white cell
after
that.
Ed
"Peter T" <peter_t@discussions wrote in message
...
I think you only need the one Weekday formula -
(Weekday(ref-date,3) <=3) ' true if Mon-Thurs
It's not clear under what condition(s) you want your CF in B1 to
flag.
In
your example B1 is not in the formula so changing B1 will do nothing.
Regards,
Peter T
"Ed" wrote in message
...
Chip:
Thanks for the help, but I'm not sure I know how to make this work.
I put 2/20/2007 in A1, 2/22/2007 in B1, and Conditional Formatting
in
B1
with Formula Is
="SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10) <=5))<3"
should turn the cell yellow. It did not. I tried changing B1 to
2/27/2007 - still no go.
What did I miss?
Ed
"Chip Pearson" wrote in message
...
Ed,
For data validation and conditional formatting, use
=IF(AND(WEEKDAY(A1)=2,WEEKDAY(A1)<=5),TRUE,FALSE)
This returns True or False indicating whether A1 is between Monday
and
Thursday.
To count the number of dates in A1:A10 that are between Monday and
Thursday, use
=SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10)< =5))
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"Ed" wrote in message
...
I would like to create a Task Sheet with Due Dates. The Due Date
would
be
a certain number of working days from the Order Date. Our work
week
is
four 10-hour days, Monday - Thursday. I wanted to set up a
color-code
for
the Due Date with data validation, but got lost trying to figure
out
how
to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this
easily
possible with validation? Or would a macro that evaluates each
date
on
open be better?
Ed