Using LARGE to return Date with Conditional Statement

Wow, this didn't format like I intended but I hope you understand what I was going after with the columns and rows.

Originally Posted by bcathell

A B C D E
1 2/24/16 2 1 DE DE
2 5/29/16 1 1 DE
3 6/22/16 1 1 DE
4 8/27/16 1 2 DE
5 11/30/16 2 1 DE
6 12/31/16 1 1 DE
7 1/9/17 1 1 DE
8 2/23/17 1 1 DE

Originally Posted by loginjmor

Hm. I'm still a little fuzzy on the logic here. Let's break your formula down:

=LARGE(IF((SUMIFS(B1:B8, A1:A8, ">="&TODAY()-90, D1:D8, "="&E1)>2)*(SUMIFS(C1:C8, A1:A8, ">="&TODAY()-90, D1:D8, "="&E1)>2), A1:A8), 3)

Let's start with the first SUMIFS:

SUMIFS(B1:B8, A1:A8, ">="&TODAY()-90, D1:D8, "="&E1)
This is going to return the number 5 because the dates 11/30/2016, 12/31/2016, 1/9/2017, and 2/23/2017 are all within 90 days of today. And they have corresponding values of 2, 1, 1, and 1. So 2+1+1+1=5 which is ALWAYS going to be greater than 2.

Yes, this is correct.

Let's look at the next SUMIFS:

SUMIFS(C1:C8, A1:A8, ">="&TODAY()-90, D1:D8, "="&E1)
This is going to return the number 4 because, again, the dates 11/30/2016, 12/31/2016, 1/9/2017, and 2/23/2017 are all within 90 days of today. And they have corresponding values of 1, 1, 1, and 1. So 1+1+1+1=4, which is ALWAYS going to be greater than 2.

Yes, this was my intention. I have used the SUMIFS in part of another formula that does exactly as you describe. That formula that I'm using in the actual document is:

=IF(C15="", "", IF(OR(AND(SUMIFS(Night_Takeoff,FlightDates,">="&TODAY()-89,FlightDates,"<="&TODAY(),Logbook!G3:G10000,"="&C15)>=3,SUMIFS(Night_Landing,FlightDates,">="&TODAY()-89,FlightDates,"<="&TODAY(),Logbook!G3:G10000,"="&C15)>=3),AND(OR(Certificate_Grade="Commercial Pilot", Certificate_Grade="Airline Transport Pilot"), ISNUMBER(MATCH(C15,T_Plane_More_1_Pilot,0))=TRUE,Logbook!$L$2>1500, $C$16="Current",SUMIFS(Logbook!L3:L10000, FlightDates, ">="&TODAY()-89, FlightDates, "<="&TODAY(), Logbook!G3:G10000,"="&C15)>=15,OR(AND(SUMIFS(Night_Takeoff,FlightDates,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY())),FlightDates,"<="&TODAY(), Logbook!H3:H10000, "=Yes")>=3, SUMIFS(Night_Landing,FlightDates,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY())),FlightDates,"<="&TODAY(), Logbook!H3:H10000, "=Yes")>=3),MAX(IF(C15=Training!J4:J25, Training!L4:L100))>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))),"Current", "Not Current"))

The above is a little sloppy and could be cleaned up, but it does exactly what I want for the conditional part of the statement. I would like to replace "Current" with the date (which I will try to explain below). Also, in the above formula, I used >=3, in this workbook, I used >2, they do the same thing, correct?

[QUOTE]
So the first part is TRUE, and the second part is TRUE, so now we end with the last part of the formula:

LARGE( the stuff we just looked at is TRUE so look at the WHOLE LIST OF A1:A8 and give me the 3rd largest date) Which is 12/31/2016. It will ALWAYS be 12/31/2016 based on the above logic.[\QUOTE]

Yes, I agree with you. And I understand why this isn't correct for what I'm trying to do.

So walk me through this slowly. When you say the sum of column B is greater than 2, do you mean start summing at the first date that is within 90 days of today? In other words, that would start with Cell B5, which has a value of 2?

Then you say the sum of Column C ALSO has to be greater than 2? Or are you saying the sum of column B PLUS the sum of Column C? In other words the first date that matches your criteria is 11/30/2016:

Cell B5 is 2, Cell C5 is 1. B5 + C5 = 2+1 = 3. That is greater than 2. Is that what you're looking for?

OR is it

Cell B5 is 2, Cell C5 is 1. B5<= 2 (No Match) and C5 <2 (No Match) So then we go to the next cell A6, which is 12/21/2016.
Cell B6 is 1, Cell C6 is 1.
B5+B6=3 which is > 2 (Match) but C5 +C6 = 2 <= 2 (No Match) So only one condition is true, but not the other. So then we go to the next cell which is 1/9/2017.
Cell B7 is 1, Cell C7 is 1.
B5+B6+B7 = 4 > 2 (Match) C5+C6+C7 = 3 > 2 (Match) Now both conditions match so you want to return 1/9/2017 which is the first instance both conditions match? Is this correct?

