Calculating Costs over varying shifts

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

This might be a good starting point :
http://cpearson.com/excel/pricing.htm
and search for "progressive pricing"

This can be done but it depends on what information you have.

Do you have start date and end date or just a start time and end time.

I see you have a night shift, will you have tasks that start one day and end the next? What's the longest stint you might have, e.g. could it be 10:00 one day to 06:00 the next?