
1. Re: Calculating the number of work days between two dates not working
SteveMartino Sep 8, 2014 5:43 AM (in response to KatrinaRolfe_1)I would check out this custom function by the very talented Mr. Raybaudi.

2. Re: Calculating the number of work days between two dates not working
philmodjunk Sep 8, 2014 10:35 AM (in response to KatrinaRolfe_1)While I find the above method needlessly complex. It should produce consistent values. (I helped correct an error in an earlier version so have tested the calculation fairly extensively.
If you need to also allow for holiday closures, it's simpler just to use a related table of holidays that includes the standard days closed in order to get the count of how many work days exist for a given date range.

3. Re: Calculating the number of work days between two dates not working
KatrinaRolfe_1 Sep 8, 2014 7:39 PM (in response to KatrinaRolfe_1)Hi again and thanks for your replies.
Steve, the custom function you suggested is a little complex for me and I don't really need to include Holiday closures, as Phil also suggested.
I just want this function to work! I have tested and tried so many different things, I just can't get it to work if the difference between the two dates are more than four days. Up to four days, it works perfectly, but five or more days and it doesn't work.
Phil, I know you said it should work, but unfortunately it's not working. Is there something I have not done correctly?
Any help would be appreciated. If I can't get this exact calculation to work, I'm going to have to consider removing it, which would be really annoying seeing as how it's already been implemented, or I'm going to have to use a much simpler calculation that just calculates the number of days, including weekends, which then won't be strictly correct.
Thanks for you answers but I'm afraid I'm still needing additional help on this.

4. Re: Calculating the number of work days between two dates not working
schamblee Sep 8, 2014 9:27 PM (in response to KatrinaRolfe_1)You have an error in your formula: You have the dates switch in the second part of the formula as compared to the knowledge base. The formula from the knowledge base is not correct in my opinion, it is a day short. It counts from day 1 to 2 as a day instead of counting each date as a day. I would think that you would want to count each date as a day. If you want it to count each day just add + 1 to the end of the formula.
Your formula
5 * Int ( ( Estimated Lodgement Date  Actual Lodgement Date ) / 7 ) +Middle ("0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( Estimated Lodgement Date )  1 ) + DayOfWeek ( Actual Lodgement Date ) ; 1 )
Estimated Lodgement Date would = the End Date from the knowledge base
Actual Lodgement Date would = the Start Date from the knowledge base5 * Int ( ( Estimated Lodgement Date  Actual Lodgement Date ) / 7 ) +Middle ("0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( Actual Lodgement Date )  1 ) + DayOfWeek ( Estimated Lodgement Date ) ; 1 ) + 1
+1 is optional
knowledge base link http://help.filemaker.com/app/answers/detail/a_id/5281/~/calculatingnumberofweekdays(workdays)betweendates

5. Re: Calculating the number of work days between two dates not working
KatrinaRolfe_1 Sep 8, 2014 11:45 PM (in response to KatrinaRolfe_1)Hi S Chamblee,
Thankyou so much for finding the error, I now know that's where it was wrong and it's working perfectly now.
Thanks again!

6. Re: Calculating the number of work days between two dates not working
schamblee Sep 9, 2014 5:24 AM (in response to KatrinaRolfe_1)Actually the +1 doesn't work 100% of the time because it just adding a day, so if the date start on a weekend and ends on a weekend with no work days in between, it would show 1 day. I was trying to find a quick fix. After thinking about it, I realize my mistake. I'll try to find a new formula and post.
Raybaudi custom function on the above link is correct and is the best solution. You would need "Advance" to create custom functions.

7. Re: Calculating the number of work days between two dates not working
KatrinaRolfe_1 Sep 11, 2014 5:19 PM (in response to KatrinaRolfe_1)Hi everyone,
Just a follow on question regarding this formula. The way my layout is setup, the "Estimated Lodgement" date is filled out at the beginning of the year and throughout the year the "Actual Lodgement" date is filled out as each deadline is completed. Until it is though, that field remains empty. But this results in the Calculation field ("Days Overdue") ie. the number of days overdue, showing a number like 525533. Is there a way to just have it blank until the Actual Lodgement Date field is completed?
Thanks!
Katrina

8. Re: Calculating the number of work days between two dates not working
schamblee Sep 11, 2014 5:49 PM (in response to KatrinaRolfe_1)If(not IsEmpty(Actual Lodgement Date); 5 * Int ( ( enddate  startdate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( startdate )  1 ) + DayOfWeek ( enddate ) ; 1 ) ;"")