Compare date in formula

[SergSlim]

Hello.

I've uploaded an example file:
https://docs.google.com/file/d/0B9PWiv0aD6B2VUVlZENNVmRIWU0/edit?usp=sharing

I have a formula with multiple IFs:
=IF($B6="","",IF($B6=Дані!$D$24,"",IF(ISNA(VLOOKUP(C6,Установка!H:H,1,FALSE)),"Не встановлено","Встановлено")))


Well, what is does:
1. If B6 is empty, then D6 (where formula is) would be empty
2. If B6 is equal to $D$24 from other sheet, then D6 would be also empty
3. Now I compare serial number of equipment (C6) with the list of serial numbers on Install (Установка) sheet in column H. If there is a match, than it is "Installed" ("Встановлено"), if there is no match, then is it "Not Installed" ("Не встановлено").

Everything works fine until I get the same serial number listed twice. Same equipment was installed, returned, and came back to my warehouse.
Now it incorrectly says that it is installed, because it was installed earlier before I got it for the second time.

To fix this, I have to compare a date in column A with date on sheet Install (Установка) in column B. So, in this formula I have to add another IF condition in the last step instead of ,"Installed".
But this is not that simple. I have to compare a date from cell A6 with date from range on Install sheet, but only for those rows that match previous IF clauses, otherwise it will always match date.

So to summarize, when formula gets to "Installed" step, it has to do another check. There might be several cells on Install sheet that will cause it to get to "Installed" step. I have to check whether date in A6 is not less than date in appropriate cells that caused formula to get to "Installed" step. If the date is less, then print "Not Installed". If the date is equal or greater than the date on Install sheet, then print "Installed"


In the example file I attached both D3 and D6 say that it was installed, even though only 1 cell on Install (Установка) sheet has the same serial number. We can see that D6 was recieved on 1/2/2013, which is later than it was actually installed (9/1/2012). So for D3 it should say Installed, while for D6 - Not Installed. D6 should change to installed after the same serial will be on Install (Установка) sheet and date in B2 on that sheet would be >= date in A6 of
Реєстр приходу sheet.


Is it possible?

 

[Andrew Poulsom]

For all the rows excluding the heading remove the @ - Distribution[Equipment Name].

 

[SergSlim]

For all the rows excluding the heading remove the @ - Distribution[Equipment Name].

Thanks, I suspected that it was some kind of easy fix. Didn't find a description of @ character in the link you gave me, and also in the other link I posted.

Anyway, the performance increased, but still far from being acceptable for automatic formula calculations.
F9 takes up to 20 sec after adding some value in one of the rows of one sheet.

 

[Andrew Poulsom]

The description of the use of @ is near the beginning of the link you posted. No complex formula is going to be quick when spanning and dealing with about 5000 rows.