search state condition start and end dates?

[pakilprogramador]

please help to find the solution??

search state condition start and end dates

See sheet "DATOS"

 

[Luke M]

Could you elaborate more on what you want to happen? I'm not even sure if you need formulas to read info from DATOS, or if DATOS should read from Hoja3

 

[pakilprogramador]

Hoja3 should read info from DATOS . to get the result in column I

Column J is a manual reference.

thanks

 

[Luke M]

Could you perhaps explain through words what the formula is trying to do then? If all we have is your formula, we can only use the logic which is in the formula, and apparently it's wrong. You show that some cells should result in "PE", but PE is not listed on DATOS.

But, trying to take a guess, I think the formula in I7 needs to be:
=INDEX(DATOS!T:T,SUMPRODUCT(ROW(DATOS!$U$2:$U$25)*(DATOS!$U$2:$U$25<=D7)*(DATOS!$V$2:$V$25>=D7)*(DATOS!$S$2:$S$25=A7)))

 

[pakilprogramador]

the formula in I7 needs to be:

if hour entrance >= hour real then show difference in minutes else ,,if hour entrance <= hour real show "A", else If hour entrance=time out show "NME"

but also ,read status from DATOS between START DATE and final date .

THE RESULT SHOULD BE AS SHOWN IN COLUMN J

sorry is "P" no "PE"

 

[Luke M]

Can you clarify your conditions? You stated two conditions for when hour entrance = hour real. Either the first is >= and the other is <, or first is > and other is <=. Should not be both.

So, possible outcomes are either difference in minutes, A, NME, or a lookup from DATOS?

 

[pakilprogramador]

yeah exactly,, should be at the same time,So, possible outcomes are difference in minutes, A, NME, and also a lookup from DATOS?

 

[Luke M]

Still need clarification on conditions. Hour entrance will either be >=, or < hour real. There is no third option. What order should we be checking the conditions?

 

[pakilprogramador]

1-hour entrance> hour real
condition calculate difference minutes
2 if hour entrance < hour real
condition "A"
3 if hour entrance = hour real
condition "NME"
4 - also ,read status from DATOS between START DATE and final date ,put in same condition,should be as the result of the column j

 

[Luke M]

Now the first three conditions work ok, but still don't see when 4th gets triggered.
Here's a pic for example


Given latest 3 rules, why is the reponse not "A" for both, since Hour entrance < Hour Real?

 

[pakilprogramador]

good observation LUKE M,

there is no "A" for both because it must be replaced by status from DATOS between START DATE and final date.

 

[Luke M]

So, should the logic then be:
1. Check if there is a record on DATOS with matching name and Date in between start/final. If true, return that status.
2. If False for #1, then check the following:
1-hour entrance> hour real
condition calculate difference minutes
2 if hour entrance < hour real
condition "A"
3 if hour entrance = hour real
condition "NME"

 

[pakilprogramador]

yeah, exactly please

 

[Luke M]

In I2, put this formula:
=IFERROR(INDEX(DATOS!T:T,(1/SUMPRODUCT(ROW(DATOS!$U$2:$U$25)*(DATOS!$U$2:$U$25<=D2)*(DATOS!$V$2:$V$25>=D2)*(DATOS!$S$2:$S$25=A2)))^-1),IF(H2=B2,"NME",IF(B2<H2,"A",B2-H2)))

Format cell with number format:
[mm]

 

[pakilprogramador]

very very well, thank's Luke M.

tema solucionado muchas gracias amigo.

saludos.