Calculate production time with a twist

[nmgmarques]

Hi all. This question might get a bit lengthy so I'll jump right to it.

Company X builds cars. It does so between the hours of 8:15 and 17:05, from Monday to Friday. They have a 50 minute lunch break from 12:30 to 13:20. This gives us a total of 8 working hours per working day.

Under normal circumstances, they produce 8 cars per day, which gives us an average of 1 hour for each car.

For argument’s sake, lets say A1 to G1, contain the headers in this order, Cars, Start Date, Start Time, End Date, End Time, Total Time and Average. Let us also assume the following values for each cell from A2 to F2: 8, 24-Feb, 8:15, 24-Feb, 17:05, 8:00. G2 shall be automatically calculated and expressed as a number using the formula =(F2/A2)*24. In this example the value returned should be 1,00 or 1 (one) hour.

If I use the following expression in F2

=IF(AND(E2=B2;C2<0,5208333334);F2-C2-0,0347222222;0,3333333333*(E2-B2)+F2-C2)

Assuming the start and end dates are in fact the same, I get the correct result expressed in fractioned hours, as I change the times. To this point, there are no problems. My problems start if the end dates are different from the starting dates. This would imply the formula would first have to check if the days are similar or not, and then check if the start and end times occur before or after lunch breaks, in order to determine the number of hours worked and subtract the relevant number of lunch breaks in between the start and end times. This has proven to be beyond my reach. Hence my coming hear asking for help.

If at all possible, I would appreciate any info, or if you could at least point me in the right direction.

I thank you all in advance!

 

[nmgmarques]

Wow! Down 9 pages in a work day! Anyway, could anyone help out with this one? It's a real zinger!

 

[barry houdini]

Try this formula

=(NETWORKDAYS(B2,E2)-1)/3+F2-C2+((F2<"12:30"+0)-(C2<"12:30"+0))*"0:50"

format result cell as [h]:mm

Assumes that your start times/dates are always within the working hours

 

[nmgmarques]

Try this formula

=(NETWORKDAYS(B2,E2)-1)/3+F2-C2+((F2<"12:30"+0)-(C2<"12:30"+0))*"0:50"

format result cell as [h]:mm

Assumes that your start times/dates are always within the working hours

Many thanks for the help.

I tried the formula but got a circular reference error. I think it's because your formula points to F2 as being end date, when it's actually E2. So after accepting the error it just gives me 0:00 in F2 (result cell)

link

But correcting all F2 references to E2 didn't solve the issue either. All I get is ##############:

link

Please let me know if you can't see the pics. I am posting from work and sometimes the firewall blocks outbound traffic and we end up loosing information we try to upload elsewhere.

 

[barry houdini]

OK, I went by this formula rather than your description

=IF(AND(E2=B2;C2<0,5208333334);F2-C2-0,0347222222;0,3333333333*(E2-B2)+F2-C2)

that suggests that you have the end date in E2 and the end time in F2.....

If you have those in D2 and E2 respectively then formula should be

=(NETWORKDAYS(B2;D2)-1)/3+E2-C2+((E2<"12:30"+0)-(C2<"12:30"+0))*"0:50"

 

[nmgmarques]

OK, I went by this formula rather than your description



that suggests that you have the end date in E2 and the end time in F2.....

If you have those in D2 and E2 respectively then formula should be

=(NETWORKDAYS(B2;D2)-1)/3+E2-C2+((E2<"12:30"+0)-(C2<"12:30"+0))*"0:50"

That worked like a charm! And way, WAY easier than anything I had thought up. So far I was plotting a if / and statement that already had about 6 ifs and quite a few ands

Just one final question if I may: could you please explain the logic behind this formula? I am trying to understand it but can't make much sense of it.

And once again, thanks!