number or working days
No, don't do that. If the start date is a weekend it counts one to many.
Test for it
=(C16-WEEKDAY(C16,2)+WEEKDAY(B16,2)-B16)/7*5-MIN(5,WEEKDAY(B16,2))+MIN(5,WEEKDAY(C16,2))+(WEEKD AY(B16,2)<5)
Personally, I would use
=C16-B16+1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B16&":"&C16)),2)5))
--
__________________________________
HTH
Bob
"Rick Rothstein" wrote in message
...
The difference is that between subtracting two numbers and counting
between two numbers (the difference between 1 and 5 is 4, but there are 5
numbers to be counted); or, in other words, the difference is in whether
you count both end days or not. Anyway, the fix is easy... just add 1 to
Jacob's formula (that is, put +1 at the very end of what you are now
using.
--
Rick (MVP - Excel)
"Gibbyky2" wrote in message
...
Jacob kindly gave me the following formula for working out number of
working
days between 2 dates. it is exactly what i was looking for but it seems
to
calculate a day less
formula
=(C16-WEEKDAY(C16,2)+WEEKDAY(B16,2)-B16)/7*5-MIN(5,WEEKDAY(B16,2))+MIN(5,WEEKDAY(C16,2))
where c16 is 17th july 2009 and B16 is 13th july 2009
the formula works out 4 days but it should be 5 days
i am also looking to calculate the number of days (in this case 5) by 7.4
many thanks
|