=NETWORKDAYS() for 6 working days (MO-SA)

[p33pm3]

hi all,

i'm new here. juz found out about dis forum thru search. been scrolling around thru the posts n i'd appreciate it if sum1 take sum time n look at a minor problem i'm facing.

i m living in a country dat quite a number of d population still work on Saturdays. dis causes a bit of a problem in using excel. For example; i'd like 2 find out how many working days from 1-Jan-08 (Cell:A1) to 24-Jun-08 (Cell:B2).

the function:

=NETWORKDAYS(A1+1,B1)

would result in 125 days from A1 to B1 including Mon-Fri & excluding Sat-Sun. whr as the actual results dat i "should" get is 150 days (according 2 my employer... )

Q1: how could i get a value with Sat as well using only functions?
Q2: is thr an option whr i could change in my O.S's regional settings or excel 2 let excel 'know' dat my country works on saturdays?

thx for reading this post! n more thx if u reply!


P/S: if i did missed out on previous posts dat related to this same problem, please point me in d rite direction

 

[Jon von der Heyden]

Welcome to the board!

This should work for you until Barry posts something better

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

Regards
Jon

 

[Richard Schollar]

An alternative approach is simply to calculate the number of days between the two dates and then deduct the number of Sundays from the total:

=B1-A1-INT((WEEKDAY($A$1-1)-$A$1+$B$1)/7)

 

[p33pm3]

To Jon:

ur formula of:

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

does indeed work, but it adds 1 day to the calculation (after i do manual count to reconfirm ).




To Richard:

ur formula of:

=B1-A1-INT((WEEKDAY($A$1-1)-$A$1+$B$1)/7)

works perfectly!


thx a lot to both of u for replying. it was a great help. and now i hv 2 breakdown the formula to understand throughly...