CountDays ( from ; to ; dayPattern )

Count days for a date range

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

leinven - Show more from this author

Sample input:

CountDays(Date(8; 1; 2015); Date(8; 15; 2015); "2¶3¶4¶5¶6")

Sample output:

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

Let(
	[
		days = to-from;
		fd = DayOfWeek(from);
		daysA = 7-fd;
		daysB = Floor((days-daysA)/7)*7;
		daysC = days-daysA-daysB;
		listA = Case(
			daysA=0;	"";
			daysA=1;	"7";
			daysA=2;	"7¶6";
			daysA=3;	"7¶6¶5";
			daysA=4;	"7¶6¶5¶4";
			daysA=5;	"7¶6¶5¶4¶3";
			daysA=6;	"7¶6¶5¶4¶3¶2"
		);
		listC = Case(
			daysC=1;	"1";
			daysC=2;	"1¶2";
			daysC=3;	"1¶2¶3";
			daysC=4;	"1¶2¶3¶4";
			daysC=5;	"1¶2¶3¶4¶5";
			daysC=6;	"1¶2¶3¶4¶5¶6";
		);
		countA = ValueCount(FilterValues(listA; dayPattern));
		countB = daysB/7*ValueCount(dayPattern);
		countC = ValueCount(FilterValues(listC; dayPattern));
		counts = countA+countB+countC
	];
	//daysA & " " & daysB & " " & daysC & " : " & countA & " " & countB & " " & CountC
	counts
)

I use it count work days for a date range, hope it helps.

Comments

		Robert Jackson, Scotland Aug 28, 2015
Wow - this is an amazingly helpful custom function!!!!!! Thanks so much for sharing.

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

CountDays ( from ; to ; dayPattern )

Comments

Support this website.

Support this website.