Hi All,

I am working on a spreadsheet that should work out how much it will cost to send an employee to a client, based on what time of day they go, and how long they are there for.

There are 3 different shifts,

AM (07:00 - 15:00)
PM (13:00 - 22:00)
ND (21:00 - 07:30)

I thikn the overlapping may cause problems but it is out of my control. Basically, the start time determines the rate charged, but if the employee is there longer than the finish time of that rate, they then start charging at the next rate.

For example, if a job was to take 5 hours, and the employee started at 12:00, they would charge the first 3 hours at the AM rate, and the next 2 hours at the PM rate.

Problem is, I am not too sure how to work this out using formulas... anyone able to offer anyhelp? The information I will have available is the required start time, and the actual duration, if that is of any help

Thanks

Patrick