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.
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.
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
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.
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.
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?
If(not IsEmpty(Actual Lodgement Date); 5 * Int ( ( enddate - startdate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( startdate ) - 1 ) + DayOfWeek ( enddate ) ; 1 ) ;"")