calculate number of working days
 

have 2 inputs, start and end date. i would like to calculate number of
weekdays (have manually entered my desired results below).
have googled the last hour and cannot find the formula i need. btw i
will be sending this spreadsheet to people without the analysis
toolpack so cannot use networkdays function.


start date end date # working days
Sun 01 May Sun 01 May 0
Sun 01 May Mon 02 May 1
Sun 01 May Tue 03 May 2
Sun 01 May Wed 04 May 3
Sun 01 May Thu 05 May 4
Sun 01 May Fri 06 May 5
Sun 01 May Sat 07 May 5
Sun 01 May Sun 08 May 5
Sun 01 May Mon 09 May 6
Sun 01 May Tue 10 May 7
Sun 01 May Wed 11 May 8
Sun 01 May Thu 12 May 9
Sun 01 May Fri 13 May 10
Sun 01 May Sat 14 May 10
Sun 01 May Sun 15 May 10
Sun 01 May Mon 16 May 11
Sun 01 May Tue 17 May 12
Sun 01 May Wed 18 May 13
Sun 01 May Thu 19 May 14
Sun 01 May Fri 20 May 15
Sun 01 May Sat 21 May 16
Sun 01 May Sun 22 May 16

Here is a formula that manages holidays as well

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1
-{2;3;4;5;6})-MIN(end_date,start_date)+8)/7))-SUMPRODUCT(ISNUMBER(MATCH(WEEK
DAY(holidays),{2;3;4;5;6},0))*(holidays=MIN(end_d ate,start_date))*(holidays
<=MAX(end_date,start_date)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"philc" wrote in message
oups.com...
have 2 inputs, start and end date. i would like to calculate number of
weekdays (have manually entered my desired results below).
have googled the last hour and cannot find the formula i need. btw i
will be sending this spreadsheet to people without the analysis
toolpack so cannot use networkdays function.


start date end date # working days
Sun 01 May Sun 01 May 0
Sun 01 May Mon 02 May 1
Sun 01 May Tue 03 May 2
Sun 01 May Wed 04 May 3
Sun 01 May Thu 05 May 4
Sun 01 May Fri 06 May 5
Sun 01 May Sat 07 May 5
Sun 01 May Sun 08 May 5
Sun 01 May Mon 09 May 6
Sun 01 May Tue 10 May 7
Sun 01 May Wed 11 May 8
Sun 01 May Thu 12 May 9
Sun 01 May Fri 13 May 10
Sun 01 May Sat 14 May 10
Sun 01 May Sun 15 May 10
Sun 01 May Mon 16 May 11
Sun 01 May Tue 17 May 12
Sun 01 May Wed 18 May 13
Sun 01 May Thu 19 May 14
Sun 01 May Fri 20 May 15
Sun 01 May Sat 21 May 16
Sun 01 May Sun 22 May 16

[philc] scrive in
http://www.google.it/groups?threadm=...oglegroups.com
have 2 inputs, start and end date. i would like to calculate number of
weekdays (have manually entered my desired results below).
have googled the last hour and cannot find the formula i need. btw i
will be sending this spreadsheet to people without the analysis
toolpack so cannot use networkdays function.


try this:
http://www.prodomosua.it/zips/delledate.xls
.f
--
fernando cinquegrani
Microsoft MVP
http://www.prodomosua.it