ResourceHours ( JobStartTimestamp ; JobEndTimestamp ; WorkStartTime ; WorkEndTime ; HolidayList )

Works out resource hours for resource planning (Mo - Fr). It also takes a Holiday list.

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

Software developer
Thermogreen
http://www.thermogreen.ch

Sample input:

ResourceHours(13.8.08 9:00;20.8.08 15:00;7:00;17:00;"14.8.08¶15.8.08")

Sample output:

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

Let([
StartTS = JobStartTimestamp;
EndTS = JobEndTimestamp;
StartD = GetAsDate(JobStartTimestamp);
EndD =GetAsDate(JobEndTimestamp);
StartT = GetAsTime(JobStartTimestamp);
EndT = GetAsTime(JobEndTimestamp);
LastJobDay = If(StartD = EndD;1;0);
WorkSTime = WorkStartTime;
WorkETime = WorkEndTime
];

/* Make sure no empty parameters */
If( StartTS = "" or EndTS = "" or WorkSTime = "" or WorkETime = ""; 0 ;

/* No reverse allowed! StartDate has to be bigger than EndDate */
If( StartTS > EndTS or WorkSTime > WorkETime ; 0 ;

/* We put the whole calc in another "Let" so we can create a number instead of time. See at the end. */

Let([
calc = 

/* Core Calc */
Case(
/* Filter Weekends */
DayOfWeek (StartD) = 1 or DayOfWeek (StartD) = 7 ; 0;

/* Filter Holidays */
FilterValues(StartD;HolidayList)>0 ; 0;

/* If JobStartTime is later than WorkStartTime and its the last JobDay then Calculate JobEndTime - JobStartTime */
StartT > WorkSTime and LastJobDay = 1 ; If(EndT > WorkETime;WorkETime;EndT) - StartT;

/* If JobStartTime is later than WorkStartTime and its NOT the last JobDay then Calculate JobStartTime - WorkEndTime */
StartT > WorkSTime and LastJobDay = 0 ; WorkETime - If(StartT > WorkETime;WorkETime;StartT);

/* If JobStartTime is equal or earlier than WorkStartTime and its the last JobDay then Calculate JobEndTime - WorkStartTime */
StartT ≤ WorkSTime and LastJobDay = 1 ; If(EndT > WorkSTime; If(EndT < WorkETime;EndT;WorkETime) - If(StartT < WorkSTime;WorkSTime;StartT));

/* If JobStartTime is equal or earlier than WorkStartTime and its NOT the last JobDay then Calculate WorkEndTime - WorkStartTime */
StartT ≤ WorkSTime and LastJobDay = 0 ; WorkETime - WorkSTime;
0)


];

/* Change the whole Calculation to Decimal Time, so 1:00:00 becomes 1 in a number */
Round(Hour(calc) + (((Minute(calc) * 100) / 60) / 100); 2)
)

/* Add the Hours recursive */
+

/* Then run this until LastJobDay = 1 */
Case(LastJobDay = 0;ResourceHours(Timestamp(StartD + 1;WorkSTime);EndTS;WorkSTime;WorkETime;HolidayList))

)))

This function can be used for resource calculating. It calculates hours between two Timestamps and does a lot more. It will take your Work hours and only count the hours between those. Also your holidays can be excluded by a list of holidays. The list need to be Proper Dates e.g. "01.08.2008" and not "1.8.08". There is a CF for that.
Weekend are excluded also, this can be modified quite easily.
It outputs a Dezimal number, this also can be changed by a simple modification to the custom function.
Error checking is also implemented. So if an End Date is before a Start Date, this will be ignored.

Comments

		Vince, New England Sep 10, 2013
This fella doesn't seem to work, it always returns a "0", even the example. Perhaps it needs to be rewritten for the newer version of FileMaker.

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

ResourceHours ( JobStartTimestamp ; JobEndTimestamp ; WorkStartTime ; WorkEndTime ; HolidayList )

Comments

Support this website.

Support this website.