I have created a program with one of our work systems to monitor how people 'book' onto jobs they are working on. Effectively this results in returning a value for time (and date) they booked on.
People can look at the document at any point and when they do I want to show how long that person has been booked onto the job. As people may work on a job over multiple days in need the formula to consider this. I only want it to calculate time that they are on shift, and also to remove time they are on breaks (12:00-12:30)
The problem gets more complex as there are a variety of different shift patterns to consider. Example of shift patterns:
Mon-Tues 6am-3:45pm,
Mon-Tues 7am-4:45pm
Mon-Tues + 4 other variations.
Tues-Fri 6am-3:45pm,
Tues-Fri 7am-4:45pm,
Tues-Fri + 4 other variations.
Mon-Fri (6am-2:30pm mon-thurs, 6am-11pm fri)
Mon-Fri (7am-3:30pm mon-thurs, 7am-12pm fri)
Mon-Fri + 4 other variations.
My initial thoughts were to create individual formulas for each shift pattern, then I would refernce that cell depending on what shift pattern the person worked.
But I am stuck on how to actually create the formula to work out elapsed time..
Can anyone help?
I have attached a copy of the sheet to try and make more sense than my garbled explaination above.
Cheers,
Steve
Bookmarks