Newest functions Upload a function Leave a tip My account

There are 2,572 functions in this library.

CountWorkingDays ( From ; To )

This helps to find no of working days in a date range including the given dates.

  Average rating: 4.3 (27 votes) Log in to vote

Saigopal Das Mindfire Solutions http://www.mindfiresolutions.com/

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

Let ( [ To = GetAsDate ( To ) ; Fr = Case( Year ( To ) > Year ( GetAsDate ( From) ) ;Date ( 1 ; 1 ; Year ( To ) ); GetAsDate ( From) ) ; Days = Case ( DayOfWeek ( Fr ) = 1 and DayOfWeek ( To ) = 1 ;( ( WeekOfYear ( To-1 ) - WeekOfYear ( Fr ) +1 ) * 5 ) ; DayOfWeek ( Fr ) = 1 and DayOfWeek ( To ) ≠ 1 ;( ( WeekOfYear ( To -7 ) - WeekOfYear ( Fr ) +1 ) * 5 )+ ( DayOfWeek ( To )-1) ; DayOfWeek ( Fr ) ≠ 1 and DayOfWeek ( To ) = 1 ;( ( WeekOfYear ( To-1 ) - WeekOfYear ( Fr + 7 ) + 1) * 5) + ( 7 - DayOfWeek ( Fr )); ( ( WeekOfYear ( To-7 ) - WeekOfYear ( Fr + 7 ) +1 ) * 5) + ( 7 - DayOfWeek ( Fr ))+ ( DayOfWeek ( To )-1) ) ]; Days + Case( Year ( To ) > Year ( From ) ; CountWorkingDays ( From ; Date ( 12 ; 31 ; Year ( To ) - 1 ) ) ; 0 ) ) Click to copy

This function first calculated the no of week between the provided dates and then adds the working days of the current week of from and to dates.

If the from and to years are different , then it calculates the working days of a particular year and adds it recursively.

 

Comments

		Bruce, Redmond WA Sep 9, 2015
Not sure this is right: List( CountWorkingDays( get( currentDate); get( currentDate) + 1 ); CountWorkingDays( get( currentDate); get( currentDate) + 2 ); CountWorkingDays( get( currentDate); get( currentDate) + 3 ); CountWorkingDays( get( currentDate); get( currentDate) + 4 ); CountWorkingDays( get( currentDate); get( currentDate) + 5 ); CountWorkingDays( get( currentDate); get( currentDate) + 6 ); CountWorkingDays( get( currentDate); get( currentDate) + 7 ) ) Result: 2 3 4 3 4 5 6

		Bruce, Redmond WA Sep 10, 2015
To clarify: it looks like your function produces an error. As you step through an increasing span of days, sometimes the count of working days moves backwards.

		eos, Teg Sep 10, 2015
As mentioned in another context: this can be coded non-recursively, and a whole lot shorter: /* Signature: CountWorkingDays_eos ( From ; To ) Recursive: No */ Case ( To > From ; Let ( [ diff = To - From ; weeks = Div ( diff ; 7 ) ; days = Mod ( diff ; 7 ) ; firstWeekDay = To - days + 1 ; dayOfWeekFirstDay = DayOfWeek ( firstWeekDay ) ; dayList = MiddleValues ( "1¶2¶3¶4¶5¶6¶7¶1¶2¶3¶4¶5" ; dayOfWeekFirstDay ; days ) ; weekdays = ValueCount ( FilterValues ( "2¶3¶4¶5¶6" ; dayList ) ) ] ; weeks * 5 + weekDays ) )

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: