Thread: Time Calculations with Shifts, Holidays, etc

I have read multiple threads on here with time calculations, but I cannot seem to get any of them to work with my situation.

We need to calculate the amount of production time between 2 cells with the below criteria.

1st Shift starts at 6:30
1st Shift ends at 15:30

2nd Shift starts at 16:30
2nd Shift ends at 1:00 (the next day)

I would also like to be able to specify holidays - these have a tendancy to change based on our production progress.

Below is what I have - but I think that the fact that my second shift passes midnight, it does not work. The below also does not take into account the hour break in our shifts.

Any help would be greatly appreciated!!

A1 = Start Date/Time (dd,mm,yyyy hh:mm)
B1 = Stop Date/Time (dd,mm,yyyy hh:mm)
C1:C5 = Holidays

=(NETWORKDAYS(A1,B1,$C$1:$C$5)-1)*OR("00:00" - "01:00","00:00"-"06:30")-MOD(A1,1)+MOD(B1,1)

Hello
If you want to calculate the number of hours worked between the two dates, try the following formula based on your example cell references.

=NETWORKDAYS(A1,B1,$C$1:$C$5)*(MOD(B1-A1,1))*24-NETWORKDAYS(A1,B1,$C$1:$C$5)

It seems to work for me but run a few tests to see if it returns the correct sum you're looking for. It subtracts the number of Network days if there is 1 hour each day as a break.

Thanks for the response. This still doesn't take into account the hour that I work from midnight to 1:00AM the next morning.

That is where I am having trouble...

Hi
Perhaps I've misunderstood what you want. On my test for example, if I put in the dates beginning 1st of November 16:30 to 30th November 1:00, I get 165 hours. This is with no Holidays and 1 hour each day for breaks subtracted. Is this not the figure you're looking for?

DBY - I really appreciate your help with this.

I believe your formula captures 1:00AM to 6:00AM during its calculation. I am attempting to leave this time period along with the hour break between shifts out.

I am probably doing a poor job of explaining this - so, I apologize for the confusion...