Sorry, I'm just not understanding what you're testing for.

I think you're very close to understanding what I would like, I'm not putting it into words very well.

The intended use for this is as a pilots (aviation) logbook. In order to maintain currency, a pilot needs three takeoffs (Column B) and three landings (Column C) within the previous 90 days. So, if I were to do this via a paper logbook, I would start at the most recent date, and count until I saw three takeoffs (B) AND three landings (C). When both conditions are met, I look at the date. So, the last part after "OR is it" is the correct logic for which I am trying to make a formula, except instead of starting 90 days ago and counting forward, I intended to start today and count backwards not to exceed 90 days. To reiterate, the bug formula from my actual workbook copied in here works perfectly to determine current versus not. I would like to go one step further and do what is described here and below.

After the date is computed, I add 90 days to the date, but that part is easy enough I can handle (whatever the date is +90), therefore I didn't include it in the above formula(s).

The final cell will say "Current until XX/XX/XXXX" or "Not Current". I also didn't include the words prior to the date because that one is also easy enough... I hope. "Current until "formula....

Does this help? I am working late tonight, I won't see this until tomorrow afternoon, so I apologize ahead of time if my response is slow.

I did a poor job of explaining the D column. The D column is a "constant", if you will, that is used elsewhere and I added it in here so I didn't forget. However, yes, that is exactly what I was looking for.

Originally Posted by bebo021999

As per my understading:

Get the most recent "DE" in column D (D7), then move backwards, sum B and sum C till sum B & sum C, >2 both?

i.e,
9/1 ; sum B = 1, sum C =1, DE, FALSE
31/12 ; sum B = 2, sum C =2, DE, FALSE
30/11 ; sum B = 4, sum C =3, DE, ok, get the date.

Is it what you are looking for?

Originally Posted by loginjmor

Hi -

This was a pretty tough nut to crack. Basically you have to get Excel to look at the dates and landings in reverse order, just as you do it by hand. I got it to work, but I had to add two "helper" columns that accumlated the takeoffs and landings from the latest date back. Those helper columns also check to see if your DE criteria is met as to whether or not to add in the takeoffs and landings on a given date.

Then, I used an Array formula of nested IF statements to pull out the list of dates that meet the criteria of 90 days or less with 3 or more accumulated takeoffs AND landings in that time period and return the latest date. That way you have the most current date that you can add 90 days to to determine how long they are current for.

The meat of the array formula looks like this:

MAX(IF((A2:A9>=TODAY()-90),IF((C2:C9>2),IF((E2:E9>2),A2:A9,""),""),""))

It checks the dates to see if they are within 90 days of today. Then it checks the helper columns to see if both columns are greater than 2. If all three conditions are met, then the dates are stored in an array. MAX searches the array for the largest date (which will exclude the later dates that have 2 or less takeoffs and landings).

To generate a message that the pilot is out of compliance, we have to wrap this whole thing in an IF statement, compare the above MAX formula to 0 (i.e., the pilot doesn't have enough takeoffs and landings in the last 90 days), and then display "Not Current" or display the date the pilot is current to (basically, copy the same formula above to the second half of the outside IF statement. That whole formula looks like this:

=IF(MAX(IF((A2:A9>=TODAY()-90),IF((C2:C9>2),IF((E2:E9>2),A2:A9,""),""),""))=0,"Not Current",MAX(IF((A2:A9>=TODAY()-90),IF((C2:C9>2),IF((E2:E9>2),A2:A9,""),""),"")))

Since it is an Array Formula, you have to press the Ctrl-Shift-Enter keys at the same time from the editing window to engage the array functionality. If you did it right, Excel adds curly braces {} around the formula. Attached is your spreadsheet with the helper columns and the above formula. To add more dates and takeoffs/landings, you will have to copy the accumulation formulas in columns C and E down and adjust the cell ranges in the above formula (which is located in Cell B11 in the attached spreadsheet.

If you want to do away with the helper columns, you will need to use a VBA program to do the calculations. I couldn't figure out how to do it otherwise. Although, there are some VERY bright people on this forum. So maybe someone else might figure it out.

Hope this helps.

My apologies, work has been keeping me busy for the last few days. I've had time without an internet connection to play with this more, and I came up with an incredibly similar solution to you, also by adding two columns for a cumulative sum in reverse order. I also added another two columns, which your solution would eliminate the need for.

I like what you did, it's cleaner than the way I came up with, and will alter what I did to use the suggestions you came up with.

Thank you for your help!! It is very much appreciated!

Here is how I did it: