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?

 

[SergSlim]

Can I see some sample data please? I imagine that you need INDEX/MATCH rather than VLOOKUP. Also it looks like you will be able to use ISERROR.

Sure,

https://docs.google.com/file/d/0B9PWiv0aD6B2ZGRWdmVka1pEd1k/edit?usp=sharing

First tab - column F.

 

[Andrew Poulsom]

Does this work for you?

=IF($B3="","",IF($B3=Дані!$D$24,"",IFERROR(INDEX(Повернення!F$3:F$500,MATCH(1,INDEX((Повернення!D$3:D$500=C3)*(Повернення!A$3:A$500>=A3),0),0)),"Не повернено")))

 

[SergSlim]

Does this work for you?

=IF($B3="","",IF($B3=Дані!$D$24,"",IFERROR(INDEX(Повернення!F$3:F$500,MATCH(1,INDEX((Повернення!D$3:D$500=C3)*(Повернення!A$3:A$500>=A3),0),0)),"Не повернено")))

Yes, it does work! Genius! Thank you very much!

 

[SergSlim]

Hey, I got another question.

I decided to use structured references in my file to test performance improvements and I faced some problems.
So I started with two sheets by creating a tables from them, and updating formulas so that they are now using column names.

Well everything works fine on the same sheet. But whenever I change piece of formula that references another sheet's column - weird things happen.

For example originally I had:

=IF(H2="";"";IF(ISNA(MATCH(1;INDEX(('Видача МЦ'!C$2:C$14996=G2)*('Видача МЦ'!D$2:D$14996=H2)*('Видача МЦ'!A$2:A$14996<=B2);0);0));"Не Видано";"Видано"))

I've updated it to use column names on the same sheet/table:
=IF([@[Серійний номер/ID модуля/МАС]]="";"";IF(ISNA(MATCH(1;INDEX(('Видача МЦ'!C$2:C$4950=[@Обладнання])*('Видача МЦ'!D$2:D$4950=[@[Серійний номер/ID модуля/МАС]])*('Видача МЦ'!A$2:A$4950<=[@[Дата установки]]);0);0));"Не Видано";"Видано"))

Then changed it to:
=IF([@[Серійний номер/ID модуля/МАС]]="";"";IF(ISNA(MATCH(1;INDEX((Видача[@Найменування]=[@Обладнання])*('Видача МЦ'!D$2:D$4950=[@[Серійний номер/ID модуля/МАС]])*('Видача МЦ'!A$2:A$4950<=[@[Дата установки]]);0);0));"Не Видано";"Видано"))

After applying this change, everything that matches C2 cell from Видача table is working fine. But everything that does not match C2 cell - works incorrectly.

If I change it to
=IF([@[Серійний номер/ID модуля/МАС]]="";"";IF(ISNA(MATCH(1;INDEX((Видача[@Найменування]=[@Обладнання])*(Видача[@[Серійний номер/Chip ID/MAC адреса]]=[@[Серійний номер/ID модуля/МАС]])*(Видача[@Дата]<=[@[Дата установки]]);0);0));"Не Видано";"Видано"))

none of the cells are showing correct result anymore.

Can I use structured references in this case? What mistakes did I make while changing formulas, so that they don't work?

 

[oldbrewer]

I would find it easier to help you if you made up a simple spreadsheet using the principles within the one in question. Show 2 tables and explain how you want the links between them to work. Your last post is very long and full of foreign characters, it must be possible to simplify it using names, colours, dates.......

 

[Andrew Poulsom]

Could you put your formulas in English please? They are difficult to follow if you don't read Russian.

 

[SergSlim]

Could you put your formulas in English please? They are difficult to follow if you don't read Russian.

Ok, I have translated it to English. I can upload an example a bit later today.

For example originally I had:

=IF(H2="";"";IF(ISNA(MATCH(1;INDEX(('Distribution EQ'!C$2:C$14996=G2)*('Distribution EQ'!D$2:D$14996=H2)*('Distribution EQ'!A$2:A$14996<=B2);0);0));"Not Distributed";"Distributed"))

I've updated it to use column names on the same sheet/table:
=IF([@[Serial No/Chip ID/МАС]]="";"";IF(ISNA(MATCH(1;INDEX(('Distribution EQ'!C$2:C$4950=[@Equipment])*('Distribution EQ'!D$2:D$4950=[@[Serial No/Chip ID/МАС]])*('Distribution EQ'!A$2:A$4950<=[@[Installation Date]]);0);0));"Not Distributed";"Distributed"))Then changed it to:=IF([@[Serial No/Chip ID/МАС]]="";"";IF(ISNA(MATCH(1;INDEX((Distribution[@Name]=[@Equipment])*('Distribution EQ'!D$2:D$4950=[@[Serial No/Chip ID/МАС]])*('Distribution EQ'!A$2:A$4950<=[@[Installation Date]]);0);0));"Not Distributed";"Distributed"))

After applying this change, everything that matches C2 cell from Distribution table is working fine. But everything that does not match C2 cell - works incorrectly.

If I change it to
=IF([@[Serial No/Chip ID/МАС]]="";"";IF(ISNA(MATCH(1;INDEX((Distribution[@Name]=[@Equipment])*(Distribution[@[Serial No/Chip ID/МАС]]=[@[Serial No/Chip ID/МАС]])*(Distribution[@Date]<=[@[Installation Date]]);0);0));"Not Distributed";"Distributed"))

 

[Andrew Poulsom]

I'll wait until you upload an example then. Meanwhile here's a tutorial on structured references:

Using structured references with Excel tables - Excel - Office.com

 

[SergSlim]

I'll wait until you upload an example then. Meanwhile here's a tutorial on structured references:

Using structured references with Excel tables - Excel - Office.com

Yeap, I've read it yesterday, as well as this:
Using formulas in Excel tables - Excel - Office.com
It seems that my link is for 2013 Office, and yours is for 2007.

 

[SergSlim]

Ok, I've uploaded copy of our production file with deleted unnecessary tabs. That's why it is rather big

https://drive.google.com/file/d/0B9PWiv0aD6B2NE85MHRjNHo3Y1E/edit?usp=sharing

It was way faster to do it, rather than creating a test file from scratch, or deleting all data and filling only needed columns.


Anyway,
Install tab
Equipment distributed column
Right now it is:
=IF([@[Serial No/Chip ID/МАС]]="","",IF(ISNA(MATCH(1,INDEX((Distribution[@[Equipment Name]]=[@Equipment])*('Distribution EQ'!D$2:D$4950=[@[Serial No/Chip ID/МАС]])*('Distribution EQ'!A$2:A$4950<=[@Date]),0),0)),"Не Видано","Видано"))

It works fine when the formula is changed back to:
=IF([@[Serial No/Chip ID/МАС]]="","",IF(ISNA(MATCH(1,INDEX(('Distribution EQ'!C$2:C$4950=[@Equipment])*('Distribution EQ'!D$2:D$4950=[@[Serial No/Chip ID/МАС]])*('Distribution EQ'!A$2:A$4950<=[@Date]),0),0)),"Не Видано","Видано"))


Red cells are incorrect - they should be all green (except few ones at the middle-bottom of the list).
Right now as far as I understand, the formula takes a value only from C2 cell ("Декодер Homecast C3200CO/eM2150CO") on Distribution EQ tab (Distribution table), and compares it with values from column G (Equipment) on Install tab (Установка table) [sorry, forgot to rename this table's name. It's not used in this formula anyway.]
So on Install tab - green cells are showing fine for equipment with this name only. For all other equipment names - cells are incorrectly showing in red.