Calculated timestamp based on shift patterns

Discussion created by cmskatz on May 1, 2018
Latest reply on Aug 31, 2018 by cmskatz

I am on a new project and still mapping a few things out. The requirement is relatively simple but haven't got a solution in my mind yet and know there are some keen minds here who just love problem solving!


I will have a simple job table in which a start date and time are defined (as a timestamp) along with the duration of the job rounded to the nearest full hour. I simply need to calculate the end date and time but need to take into account limited hours on certain days.


For instance Monday to Friday we have 24 hours a day midnight to midnight, Saturday we have 12 hours from 7am to 7pm, Sunday there are none. A job starts on a Thursday at 2pm and needs 58 hours to complete. How do I calculate finish date and time? Sunday overtime is possible and would be specified in advance which should then change the finish date and time of any jobs running over that weekend.


I am thinking a secondary table for the available hours by day but still not sure how to use this data in a calculation or cover the partial days. Only an indication to the nearest hour is needed which may make this easier?


I would estimate 250 jobs will exist in this table and the start dates and times will be changed regularly.


A nudge in the right direction would be much appreciated!


I am using the PC version of Filemaker 16 advanced.