Cost split into different periods

[pdhawan]

Hi All

I have attached a sample spreadsheet for the problem I am having.

I have a list of people who worked in different Food and Bveraage outlets (cost centres) with the number of hours worked and the amount paid for that work. The data also includes their start and end date and time. The business runs 24/7. There are 4 meal periods - Breakfast (from 5am to 10am), Lunch (from 10am to 3 pm), Dinner from (3pm to 11pm) and Supper from (11pm to next day 5am). A business day runs from 5am to 5am. So for example, if a person started work on 26th March at 11pm and finished at 27th March 7am, he or she would have worked 6 hours supper on the 26th (11pm to 5 am) and 2 hours breakfast on the 27th (5am to 7am).

I need to use the data on the sample spreadsheet and find out how much money was paid in a day for each meal period for each cost centre. Hence, if I need to find out this information for one day (say 26th March), I need time sheets for 3 days (25th to 27th) coz of people working over 2 days and each day being from 5am to 5pm.

This is what I tried. Based on amount paid and hours worked, I got the hourly rate. I created a separate column for each hour for the 3 days. Then using greater than (>), less than (<) formula I split this hourly rate between start and end time. Then using sumif I got the totals for each meal period for a day. The problem I am having is that for some people the difference between start and end time is not same as hours worked or the same person is working in 5 different cost centres for one hour each but start and end time difference is 5 hours for each cost centre (Row 141 to 146).

I was hoping if you have any other ideas to work this out.

Thanks a lot in advance.