- Hot Topics
Excel Compare date in formula
Hello. I've uploaded an example file: https://docs.google.com/file/d/0B9PW...it?usp=sharing I have a formula with multiple IFs: 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?
This question generated 23 answers. To proceed to the answers, click here.
This thread is current as of October 21, 2013.
For more resources for Microsoft Excel:
This article includes the following tags:
- Microsoft Excel