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

How about a user-defined function?

Public Function DueDate(OrderDate As Date, WorkdaysLater As Integer)
As Date
Dim iCt As Integer
Dim iCt2 As Integer
Do
iCt = iCt + 1
If Weekday(OrderDate + iCt) 1 And _
Weekday(OrderDate + iCt) < 6 Then iCt2 = iCt2 + 1
Loop Until iCt2 = WorkdaysLater
DueDate = Format(OrderDate + iCt, "m/d/yy")
End Function

Hth,
Merjet

Merjet:

I've never used a UDF before. I thinking I would name a range OrderDate and
put a date in it, then in the Due Date column put the formula
=DueDate(OrderDate)"? Did I come close?

Ed

"merjet" wrote in message
ups.com...
How about a user-defined function?

Public Function DueDate(OrderDate As Date, WorkdaysLater As Integer)
As Date
Dim iCt As Integer
Dim iCt2 As Integer
Do
iCt = iCt + 1
If Weekday(OrderDate + iCt) 1 And _
Weekday(OrderDate + iCt) < 6 Then iCt2 = iCt2 + 1
Loop Until iCt2 = WorkdaysLater
DueDate = Format(OrderDate + iCt, "m/d/yy")
End Function

Hth,
Merjet

I've never used a UDF before. I thinking I would name a range OrderDate and
put a date in it, then in the Due Date column put the formula
=DueDate(OrderDate)"? Did I come close?

You use it like Excel' worksheet function.

That's close, but it has two inputs. Suppose cell A2 holds an order
date, and you want a due date 10 days later. In another cell, put the
formula: =DueDate(A2, 10)

Hth,
Merjet

I put the Function in the ThisWorkbook module, and I'm getting a #NAME?
error.

Ed

"merjet" wrote in message
oups.com...
I've never used a UDF before. I thinking I would name a range OrderDate
and
put a date in it, then in the Due Date column put the formula
=DueDate(OrderDate)"? Did I come close?

You use it like Excel' worksheet function.

That's close, but it has two inputs. Suppose cell A2 holds an order
date, and you want a due date 10 days later. In another cell, put the
formula: =DueDate(A2, 10)

Hth,
Merjet

In the VB Editor, use the menu Insert | Module. That adds Module1. Put
the function there.

Merjet

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

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

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

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

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