Shopping Cart

Free Newsletter
Signup

Contact

FileMaker is a registered trademark of FileMaker, Inc. in the U.S. and other countries.

FileMaker Pro Custom Functions

List | Show Random | Upload | Add This to Your Site

CalcDaysBetween ( startDate ; endDate ; numDays ; $$HolidayList ; countWeekends ; returnNeg )

Rate this function:

Average rating: 3.1 (21 votes)
Discuss this Custom Function

Shan Younker, Shan Younker
none

Calculates number of days between dates, accounts for holidays and weekends

Sample Input:

CalcDaysBetween ( 7/1/2009 , 7/9/2009, 0, $$Holidays, False, "" )

Sample Output:

/* 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 ) )
Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Click here to copy To Clip Manager if you have myFMbutler's Clip Manager installed

Description:

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.

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

This is my Custom Function and I want to edit it

Discuss:

5 most recent comments | Show all 7 comments

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

Alan Stirling, London UK
July 13, 2009 3:09pm

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

Jonn Howell, Los Angeles, CA
August 08, 2010 3:31pm

Hi Jonn,

Are you able to share the corrections/modifications here?

Michael, Brisbane Australia
August 11, 2010 4:13pm

Hi Jonn,

I'm battling to get this one to work, would you share your corrections?

Sean Newton, Durban RSA
March 18, 2011 1:04am

Hi Shan,

I am using your other cf - calcEndDate - Thank you, it is very useful. This one does not work though.

Sean Newton, Durban RSA
March 23, 2011 4:48am

Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:

Download the Custom Function Dashboard Widget for OS X
Keep all the latest Custom Functions right at your fingertips!

Newest Custom Functions:

1.	indonesian_date(date)
	(Mon, Feb 06, 7:14pm)
2.	phpArrayValue ( array ; key ; pos )
	(Fri, Jan 27, 1:49pm)
3.	solfm_timeToMinutes (theTime;roundSec)
	(Fri, Jan 20, 6:26am)
4.	filterLines(filterField;filterValue;Result)
	(Sat, Jan 14, 2:20pm)
5.	getMaxValue ( theList )
	(Thu, Jan 12, 1:06pm)
6.	MiddleWordsIncPunct ( text ; startingWord ; numberOfWords )
	(Sat, Jan 07, 9:16am)
7.	WindowInfo
	(Fri, Jan 06, 12:39pm)
8.	CenterWindow in Window vert horiz (demension)
	(Fri, Jan 06, 12:25pm)


			[ Close Window ]
	The class that teaches you how to successfully sell your FileMaker Pro solution as a professional shrinkwrapped software application.
	Let's Meet in Person Sunday, August 13 2006, Orlando FL Courtyard Orlando International Drive
	Don't miss this most important day of the Developer Conference. Please click here for complete details and online registration.

FileMaker Pro Custom Functions

Description:

Discuss:

Let's Meet in Person