Newest functions Upload a function Leave a tip My account

There are 2,577 functions in this library.

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

Shan Younker Shan Younker none

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/* calculates number of days between 2 dates; parameter for counting weekends and holidays Variables -startDate, beginning date to start calculations from -endDate, ending date for function -numDays, serves as a counter for the total number of days; pass the function a 0 to start with; the function will increment this counter each time through the loop and return the final value -holidayList, global with all holidays, must be populated before calling function -countWeekends, true - weekends and holidays will be counted in numDays -returnNeg, return negative number if end date is before start date, pass empty value "" for first pass through function, the function will handle this parameter from there -these are only used within the function -tempEndDate, used only within the function -exitFunction, determines when to exit function and return result, only used within the function */ Let ( [ // increment or decrement endDate accordingly tempEndDate = Case ( endDate > startDate; endDate - 1; startDate > endDate; endDate + 1 ); //first time through function determine if negative numDays should be returned. returnNeg = Case ( IsEmpty ( returnNeg ); Case ( endDate > startDate ; False; startDate > endDate; True )); //add one day to numDays or flag to exit function numDays = Case ( startDate = endDate; 0; //no count, start and end are same countWeekends; endDate - startDate; //count all days, including weekends and holidays DayOfWeek ( tempEndDate ) = 1 or DayOfWeek ( tempEndDate ) = 7 or ValueCount ( FilterValues ( tempEndDate; HolidayList )) > 0 ; numDays; //no count, weekend or holidays numDays + 1 ) ; //calc function exit exitFunction = Case ( startDate = endDate ; True ; startDate = tempEndDate ; True ; countWeekends; True; False ) ] ; Case ( exitFunction; If ( returnNeg ; 0 - numDays ; numDays ); CalcDaysBetween ( startDate; tempEndDate; numDays; holidayList; countWeekends; returnNeg ) //call function again as necessary ) ) Click to copy

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.

 

Comments

		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. returnNeg = Case ( IsEmpty ( returnNeg ); Case ( 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. Jonn jonn@dataexperience.com

		Michael, Brisbane Australia Aug 11, 2010
Hi Jonn, Are you able to share the corrections/modifications here?

		Sean Newton, Durban RSA Mar 18, 2011
Hi Jonn, I'm battling to get this one to work, would you share your corrections?

		Sean Newton, Durban RSA Mar 23, 2011
Hi Shan, I am using your other cf - calcEndDate - Thank you, it is very useful. This one does not work though.

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 21 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: