calculate business days(where there is 6 days a week)

**arnab0711** · 06-09-2011, 08:55 AM

Hi,
I want to calculate net business days between two dates,I have used networkdays() but its not giving desired result.

**Richard Buttrey** · 06-09-2011, 09:08 AM

Hi

You need to tell us what results you expect and why.

Regards

**daddylonglegs** · 06-09-2011, 09:12 AM

Which 6 days do you want to count? Assuming all days except Sundays you can use this formula in row 2 copied down

=SUM(INT((WEEKDAY(C2-{2,3,4,5,6,7})+N2-C2)/7))

If you have Excel 2010 you can use NETWORKDAYS.INTL function, i.e.

=NETWORKDAYS.INTL(C2,N2,11)

**arnab0711** · 06-09-2011, 09:16 AM

I need to calculate difference between column N and column C,and the formula would able to identify if there is any sunday in between the dates and exclude that date.

**arnab0711** · 06-09-2011, 09:25 AM

Thanks for the formula,but if you look at the first row,although by normal substraction the difference comes 1 days,but using your formula its coming 2 days,we need to rectify it.

**daddylonglegs** · 06-09-2011, 09:33 AM

The formula I suggested works like NETWORKDAYS in that it include both start and end date, e.g. like NETWORKDAYS it will count 2 for a date range starting on a Tuesday and ending the next day (because it counts both the Tuesday and the Wednesday).

If you want the count to be 1 in that instance then you can just subtract 1, i.e. use this formula.

=SUM(INT((WEEKDAY(C2-{2,3,4,5,6,7})+N2-C2)/7))-1

....but if the start or end date of the range might be a Sunday then that might not give you the results you expect. What result do you expect if start date is Sunday and end date the following Tuesday....or Friday to Sunday?

**arnab0711** · 06-09-2011, 09:34 AM

The start date cannot be a sunday,but the end date can be a sunday

**daddylonglegs** · 06-09-2011, 10:09 AM

OK, then by your definition Friday to Saturday =1, Friday to Monday = 2, is that right? so what should Friday to Sunday be? still 1? If so then the last formula I suggested should work for you, i.e.

=SUM(INT((WEEKDAY(C2-{2,3,4,5,6,7})+N2-C2)/7))-1