|
count work days
I need a way of counting workdays between two dates. i can do it using the NETWORKDAYS function but I know that not everyone has the add ons turned on so its pointless as 100 people will be viewing this. Any Help is greatly appreciated. -- Message posted via http://www.officekb.com |
count work days
On 1 jul, 20:17, "Darrell_Sarrasin via OfficeKB.com" <u33691@uwe
wrote: I need a way of counting workdays between two dates. *i can do it using the NETWORKDAYS function but I know that not everyone has the add ons turned on so its pointless as 100 people will be viewing this. Any Help is greatly appreciated. -- Message posted viahttp://www.officekb.com Hi Darrell, I have had the same problem in the past using Excel 2003. I created this combo of functions: Function isWeekend(aDate As Date) As Boolean If Weekday(aDate, vbSunday) = vbSunday Or Weekday(aDate, vbSunday) = vbSaturday Then isWeekend = True Else isWeekend = False End If End Function Function countWorkdates(datFrom As Date, datUpto As Date) As Integer Application.Volatile Dim intCount As Integer Dim datLoop As Date ' For datLoop = datFrom To datUpto If Not isWeekend(datLoop) Then intCount = intCount + 1 Next countWorkdates = intCount End Function HTH, Wouter |
count work days
Hi, You can do it with Sumproduct and weekday which isn't part of ATP like this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6)) and if you want to include holidays try this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C8,0)))) Where your holidays are in c1 - c8 Mike "Darrell_Sarrasin via OfficeKB.com" wrote: I need a way of counting workdays between two dates. i can do it using the NETWORKDAYS function but I know that not everyone has the add ons turned on so its pointless as 100 people will be viewing this. Any Help is greatly appreciated. -- Message posted via http://www.officekb.com |
count work days
If someone enter a weekend date as a holiday, that will deduct 1 for that date as well. -- __________________________________ HTH Bob "Mike H" wrote in message ... Hi, You can do it with Sumproduct and weekday which isn't part of ATP like this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6)) and if you want to include holidays try this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C8,0)))) Where your holidays are in c1 - c8 Mike "Darrell_Sarrasin via OfficeKB.com" wrote: I need a way of counting workdays between two dates. i can do it using the NETWORKDAYS function but I know that not everyone has the add ons turned on so its pointless as 100 people will be viewing this. Any Help is greatly appreciated. -- Message posted via http://www.officekb.com |
count work days
B1 contains 6/10/2009
C1 contains 7/1/2009 D1 contains the formula =CEILING((C1-B1)-(((C1-B1)/7)*2),1) This returns the value 15, which is the number of weekdays between the two dates. HTH, JP On Jul 1, 2:17*pm, "Darrell_Sarrasin via OfficeKB.com" <u33691@uwe wrote: I need a way of counting workdays between two dates. *i can do it using the NETWORKDAYS function but I know that not everyone has the add ons turned on so its pointless as 100 people will be viewing this. Any Help is greatly appreciated. -- Message posted viahttp://www.officekb.com |
count work days
Bob, I know, do you know how to eliminate that problem? Mike "Bob Phillips" wrote: If someone enter a weekend date as a holiday, that will deduct 1 for that date as well. -- __________________________________ HTH Bob "Mike H" wrote in message ... Hi, You can do it with Sumproduct and weekday which isn't part of ATP like this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6)) and if you want to include holidays try this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C8,0)))) Where your holidays are in c1 - c8 Mike "Darrell_Sarrasin via OfficeKB.com" wrote: I need a way of counting workdays between two dates. i can do it using the NETWORKDAYS function but I know that not everyone has the add ons turned on so its pointless as 100 people will be viewing this. Any Help is greatly appreciated. -- Message posted via http://www.officekb.com |
count work days
Bob, I think I agreed with you a bit too quickly. Take the dates 20/12/2009 - 31/12/2009 and 4 holiday dates 26/12, 27/12,28/12 & 21/12 in c1 - C4. Sumproduct produces these 2 arrays. Because Sat and Sun are already evaluting as FALSE the second array is irrelevent and only becomes relevant if the first array evalueates as TRUE which it never will for a weekend date. The same as networkdays (7 in this case) or am I missing something? 20/12/2009 0 1 21/12/2009 1 0 22/12/2009 1 1 23/12/2009 1 1 24/12/2009 1 1 25/12/2009 1 1 26/12/2009 0 0 27/12/2009 0 0 28/12/2009 1 0 29/12/2009 1 1 30/12/2009 1 1 31/12/2009 1 1 Mike "Mike H" wrote: Bob, I know, do you know how to eliminate that problem? Mike "Bob Phillips" wrote: If someone enter a weekend date as a holiday, that will deduct 1 for that date as well. -- __________________________________ HTH Bob "Mike H" wrote in message ... Hi, You can do it with Sumproduct and weekday which isn't part of ATP like this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6)) and if you want to include holidays try this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C8,0)))) Where your holidays are in c1 - c8 Mike "Darrell_Sarrasin via OfficeKB.com" wrote: I need a way of counting workdays between two dates. i can do it using the NETWORKDAYS function but I know that not everyone has the add ons turned on so its pointless as 100 people will be viewing this. Any Help is greatly appreciated. -- Message posted via http://www.officekb.com |
count work days
I think you are right Mike. My standard formula for that is a lot longer, I assumed there was a reason for that. I tested yours thinking it must have constraints, but it must have been rubbish testing as on re-checking your formula seems fine. My apologies. -- __________________________________ HTH Bob "Mike H" wrote in message ... Bob, I think I agreed with you a bit too quickly. Take the dates 20/12/2009 - 31/12/2009 and 4 holiday dates 26/12, 27/12,28/12 & 21/12 in c1 - C4. Sumproduct produces these 2 arrays. Because Sat and Sun are already evaluting as FALSE the second array is irrelevent and only becomes relevant if the first array evalueates as TRUE which it never will for a weekend date. The same as networkdays (7 in this case) or am I missing something? 20/12/2009 0 1 21/12/2009 1 0 22/12/2009 1 1 23/12/2009 1 1 24/12/2009 1 1 25/12/2009 1 1 26/12/2009 0 0 27/12/2009 0 0 28/12/2009 1 0 29/12/2009 1 1 30/12/2009 1 1 31/12/2009 1 1 Mike "Mike H" wrote: Bob, I know, do you know how to eliminate that problem? Mike "Bob Phillips" wrote: If someone enter a weekend date as a holiday, that will deduct 1 for that date as well. -- __________________________________ HTH Bob "Mike H" wrote in message ... Hi, You can do it with Sumproduct and weekday which isn't part of ATP like this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6)) and if you want to include holidays try this =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C8,0)))) Where your holidays are in c1 - c8 Mike "Darrell_Sarrasin via OfficeKB.com" wrote: I need a way of counting workdays between two dates. i can do it using the NETWORKDAYS function but I know that not everyone has the add ons turned on so its pointless as 100 people will be viewing this. Any Help is greatly appreciated. -- Message posted via http://www.officekb.com |
| All times are GMT +1. The time now is 12:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com