Compare date in formula

SergSlim

New Member
Joined
Feb 18, 2013
Messages
19
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?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Does this work for you?

=IF($B3="","",IF($B3=Дані!$D$24,"",IF(ISNA(MATCH(1,INDEX((Установка!H1:H5=C3)*(Установка!B1:B5>A3),),FALSE)),"Не встановлено","Встановлено")))
 
Upvote 0
in stockinstalledreturnedre-installed
machine1yes
machine2no06/01/2013
machine3yes
machine4no06/02/201306/08/201327/09/2013
machine5yes
machine6no06/03/2013
machine7no06/04/201306/09/201328/09/2013
machine8no06/05/2013
machine9yes
machine10yes06/06/201306/10/2013
number of machines10
in stock5
installed5
of which re-installed2
column B automatically gives you the correct status
could you try this approach ?

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
This one seems to work, with small correction
=IF($B3="","",IF($B3=Дані!$D$24,"",IF(ISNA(MATCH(1,INDEX((Установка!H$1:H$5=C3)*(Установка!B$1:B$5>=A3),),FALSE)),"Не встановлено","Встановлено")))
I added $ signs in few places to be able to copy it correctly, and changed > to >=.

Now I have to figure our how it works.

Thank you.
 
Upvote 0
Does this work for you?

=IF($B3="","",IF($B3=Дані!$D$24,"",IF(ISNA(MATCH(1,INDEX((Установка!H1:H5=C3)*(Установка!B1:B5>A3),),FALSE)),"Не встановлено","Встановлено")))

Syntax


MATCH(lookup_value, lookup_array, [match_type])
match_type - Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
In your case, if I understood correctly, match_type is FALSE. Why is it so, if Excel help says that it should be either -1, 0 or 1?
 
Upvote 0
FALSE is zero.

Thanks.

How about Index overall? According to help:

  • Row_num Required. Selects the row in array from which to return a value. If Row_num is omitted, Column_num is required.
  • Column_num Optional. Selects the column in array from which to return a value. If Column_num is omitted, Row_num is required.

In your case, =INDEX((Установка!H$3:H$15000=C3)*(Установка!B$3:B$15000>=A3); ) - there is nothing before last parenthesis. Why is it so?
Also, I tried to use just this part of the formula to see what will the result be for two cases - when there is "Не встановлено" and "Встановлено" to compare them, but in both cases I get 0 as a result. While when using =MATCH(1;INDEX((Установка!H$3:H$15000=C3)*(Установка!B$3:B$15000>=A3); );0) I get N/A in one case, and some value in another, which seems to be correct.

How does this Index function work? I suppose, that in one case it should produce "1" so that MATCH function works, and in another case - some other value.
Why do I get 0 in both cases when using Index function only?
What type of Index function is it (there are several described in Excel Help)?
How does this multiplying inside INDEX work exactly?
 
Upvote 0
With that syntax INDEX returns an array. It's equivalent to:

INDEX((Установка!H1:H5=C3)*(Установка!B1:B5>=A3),0)

From Help:

If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively.
 
Upvote 0
With that syntax INDEX returns an array. It's equivalent to:

INDEX((Установка!H1:H5=C3)*(Установка!B1:B5>=A3),0)

From Help:


Thank you! Now I have figured out how this thing works.


I have one more small question.

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

I'm using this method in similar formula, but there is small difference in the end. Instead of simply writing that equipment was returned, in case of FALSE result of IF(ISNA...), I need to write how it was returned. I do it by using VLOOKUP and taking a value from Повернення sheet from column F (from row that matches C3 cell in current sheet), where user selects what kind of return it was.

Now I need to also use this method to check whether it was returned after date in A3, but instead of just checking it, I also need to use value from column F, and row which matches formula.
How do I combine MATCH/INDEX with VLOOKUP?

I tried VLOOKUP(C3,(Повернення!D$3:D$500=C3)*(Повернення!A$3:A$500>=A3),1,FALSE), but it is not correct, because it does not take into account value from column F.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top