Calculating the number of work days between two dates not working
I have found this calculation on the FileMaker Knowledge Base and have inserted it into my database:
5 * Int ( ( Estimated Lodgement Date - Actual Lodgement Date ) / 7 ) +Middle ("0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( Estimated Lodgement Date ) - 1 ) + DayOfWeek ( Actual Lodgement Date ) ; 1 )
I use two date fields in the calculation: "Estimated Lodgement Date" and "Actual Lodgement Date" and apply this calculation to a field called "Days Overdue".
This should calculate the number of WORK days between the two dates and it does seem to work, but only if the number of days between the two dates is four working days. Once the "Actual Lodgement Date" is more than five working days after the "Estimated Lodgement Date", the result of the calculation doesn't seem to be correct and it starts returning unusual numbers.
For instance, for one record, the "Estimated Lodgement Date" is 5 August 2014 and the "Actual Lodgement Date" is 12 August 2014. This should be displayed as "5" (working days) in the "Days Overdue" field. However it returns "-5" as the result. Why is it displaying a negative number?
And playing around with the numbers, if the "Actual Lodgement Date" is changed to 13 August 2014, the result is "-4" when it should be "6" (working days).
I don't really understand what is wrong here? Calculations are not my forte and I thought I had lucked upon a really good calculation provide by FM, so now that it's wrong I don't know how to fix it. Can anyone point me in the right direction? I need this calculation to work, as sometimes the days between "Estimated" and "Actual" can be 10 or more days.
Thanks in advance,