Iain,

Maybe this
Tools|Options|Calculation
check the 'Iteration' box.

ETA staff arrive staff depart
01:00 00:30 06:30
02:25 06:30
03:00 06:30
08:00 07:30 13:30
04:00 13:30

the ETA are manually entered on column A starting in a2
Put this in B2 for the start time
=MROUND(A2-TIME(0,30,0),1/48)

and this in C2 for when that shift ends
=B2+TIME(6,0,0)

now enter these 2 formula in B3 and c3
=IF(A3C2,MROUND(A3-TIME(0,30,0),1/48),"")
=IF(B3<"",B3+TIME(6,0,0),C2)

Select them both and drag down and you should end up with a table like the
one above.



Mike
"Iain" wrote:

Hi Mike.

I have flight schedules and i am trying to work out an automated rosta,
according to ETA times for a/craft movements. If the first ETA is 14:37 I
need the staff in for 14:00, but if ETA is 14:57, i dont need them to start
until 14:30. For example, I have 2 types of service, "P" if the a/craft is
doing another flight, and "E" if the a/craft is down for the day. Staff get
a minimum of a 4 hours shift, so if there are ETA's of 14:37, 21:30, 22:00
and 01:15 and an ETD of 15:45 for the first arrival i need to run 2 shifts. 1
shift starting at 14:00 with an automatic finish time giving them 4 hours,
and the second shift starting at the half hour before the first ETA, and a
finish time allowing a maximum of 2 hours on each a/craft and a half hour
break. In the example above, they would start at 21:00 and finish at 04:00
allowing the 2 hours on each a/craft with a half hour break. I hope there
is enough information here, but if not please let me know and i will try to
explain more. As you can imagine, my head is about to implode thinking about
this.

Thanks in advance.

"Mike H" wrote:

Iain,

There isn't enough information i.e. what does this mean

i.e. ETA 14:37 staff start 14:00.

It doesn't tell us when the empoyee arrives all I think we can conclude is
that you estimate the employee will be late!!

How are we to get to finish time from the information you've provided?


Mike

"Iain" wrote:

I am trying to calculate start and finish times according to arrival times,
and staff need to be on site on the hour or half hour before ETA. How can
this be done please? i.e. ETA 14:37 staff start 14:00. This is done on a
sheet where i input ETA, and the start time is automatically given.