Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count work days excluding Fridays and Saturdays ???
I would like to know how many working days between 2 dates. BUT, the
networkdays function only takes into account SATURDAYS and SUNDAYS as week end. I live in a country where week ends are FRIDAYS and SATURDAYS... Your help will be really appreciated. Best Regards, Marouane |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count work days excluding Fridays and Saturdays ???
Try this:
A1 = start date B1 = end date C1:C10 = holiday dates to exclude*** =NETWORKDAYS(A1+1,B1+1,C1:C10) *** Add 1 day to each holiday date. If 1/1/2007 (m/d/y) is a holiday enter it as 1/2/2007. Or, to avoid that confusion enter the actual holiday dates and add the 1 day directly in the formula. That will make the formula an array and will need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =NETWORKDAYS(A1+1,B1+1,C1:C10+1) Biff "Excel Dubai" <Excel wrote in message ... I would like to know how many working days between 2 dates. BUT, the networkdays function only takes into account SATURDAYS and SUNDAYS as week end. I live in a country where week ends are FRIDAYS and SATURDAYS... Your help will be really appreciated. Best Regards, Marouane |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count work days excluding Fridays and Saturdays ???
On Feb 14, 10:03 pm, Excel Dubai <Excel
wrote: I would like to know how many working days between 2 dates. BUT, the networkdays function only takes into account SATURDAYS and SUNDAYS as weekend. I live in a country where week ends are FRIDAYS and SATURDAYS The following is a general solution that will permit you count the number of any set of days of the week, not just the 5 consecutive days excluding 2 "weekend" days: =SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("a1:a"&A2- A1+1)))={1,2,3,4,5})) The above assumes that A1 is the start date and A2 is the end date. The constant {1,2,3,4,5} represents Sunday through Thursday. But, for example, if you replace it with {6,7}, the formula will count the number of "weekend" days, assuming they are Friday and Saturday. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count work days excluding Fridays and Saturdays ???
This formula will also give the sam result, although, of course, it doesn't
take account of holidays =SUM(INT((WEEKDAY(A1-{1,2,3,4,5})+A2-A1)/7)) "joeu2004" wrote: On Feb 14, 10:03 pm, Excel Dubai <Excel wrote: I would like to know how many working days between 2 dates. BUT, the networkdays function only takes into account SATURDAYS and SUNDAYS as weekend. I live in a country where week ends are FRIDAYS and SATURDAYS The following is a general solution that will permit you count the number of any set of days of the week, not just the 5 consecutive days excluding 2 "weekend" days: =SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("a1:a"&A2- A1+1)))={1,2,3,4,5})) The above assumes that A1 is the start date and A2 is the end date. The constant {1,2,3,4,5} represents Sunday through Thursday. But, for example, if you replace it with {6,7}, the formula will count the number of "weekend" days, assuming they are Friday and Saturday. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Saturdays in a List | Excel Worksheet Functions | |||
Due date excluding weekend days | Excel Worksheet Functions | |||
Excel - list days of a month, excluding Sundays | Excel Discussion (Misc queries) | |||
How do you count work days excluding weekends and holidays? | Excel Discussion (Misc queries) | |||
Count Days excluding Sundays | Excel Worksheet Functions |