Brian Dunning's FileMaker Custom Functions

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
Shan Younker
none

Share on Facebook Share on Twitter

  Sample input:
CalcDaysBetween ( 7/1/2009 , 7/9/2009, 0, $$Holidays, False, "" )
  Sample output:
5

  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.

 

Comments

Roger   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   Shan Younker, Medford, OR
May 12, 2009
I've clarified the variable descriptions a bit
 
Alan Stirling   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   Jonn Howell, Los Angeles, CA
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   Michael, Brisbane Australia
Aug 11, 2010
Hi Jonn,

Are you able to share the corrections/modifications here?
 
Sean Newton   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   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.

Under construction. Email me your wish list for improvements.