8 Replies Latest reply on Sep 11, 2014 5:49 PM by schamblee

# Calculating the number of work days between two dates not working

### Title

Calculating the number of work days between two dates not working

### Post

Hi,

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.

Katrina

• ###### 1. Re: Calculating the number of work days between two dates not working

I would check out this custom function by the very talented Mr. Raybaudi.

https://www.briandunning.com/cf/1182

• ###### 2. Re: Calculating the number of work days between two dates not working

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

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

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.

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 base

5 * 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

• ###### 5. Re: Calculating the number of work days between two dates not working

Hi S Chamblee,

Thank-you 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

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

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

If(not IsEmpty(Actual Lodgement Date);   5 * Int ( ( enddate - startdate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( startdate ) - 1 ) + DayOfWeek ( enddate ) ; 1 ) ;"")