C
Chuy
Hi! Can Anyone help me please!!!
I Need the Function WORKDAY buy that INCLUDES saturdays and EXCLUDE
Holidays...
I Need the Function WORKDAY buy that INCLUDES saturdays and EXCLUDE
Holidays...
C
Chuy
I really need as an Urgent Matter....
I Need Like the WORKDAY function but That *INCLUDES SATURDAYS *and
*EXCLUDES HOLIDAYS*!!! Please!!!! Can Anyone Help Me????
I Need Like the WORKDAY function but That *INCLUDES SATURDAYS *and
*EXCLUDES HOLIDAYS*!!! Please!!!! Can Anyone Help Me????
V
vezerid
Chuy,
the following *array* formula (should be entered with
Shift+Ctrl+Enter), assumes the starting date in E1, the ending day in
E2 and a list of additional holidays in H1:H3. There might be a simpler
one, but this one works, at least according to my tests.
=SUM((WEEKDAY(ROW(INDIRECT(VALUE(E1)&":"&VALUE(E2))),1)<>1)*(1-ISNUMBER(MATCH(ROW(INDIRECT(VALUE(E1)&":"&VALUE(E2))),H1:H3,0))))
Does this help?
Kostis Vezerides
the following *array* formula (should be entered with
Shift+Ctrl+Enter), assumes the starting date in E1, the ending day in
E2 and a list of additional holidays in H1:H3. There might be a simpler
one, but this one works, at least according to my tests.
=SUM((WEEKDAY(ROW(INDIRECT(VALUE(E1)&":"&VALUE(E2))),1)<>1)*(1-ISNUMBER(MATCH(ROW(INDIRECT(VALUE(E1)&":"&VALUE(E2))),H1:H3,0))))
Does this help?
Kostis Vezerides
C
Chuy
Thank You Kostis, Really!
But I Require The Date as a Output of The Function...
INPUT
Holidays
Number Of Days
Start Day
Extra: Including Saturdays. (Workday works only from Monday To Friday)
*OUTPUT:*
Deadline Da
But I Require The Date as a Output of The Function...
INPUT
Holidays
Number Of Days
Start Day
Extra: Including Saturdays. (Workday works only from Monday To Friday)
*OUTPUT:*
Deadline Da
V
vezerid
Ooops! Wrong functionality.
For some time now I am trying to figure out a formula but I am stuck.
It is a much harder problem that for NETWORKDAYS(). I have not come up
with anything yet. My mind is clogged. I cannot even think the logic of
a VBA solution. I'll give it a try for some more time. Maybe it can be
done with 2 cells instead of one, I will have to see.
Sorry that I cannot help you right now, but I too am infatuated with
the problem and I cannot come up with anything yet.
I will post something before I go, even if it is to say that I quit
Regards,
Kostis
For some time now I am trying to figure out a formula but I am stuck.
It is a much harder problem that for NETWORKDAYS(). I have not come up
with anything yet. My mind is clogged. I cannot even think the logic of
a VBA solution. I'll give it a try for some more time. Maybe it can be
done with 2 cells instead of one, I will have to see.
Sorry that I cannot help you right now, but I too am infatuated with
the problem and I cannot come up with anything yet.
I will post something before I go, even if it is to say that I quit
Regards,
Kostis
C
Chuy
Thank You Again Kostis!
I allready hace the one of Networkdays, This formula Im Positng I Mede
It...
=IF(A2<=B2,(SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&B2)))=0),--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),3)<6))-1),(SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&B2)))=0),--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),3)<6))-1)*-1)
*Where:*A2 = Start Date
B2 = End Date
Holidays = Range of Holidays
I Repeated The
"SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&B2)))=0),--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),3)<6))-1"
So That in case that the start date has been in the past...
Best Regards.
Chuy
I allready hace the one of Networkdays, This formula Im Positng I Mede
It...
=IF(A2<=B2,(SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&B2)))=0),--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),3)<6))-1),(SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&B2)))=0),--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),3)<6))-1)*-1)
*Where:*A2 = Start Date
B2 = End Date
Holidays = Range of Holidays
I Repeated The
"SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&B2)))=0),--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),3)<6))-1"
So That in case that the start date has been in the past...
Best Regards.
Chuy