@HourlyWorkEnd ( workStartTimestamp ; scheduledHours )

A customizable function to precisely calculate the end date and time of scheduled hourly work, excluding lunches, weekends, and holidays.

Average rating: 5.0 (1 vote) Log in to vote

Alex Zueiv - Show more from this author
Tashkent
https://github.com/zueiv

Sample input:

@HourlyWorkEnd ( "2022+12+30 3:45pm" ; 6.5 )

Sample output:

1/3/2023 2:15 PM

or

1/2/2023 1:15 PM
(ignoring lunches and holidays)

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

/**
* @HourlyWorkEnd ( workStartTimestamp ; scheduledHours )
*
* A customizable function to precisely calculate the end date and time of
* scheduled hourly work, excluding lunches, weekends, and holidays.
*
* Example: @HourlyWorkEnd ( "2022+12+30 3:45pm" ; 6.5 )
*
* Result: 1/3/2023 2:15 PM or 1/2/2023 1:15 PM (ignoring lunches and holidays)
*
* Created 2022-04-01 by Alex Zueiv - https://www.briandunning.com/cf/2584
**/

Let ( [

DayStartTime = GetAsTime ( "8:00" ) ;
DayEndTime = GetAsTime ( "17:00" ) ;

LunchStartTime = GetAsTime ( "12:00" ) ;
LunchDurationHours = 1 ; // set to 0 to ignore lunch breaks

// To exclude holidays, create a table with a date field, populate it with
// holiday dates several years in advance, and enter the fully qualified
// field name here, for example:
//
// HolidayTableDateFieldName = "Holidays::Date" ;

HolidayTableDateFieldName = "" ;

/********** CUSTOMIZE THE SETTINGS ABOVE FOR YOUR ORGANIZATION ***********/


WorkStartTimestamp = GetAsTimestamp ( workStartTimestamp ) ;
WorkDurationTime = GetAsTime ( scheduledHours * 3600 ) ;
LunchDurationTime = GetAsTime ( LunchDurationHours * 3600 ) ;
DayDurationTime = DayEndTime - DayStartTime - LunchDurationTime ;
HolidayTableError = If ( HolidayTableDateFieldName ≠ "" ;
	EvaluationError ( GetField ( HolidayTableDateFieldName ) )
) ;

WorkStartTime = GetAsTime ( WorkStartTimestamp ) ;
WorkStartTimestamp = Timestamp (
	GetAsDate ( WorkStartTimestamp ) + ( WorkStartTime > DayEndTime ) ;
	If ( WorkStartTime < DayStartTime or DayEndTime < WorkStartTime ; DayStartTime ; WorkStartTime )
) ;
WorkStartDate = GetAsDate ( WorkStartTimestamp ) ;
WorkStartTime = GetAsTime ( WorkStartTimestamp ) ;

FirstLunchTime = If ( WorkStartTime ≤ LunchStartTime and LunchStartTime < WorkStartTime + WorkDurationTime ; LunchDurationTime ) ;
FirstDayTime = Min ( WorkDurationTime ; DayEndTime - WorkStartTime - FirstLunchTime ) ;
LastDayTime = GetAsTime ( Mod ( WorkDurationTime - FirstDayTime ; DayDurationTime ) ) ;
LastLunchTime = If ( DayStartTime + LastDayTime > LunchStartTime ; LunchDurationTime ) ;

WorkdayCount = Div ( WorkDurationTime - FirstDayTime ; DayDurationTime ) + ( LastDayTime > 0 ) ;
WorkEndDate = SetRecursion ( While ( [
		htf = Substitute ( Quote ( HolidayTableDateFieldName ) ; "::" ; "\"¶\"" ) ;
		x = "" ; hc = 0
	] ;
		x ≠ hc ;
	[
		x = hc ;

		wc = WorkdayCount + hc ;
		ed = WorkStartDate
			+ Int ( wc / 5 ) * 7
			+ Middle (
				"12345012340123601256014560345623456" ;
				( DayOfWeek ( WorkStartDate ) - 1 ) * 5 + Mod ( wc ; 5 ) + 1 ; 1
			) ;
			// https://support.claris.com/s/?language=en_US&q=12345012340123601256014560345623456

		hc = If ( HolidayTableDateFieldName = "" or HolidayTableError ; 0 ;
			ExecuteSQL ( "
				SELECT COUNT( DISTINCT "& GetValue ( htf ; 2 ) &")
				FROM "& GetValue ( htf ; 1 ) &"
				WHERE "& GetValue ( htf ; 2 ) &" BETWEEN ? AND ?
				AND NOT DAYOFWEEK("& GetValue ( htf ; 2 ) &") IN (1,7)
				" ; "" ; "" ; WorkStartDate ; ed
			)
		)
	] ;
		If ( hc = "?" ; "?" ; ed )
) ; 365 )

] ;

Case (

	HolidayTableError ; "Holiday table error " & HolidayTableError ;
	WorkEndDate = "?" ; "Holiday date field error" ;

	Timestamp (
		WorkEndDate ;
		Case (
			LastDayTime ; DayStartTime + LastDayTime + LastLunchTime ;
			WorkdayCount ; DayEndTime ;
			WorkStartTime + FirstDayTime + FirstLunchTime
		)
	)

) )

To exclude holidays, create a table with a date field, populate it with holiday dates several years in advance, and enter the fully qualified field name in the leading section of the CF code.

Comments

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

@HourlyWorkEnd ( workStartTimestamp ; scheduledHours )

Comments

Support this website.

Support this website.