Thank you.  Your calc gave me the foundation I needed to get where I
needed to go.

Kim 

 

From: FileMaker Pro Discussions [mailto:[EMAIL PROTECTED]
On Behalf Of Bill Holt
Sent: Thursday, February 21, 2008 6:13 AM
To: [email protected]
Subject: Re: Need help with a calc!

 

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

 

Reply via email to