date calculation

[Ben Ball]

Hi All,

 

I am trying to perform a date calc based on a certian situation.

 

I have the following fields start date, start time, finish date, finish time, hours

 

so.. I have the hours field with 18.5 hours filled in.

 

I select my start date and start time.

 

But I want to be able to calculate the finish date and finish time automatically because I know the hours to be added.

 

The problem also to be added..

 

The day/shift only has a certain amount of hours to be allocated that is 08:00hrs  between 6:00 and 14:00

 

so.. if the start date is say 24/02/14 the start time is 14:00 and the hours is 18.5

 

I need the finish date to be 26/02/14 and the finish time to be 07:30

 

so to simplify

 

Hours: 18.5

shift start: 06:00

shift end   14:00

shift hours per day: 8.0

 

start date: 24/02/14

start time: 13:30

 

finish date: ? (need this to calculate based on the info above)

finish time ? (need this to calculate based on the info above)

 

TIA

[comment]

so.. if the start date is say 24/02/14 the start time is 14:00 and the hours is 18.5

 

I need the finish date to be 26/02/14 and the finish time to be 09:00

 

Are you sure about this? If the 18.5 hours started at 14:00, then on the first day (24/2) 0.5 hour elapsed until the end of the shift at 14:30. This leaves 18 hours to go. On the second day (25/2). a full shift takes another 8.5 hours off, leaving 9.5 hours to go. On the third day (26/2) another shift of 8.5 hours leaves a remainder of 1 hour. This brings us to 27/2 at 7:00.

[Ben Ball]

OK

 

18.5 hrs

 

shift is from 6 until 2:00pm (14:00) - 8 hrs total

 

job starts at 13:30 on 24/2/14 (00:30hrs left on shift)

all day on 25/2/14 (8hrs)

all day on 26/2/14 (8 hrs)

finish on 27/2/14 at 08:00

 

0.5+8+8+2=18.5 (inbetween the both of us.. at a finish time of 8:00 am)

 

sorry about that..

 

I just can't get my head around this calculation... using the date/time function commands

[comment]

Sorry, it still doesn't come together for me:

 

shift start: 06:00

shift end   14:30

shift hours per day: 8.5

 

all day on 25/2/14 (8hrs)

all day on 26/2/14 (8 hrs)

 

You can't do a calculation in Filemaker before you can do it on paper.

[Ben Ball]

Well of course I can do it on paper.. Sorry... Its 8 hours per day.. I did edit last post.. but you must have viewed it before I changed it

 

I don't know how to use this same calc and make use of date and time fields... I need the date field and time field to change..

 

related to the calc that I can do on paper

[comment]

shift is from 6 until 2:00pm (14:00) - 8 hrs total

 

job starts at 14:00 on 24/2/14 (0.5hrs)

 

This is getting quite ridiculous. Please edit ALL your posts and check your data again for consistency. You cannot have 0.5 hours on the first day if you start at the exact time when the shift ends!

[Ben Ball]

Again, apologies.. I am trying to do 20 things a once... I did'nt take into account lunch..

 

basically.. I will have 1 field with the amount of working hours calculated.. its 18:30 hrs

 

basically I need

 

a date field to calculate a day based on a start time and a finish time in that day.

 

so 1 day comprises of 8 hours from a start point and an end point.

 

 

using 24 clock. for example

(shift finish time(14:00)  -  Job start time (13:30)) + (24:00 - shift finish time (14:00)) + Shift Start time (06:00)) + working hours (18:30) (so at this point I need to check how many hours are left... in this case its 18:30 - 8:30)

but where does it finish.. it could go on and on...

 

If I am doing this manually.. I calculate in my head

start date (24.02.14) Start time (13:30) shift finish time is 14:00 so that leaves 00:30

the next shift start date is a week day (24.02.14) start time is 06:00 finish time is 14:00 = 08:00 (18:30 - 08:00 + 00:30 = 10:00 left)

the next shift start date is a week day (25.02.14) start time is 06:00 finish time is 14:00 = 08:00 (10:00 - 08:00 = 02:00)

the next shift start date is a week day (26.02.14) start time is 06:00 finish time is 14:00 = this leaves 02:00 hrs

therefore: the end time is 08:00 on 27.02.14 (02:00 hrs added to the shift start time of 06:00)

[comment]

I am not sure what was wrong with your first example, except the result that you have reached. We seem to have the same problem with this new example, because if the job duration is 18.5 hours, then I believe that result should be 25.02.2014 at 8:00.

 

I'll let someone else take this, because it's taking too much of my time and energy.

[eos]

Try this one:

 

Let ( [

sampleD = Date ( 2 ; 22 ; 2014 ) ;
sampleT = Time ( 13 ; 30 ; 00 ) ;

toDistribute = Time ( 18 ; 30 ; 00 ) ; 

shiftBegin = Time ( 6 ; 00 ; 00 ) ;
shiftEnd = Time ( 14 ; 00 ; 00 ) ;
shiftDuration = shiftEnd - shiftBegin ;

firstDayHours = shiftEnd - sampleT ;
rest = toDistribute - firstDayHours ;
restDayCount = Div ( rest ; shiftDuration ) ;
lastDayHours = GetAsTime ( Mod ( rest ; shiftDuration ) ) ;

hasLastHours = GetAsBoolean ( lastDayHours ) ;

finaldate = sampleD + restDayCount + hasLastHours ;
finalTime = Case ( hasLastHours ; shiftBegin + lastDayHours ; shiftEnd )
] ;
Timestamp ( finalDate ; finalTime ) 
)

For a starting point of 22.02.2014, 13:30 (used as sample input), this will give a result of 25.02.2014, 8:00 (as comment correctly pointed out).

[Ben Ball]

comment.. apologies for the confusion.

 

eos, that looks like it.. I will try it and let you know.. thanks very much.

[Ben Ball]

This works perfectly.. I have attached a sample of this working.. thank you OES.. Outstanding.

This works perfectly.. I have attached a sample of this working.. thank you.. Outstanding.

date_calc.zip