Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
thanks
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:en d_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 |