CalcDaysBetween ( startDate ; endDate ; numDays ; $$HolidayList ; countWeekends ; returnNeg )
Calculates number of days between dates, accounts for holidays and weekends
Average rating: 3.9 (61 votes) Log in to vote
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
Calculates the number of days between two dates. Will not count holidays or weekends if passed a variable with a list of holidays.
When you initially call the function, pass a start and end date. Use 0 for numDays - this gets incremented each time through the loop. Pass a pre-populated list of holidays from a variable. CountWeekends - pass 'true' if you want to count weekends and holidays in numDays, pass 'false' if you don't. For returnNeg, pass "" when calling the function, if the startDate is after the endDate then the function will return a negative number for numDays.
|Roger, New Zealand
May 7, 2009
|What do the other four "param's" do? I haven't looked at the code $$Holidays is obvious, but the others?|
|Shan Younker, Medford, OR
May 12, 2009
|I've clarified the variable descriptions a bit|
|Alan Stirling, London UK
Jul 13, 2009
|Thank you for developing this custom function - I have found it to be very useful ...
But I need to report a bug!
When working with negative results, the 'ReturnNeg' variable is not consistently 'True'. I have tracked this down to a missing entry (shown in caps and quotes) in the formula - see below;
//first time through function determine if negative numDays should be returned.
IsEmpty ( returnNeg );
endDate > startDate ; False;
startDate > endDate;
True ) "RETURNNEG" );
Perhaps once you have checked this, you could report back here if you concur.
Thanks again - Alan
|Jonn Howell, DataExperience, Inc.
Aug 8, 2010
|I like this custom function. I found some bugs for end case scenarios. I modified it with corrections, and removed the negative numDays option for myself. Please contact me, Shan, if you would like to see my work.
|Michael, Brisbane Australia
Aug 11, 2010
Are you able to share the corrections/modifications here?
|Sean Newton, Durban RSA
Mar 18, 2011
I'm battling to get this one to work, would you share your corrections?
|Sean Newton, Durban RSA
Mar 23, 2011
I am using your other cf - calcEndDate - Thank you, it is very useful. This one does not work though.
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.