How to calculate the elapsed working days between two dates based on more criterias

[Villalobos]

Hello,

I would like to ask some help from you to calculate the elapsed working days between two dates based on more criterias.
In shortly, I want to know the number of elapsed working days based on Material + actually linked Batch + Highest iteration + "Ok" Status.

For the details and the manually added expected results please see the attached file.

Thank you in advance for your reply!

[mikeTRON]

It appears that I am not understanding something or your sample doesnt show us some examples of "number of elapsed working days based on Material + actually linked Batch + Highest iteration + "Ok" Status."

[José Augusto]

Hi
use in N3 the following formula

Please Login or Register  to view this content.

and copy down

[Villalobos]

Hi,

I try to explain.

The point is that: we check the Batch numbers per material on different date range (with start and end date), the event number of batch check is the "Iteration", and when the Batch was accepted then receive the OK status. My main problem is that some Batch number is repeating regarding the same material.

For example (range: A17:M18): the Material of Batch 7 was started to check on 10/01/2016 and finished on 25/01/2016, so the working days is 11.

but... there was an other check (range A19:M22) started on this date (10/01/2016) regarding this material but it was finished on 23/02/2016, in this case the number of working days were 32.

[Villalobos]

Hi José,

Your solution give me back the expected result, but each day we add new rows to the list. How could be modify the formula in order to avoid the manual range adjustment?

[Villalobos]

Ohh... I've just checked again with larger data set and in some case the formula is not give back the expected result. Could you have a look please?

[José Augusto]

Hi
Try this

Please Login or Register  to view this content.

I correct this ... ($J$3:$J$203=J3) ....

[José Augusto]

Sorry
Iteration don't make sense.
use

Please Login or Register  to view this content.

and see if is this you want.
See the file

[Villalobos]

Thanks, works fine.