Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays plus Sundays
Hi,
I need to calculate the number of work days between two dates that includes Sundays as a workday. So only Saturday would be excluded. Is there a way to count the number of Sundays between two dates? I could then just add that to the result of the NetWorkDays function. Thanks! - Kurt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays plus Sundays
I would use a combination of the following:
First step = end_date minus start_date divided by 7 and take only the integer part. Second step = using the WEEKDAY function to identify if another extra Sunday needs to be added or not? Best of luck. "Kurt Levitan" wrote: Hi, I need to calculate the number of work days between two dates that includes Sundays as a workday. So only Saturday would be excluded. Is there a way to count the number of Sundays between two dates? I could then just add that to the result of the NetWorkDays function. Thanks! - Kurt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays plus Sundays
If you DON'T need to account for any holidays:
F1 = start date G1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(F1&":"&G1)))<7)) If you DO need to account for holidays: List of holiday dates to be excluded from the calculation in I1:I10 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(F1&":"&G1)))<7))-SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(F1&":"&G1)),I1:I10,0) ))) Biff "Kurt Levitan" wrote in message ... Hi, I need to calculate the number of work days between two dates that includes Sundays as a workday. So only Saturday would be excluded. Is there a way to count the number of Sundays between two dates? I could then just add that to the result of the NetWorkDays function. Thanks! - Kurt |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays plus Sundays
Thanks Biff,
You actually provided me with a simpler answer in another post for somneone else. I can use the Network Days to get the week days minus holidays and then use : SUM(INT((WEEKDAY(A1-7,2)+B1-A1)/7)) to add back in the Sundays. - Kurt "Biff" wrote: If you DON'T need to account for any holidays: F1 = start date G1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(F1&":"&G1)))<7)) If you DO need to account for holidays: List of holiday dates to be excluded from the calculation in I1:I10 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(F1&":"&G1)))<7))-SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(F1&":"&G1)),I1:I10,0) ))) Biff "Kurt Levitan" wrote in message ... Hi, I need to calculate the number of work days between two dates that includes Sundays as a workday. So only Saturday would be excluded. Is there a way to count the number of Sundays between two dates? I could then just add that to the result of the NetWorkDays function. Thanks! - Kurt |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Networkdays plus Sundays
I was going to suggest that but I thought what I did suggest is a little
easier to understand and maintain if the need arises. Thanks for the feedback! Biff "Kurt Levitan" wrote in message ... Thanks Biff, You actually provided me with a simpler answer in another post for somneone else. I can use the Network Days to get the week days minus holidays and then use : SUM(INT((WEEKDAY(A1-7,2)+B1-A1)/7)) to add back in the Sundays. - Kurt "Biff" wrote: If you DON'T need to account for any holidays: F1 = start date G1 = end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(F1&":"&G1)))<7)) If you DO need to account for holidays: List of holiday dates to be excluded from the calculation in I1:I10 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(F1&":"&G1)))<7))-SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(F1&":"&G1)),I1:I10,0) ))) Biff "Kurt Levitan" wrote in message ... Hi, I need to calculate the number of work days between two dates that includes Sundays as a workday. So only Saturday would be excluded. Is there a way to count the number of Sundays between two dates? I could then just add that to the result of the NetWorkDays function. Thanks! - Kurt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining an If Statement with NETWORKDAYS function | Excel Discussion (Misc queries) | |||
networkdays vs days360 | Excel Discussion (Misc queries) | |||
# of Sundays in a year | Excel Discussion (Misc queries) | |||
networkdays | Excel Worksheet Functions | |||
MS Excel Function - Networkdays | Excel Worksheet Functions |