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