calculate time

[IvanK]

Hi,
I have attached to this post the file with data and formula's.

I can't seem to find out how to get a time difference calculated between a planned start & actual start + planned end & actual end, when the actual start was earlier than the planned and/or when the actual end is later than the planned one.

The time is expressed in dd-mm-yyy hh:mm:ss in all the data fields.

Context:
Teams receive a time window within which they are authorized to execute a task. It is not allowed to start earlier and/or exceed the authorized end time.
The assigned window is expressed in planned start and planned end time.

A controller registers the actual start and end times.

The goal is to get an automated calculation of the time excess, when applicable.
In addition, I need to formula to calculate the excess time only for 1 team (indicated in the sheet with "y" in the 1st column).

Currently the used formula for comparison returns "xxxxxxxxxxxx" when it is to calculate the excess time in very specific cases.

Hope someone can get me on the right track again.

[Dave H9]

Can't quite work out what your trying to acheive with the formula but your OR's and AND's seem to be wrong, they should be in this format -

Please Login or Register  to view this content.

If you could revisit this please then I will try and help you out.

PS the ####### are returned when there is a negative time.

Dave H

[TheExcelphile]

Use the following formula in G3, and autofill.

=SUM(IF(B3>D3,B3-D3,0),IF(C3>E3,0,E3-C3))

Don't forget to click on the little star to the left of this post if you feel I helped!

[IvanK]

Hi Dave,
thanks for the OR - AND indication.
seems MS Excel didn't return an error on that.
xxxxx = negative value, didn't know that one yet and I have sorted out the issues.
for those who might need the same solution, I have corrected the attachment here

[Dave H9]

Ivank,

Your attachment still has formula which are not quite accurate.

Using exelphile's solution with my own adaptation, I think the attached is what you are looking for (also amended column F)

Let us know if that sorts you out.

Dave H