10 Replies Latest reply on Jul 15, 2016 9:49 AM by ddave

# Calculating Elapsed Time during business hours timeframe

Hello,

So I am trying to calculate total time taken for one job during company's business hours (8am-5pm, Monday-Friday). Now the real challenge for me is to consider only hours during business time and days frame when the ending time is on later date. I found this Calculating Elapsed Time When Ending Time on a Later Day | FileMaker   to calculate total time but I don't want to consider non-business hours and days(sat,sun).

P.S: I have Date started, Time started, Date Ended and Time Ended field values

• ###### 1. Re: Calculating Elapsed Time during business hours timeframe

There is several Custom Functions that you can use to calculate time that you can find on FileMaker Pro templates, downloads, & help

• ###### 2. Re: Calculating Elapsed Time during business hours timeframe

I would do something like this, though its psuedocode (look at the dayofweek function FileMaker Pro 15 Help )

if getasdate ( starttimestamp) <> getasdate (endtimestamp)

If dayofweek (starttimestamp) > dayofweek (endtimestamp)

\$wholedays = get as date ( endtimestamp) - get as date ( start timestamp) - 3 // - 3 cut weekend and start day

Else

\$wholedays = get as date ( endtimestamp) - get as date ( start timestamp) - 1 // - 1 to cut out just the start date

End If

\$day1 time = getastime (business close) - getastime (starttimestamp)

\$totaltime = (\$wholedays * hoursopen * 60minutes ) + \$day1time

Else

\$totaltime = getastime (business close) - getastime (endtimestamp)

End if

If I understand what you are looking to do. That will give you the total time. You don't have to do the 60 minutes if you don't need it broken down into minutes.

1 of 1 people found this helpful
• ###### 3. Re: Calculating Elapsed Time during business hours timeframe

I don't think I can use custom functions in Filemaker Pro though, will need advanced version.

• ###### 4. Re: Calculating Elapsed Time during business hours timeframe

This is a bit messy...but... the bit in bold italics below gives days worked between two dates, removing weekends.  My end result is in total hours but this can be tweaked for hours and minutes (and seconds)  of total time worked.

Working hours start at 8 and end at 17 (military time).  I hope this along with the other feedback helps.

• Set Field [ test::CurrentDay; DayOfWeek ( test::CurrentDate ) ]
• Set Field [ test::CurrentDayName; DayName ( test::CurrentDate ) ]
• Set Field [ test::StartDay; DayOfWeek ( test::StartDate ) ]
• Set Field [ test::StartDayName; DayName ( test::StartDate ) ]
• Set Field [ test::EndDay; DayOfWeek ( test::EndDate ) ]
• Set Field [ test::EndDayName; DayName ( test::EndDate ) ]
• Set Field [ test::daysBetween; (Day ( test::EndDate ) - Day ( test::StartDate )) +1 ]
• Set Variable [ \$\$Max; Value:test::daysBetween ]
• Set Variable [ \$\$LoopControl; Value:0 ]
• Set Variable [ \$\$WeekEnd; Value:0 ]
• Set Variable [ \$\$SDate; Value:test::StartDate ]
• Set Variable [ \$\$SecsInDay; Value:9*3600 ]
• Loop
• Exit Loop If [ Let ( \$\$LoopControl = 1 + \$\$LoopControl; \$\$LoopControl > \$\$Max) ]
• Set Field [ test::WeekendDayCount; Let (\$\$SDate = \$\$SDate + 1; Case ( DayOfWeek (\$\$SDate) = 1; Let ( \$\$WeekEnd = \$\$WeekEnd + 1; \$\$WeekEnd ); DayOfWeek (\$\$SDate) = 7 ; Let ( \$\$WeekEnd = \$\$WeekEnd + 1; \$\$WeekEnd ); Let ( \$\$WeekEnd = \$\$WeekEnd + 0; \$\$WeekEnd ))) ]
• End Loop
• Set Field [ test::TotalDays; test::daysBetween - test::WeekendDayCount ]
• Set Variable [ \$\$TotalDays; Value:test::daysBetween-test::WeekendDayCount ]
• Set Variable [ \$\$TotHours; Value:(17-test::StartTime) + (test::EndTime-8) + ((test::TotalDays-2)*9) ]

Ron Harris

1 of 1 people found this helpful
• ###### 5. Re: Calculating Elapsed Time during business hours timeframe

You can certainly USE them in FMPro, but you will need FMAdvanced to install them in your file. If you can find an appropriate CF, you could call on someone with FMAdvanced to install it for you. But if you are doing any serious development Advanced is worth the upgrade anyhow.

• ###### 6. Re: Calculating Elapsed Time during business hours timeframe

Mine wasn't a custom function. It was script step based. I just didn't have the time to write it out in full code.

• ###### 7. Re: Calculating Elapsed Time during business hours timeframe

with the help of sreese and ron.harris , this is the final solution which worked for me:

Let( [\$opening =  Time ( 8 ; 0 ; 0 );\$closing = Time ( 17 ; 0 ; 0 )];

If(date_start ≠ date_done;

If(DayOfWeek ( date_done ) > DayOfWeek ( date_start );

If( DayOfWeek ( date_done ) = DayOfWeek ( date_start )+1 ;Round((\$closing - time_start)/3600;3);Round ( (((date_done - date_start) * 32400)+time_end - time_start )/3600 ; 3 ));

Round ( (((date_done - date_start - 2) * 32400) + time_end - time_start)/3600 ; 3 )

);

If(time_end > \$closing;Round((\$closing - time_start)/3600;3);Round((time_end - time_start)/3600;3))

)

)

• ###### 8. Re: Calculating Elapsed Time during business hours timeframe

Awesome, I am glad you figured it out and I was able to help.

• ###### 9. Re: Calculating Elapsed Time during business hours timeframe

Thank you

• ###### 10. Re: Calculating Elapsed Time during business hours timeframe

Thank you