how do i round time down in excel?

[Iain]

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.

 

[Mike H]

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]

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.

 

[Bob Phillips]

Hi Mike.

As you can imagine, my head is about to implode thinking about this.

And mine, I failed to understand how the results came from the inputs
completely.

 

[Mike H]

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(A3>C2,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

 

[MartinW]

Hi Iain,

This is not an excel solution, merely a manager's solution.

Stop stuffing around with this and OUTSOURCE it.

There are many professionals who specialise in this sort
of thing, just do a google search for 'shift work rosters' or the like.

It will be much cheaper for your business and you will learn
some handy new talents.

Regards
Martin

 

[Iain]

Thanks Mike, its working a treat. If i get hung up again i will shout.

You have saved my hair (for now)

 

[Iain]

Martin

I have asked this question as this is a project i am doing and faced with
something that i didnt know the solution for. I am trying to teach myself
excel and also include vbasic.net, and can find information on "basic"
solutions but not the more complex ones. If it was for a business then i
would pay for this service, but as it is for my own personal use and i have
limited resources for this i have asked on here as that is what i understood
this to be for. It is merely a planning exercise for me to increase my
knowledge and gain a better understanding for when i do things in the
commercial world. The threads underneath mine are also asking for information
that could be construed as for "business use"

Thanks for your comment anyhow.

Regards

Iain