If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Workdays (inc Sat) formula
Opps! You're right. Didn't even think about that. Thanks. :)
-- Dana DeLouis "Bob Phillips" wrote in message ... but what about if one of the days in the holiday range is a Saturday? snip |
#12
|
|||
|
|||
Workdays (inc Sat) formula
It occurred to me as I catered for it in my code (I hopevbg).
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dana DeLouis" wrote in message ... Opps! You're right. Didn't even think about that. Thanks. :) -- Dana DeLouis "Bob Phillips" wrote in message ... but what about if one of the days in the holiday range is a Saturday? snip |
#13
|
|||
|
|||
Workdays (inc Sat) formula
Great stuff chaps, thanks. But what about crossing a year change.
Your formulas give me 300 days between December and January of the following year...! Michael "Frank Kabel" wrote in message ... Hi if A1 stores the start date and B1 the end date try the following array formula (entered with CTRL+SHIFT+ENTER): =B1-A1+1-SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(B1-A1)+1)))=1,1,0) ) -- Regards Frank Kabel Frankfurt, Germany x wrote: I need a formula to tell me the number of workdays between two dates, the problem being that Saturday I consider a workday. The NETWORKDAYS function obviously won't do. Thanks Michael |
#14
|
|||
|
|||
Workdays (inc Sat) formula
Try this:
=NETWORKDAYS(A1,B1,HOLIDAYS)+INT((B1-A1)/7)+IF(WEEKDAY(A1)6-MOD(B1-A1,7),1, 0)-SUM((WEEKDAY(HOLIDAYS)=6)*1) Not guaranteed dto work; let us know how it turns out. -- Vasant x wrote in message ... Great stuff chaps, thanks. But what about crossing a year change. Your formulas give me 300 days between December and January of the following year...! Michael "Frank Kabel" wrote in message ... Hi if A1 stores the start date and B1 the end date try the following array formula (entered with CTRL+SHIFT+ENTER): =B1-A1+1-SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(B1-A1)+1)))=1,1,0) ) -- Regards Frank Kabel Frankfurt, Germany x wrote: I need a formula to tell me the number of workdays between two dates, the problem being that Saturday I consider a workday. The NETWORKDAYS function obviously won't do. Thanks Michael |
|
Thread Tools | |
Display Modes | |
|
|