Glad to help, Kim, but I have to point out that it's not my calc ... Reinder Wolting presented it as a custom function on the Dunning site.

   Bill Holt



"I like when the enemy shoots at me; then I know where the bastards are and can kill them." George Patton


On Feb 22, 2008, at 8:31 PM, Kim Parris wrote:

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

From: FileMaker Pro Discussions [mailto:FMPRO- [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