Work shifts

[gibson2503]

I have a worksheet in Excel 2007 showing the different tours (shifts) available to a particular department in my organization. The first column is the tour number, second column shows the start and end times of the tour (example 01:00P-09:30P), third column shows the number of paid hours in that tour after taking out any unpaid lunch. I need to have a formula or something that will show me how many hours occur after 3pm in each shift. If I only had a few tours to figure, I would just figure it out in my head real fast. However, I have literally a couple hundred tours. Plus, I sometimes have to create a brand new tour for a department. If I had the formula in place, then all I would have to do in the future would be to just plug in the start and end times of the new tour.

[Jockney]

Slight amendment to how you should record the information. Column 'A' Tour Number. 'B' Start Time 'C' End Time 'D' Number of Hours. Put in Cell E1 15:00 Then put this formula into Cells 'E' =IF(C2>=$E$1,SUM(C2-$E$1),0) Then in Cell E2 put =SUM(E:E) This will be your answer

That should do it for you. If you need any help then please let me know.