Kim,
Your 5/7 calculation is going to return some error depending on when
in the week your start and end dates are. Sometimes it'll be exactly
right, sometimes slightly off. Below is a calculation developed by
Reinder Wolting which he presented as a custom function on the
Dunning site. Study it for a bit and you'll see the source of your
error and be able to apply the Wolting calc to your situation.
Bill Holt
// weekdays ( startDate ; endDate )
// Computes the numbers of weekdays between any two dates.
// Includes both end dates in the total.
( Int ( ( endDate - 6 ) / 7 ) - Int ( ( startDate - 6 ) / 7 ) ) * 5
+ // the number of weeks times 5
If ( Mod ( endDate - 6 ; 7 ) - 2 > 0 ; Mod ( endDate - 6 ; 7 ) - 2 ;
0 ) - // the number of weekdays of the last week
If ( Mod ( startDate - 6 ; 7 ) - 2 > 0 ; Mod ( startDate - 6 ; 7 ) -
2 ; 0 ) // the number of weekdays of the first week
"The strongest reason for the people to retain the right to keep and
bear arms is, as a last resort, to protect themselves against tyranny
in government." Thomas Jefferson
On Feb 20, 2008, at 2:42 PM, Kim Parris wrote:
Someone else made this calc for me and I thought it was working,
but now I realize that it is not. I have gone over it and can’t
figure out how to fix it. The purpose is to find out how many
hours they need to work on each business day to meet their target
hours, based on their anniversary ( start and end dates)
Start date = 3/1/2007
End date = 2/20/2008
Target Hours = 1840
Total hours = 1791.6
The answer should be there are 8 business days left so
(1840-1791.6) / 8 = 6.05. The current calc is coming up with
8.47. What am I missing?
If((Date(Month(start date), Day(start date), Year(start date) +
1)-1)=End date, 0 ,
If(target hours - total hours > 0 , (target hours - total hours) /
((365+ If((Date(Month(start date), Day(start date), Year(start
date) +1)- start date + 1) = 366,1,0) - (End date - start date +1))
*5/7),0))
Thanks for your help!
Kim