Re: thanks
- From: "T. Valko" <biffinpitt@xxxxxxxxxxx>
- Date: Tue, 2 Jan 2007 18:59:29 -0500
Here's the problem......
Every time someone replies to one of your posts you then follow-up and
change the conditions. The "new" conditions of your last post are
*EXTREMELY* complicated. I don't use that phrase very often! Any solution to
your last post is going to be a true "monster" formula. I haven't even
attempted it.
I'll take another look at it tonight. No guarantees!
Biff
"been dribbled to 2007" <beendribbledto2007@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in message news:A3A5D457-B474-47BA-BF7B-41664675C93A@xxxxxxxxxxxxxxxx
wish to proceed farther,
after some time spending with this forum, I do have learned a lot through
a
deep and kind replies.
Formula for workday [excluding sundays and holiday or any weekdays] was
such
a real world direct finder of workday series.
I gathered it for someone who may need it for this year.
Formula.
1)
=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5;6;7})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6;7},0))*(holidays>=MIN(end_date,start_date))*(holidays<=MAX(end_date,start_date)))
Formula 2)
=SUM(INT((WEEKDAY(A1-{1,2,3,4,5,6},2)+A732-A1)/7))-(SUMPRODUCT(--(holidays>=start),--(holidays<=end))-SUMPRODUCT(--(WEEKDAY(holidays,2)=7),--(holidays>=start),--(holidays<=end)))
Formula 3)
=IF(WEEKDAY(end_date)=1,"THIS IS A REST
DAY",IF(LOOKUP(end_date,holidays)=B3,"THIS IS A
HOLIDAY",SUMPRODUCT((WEEKDAY(start_date:end_date)={2,3,4,5,6,7})*1)-SUMPRODUCT((WEEKDAY(holidays)={2,3,4,5,6,7})*(MIN(start_date:end_date)<=holidays)*(MAX(start_date:end_date)>=holidays))))
I am still trying to gather resolving formula to insert another real
workworld condition , a no work between 2 holiday dates or holiday and
sunday....
more power to those who help me like J.Latham and Bob.Phillips and
T.Valko..
driller
.
- Follow-Ups:
- Re: thanks
- From: been drilbled to 2007
- Re: thanks
- Prev by Date: Re: Not sure if this is possible...
- Next by Date: RE: Not sure if this is possible...
- Previous by thread: Re: Not sure if this is possible...
- Next by thread: Re: thanks
- Index(es):
Relevant Pages
|
