Calculate latest date from two columns with exceptions

Hi all



I've been working on creating my own pilot logbook so I have a little more control and customization with data. I'm currently stuck calculating landing currencies.



For day landings you must have completed 3 or more landings within 90 calendar days during the day. This is column W.

For night landings you must have completed 3 more more landings within 90 calendar days at night. This is column X.



If current, calculate +90 days. The caveat here is that day landings do NOT count for night, however night landings DO count for day.



I have attached an example spreadsheet. On the Summary tab I have two fields I need to simply tell me when currency (date) expires for day landings and night landings. My night landings seem to be calculating accurately (to the extent of my testing) however my day landings are incorrect:



In the demo my last eligible night landings were 10/1/2020 - excel should show me 12/30/2020 - which it does.

My last eligible day landings were 11/1/2020 - excel should show me 1/30/2021 - it does not. Instead it's seeing 12/30/2020.



Either my formula contains an error which I can't figure out or my formula is just massively incorrect.



Tips or help would be appreciated. Cheers!

Plz Try these functions..

* After insert bellow functions you should press Ctrl + Shift + Enter

Day landing : =MAX(IF('Flight Logbook'!W8:W19>2,'Flight Logbook'!B8:B19))+90
Night Landings : =MAX(IF('Flight Logbook'!X8:X19>2,'Flight Logbook'!B8:B19))+90

* When you are entering more data you have to change columns range

Originally Posted by JeteMc

As the night landing formula is working I didn't work on that one.
As for the day landing array entered formula**, the following yields the expected date (1/30/2021):

Formula:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

That array formula worked a treat! All the applicable dates now calculate correctly. Thanks buddy!

Originally Posted by JeteMc

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

How can I mark this as solved? I don't see the option.

I do have another question though. I found a different error in another date calculation.

I need to calculate single engine (SEL) currency but I believe it will be similar to the array formula you came up with with only a minor change I would hope.

Single Engine Land (SEL) currency only requires 3 takeoffs and 3 landings within 90 calendar days within that category. The previous formula looks at the logbook as a whole, which is correct. This calculation needs to limit what data is calculated based on if data is in the SEL column (column H) or blank.

TL;DR In the demo spreadsheet under the Flight Logbook tab I need to look at 3 takeoffs and landings within 90 days (again, night counts for day and day does not count for night) and look to see if a value in column H has a value (which would define the flight as single engine, or "SEL") to make sure the calculation accounts for what Aircraft Category was flown. The calculation should omit MEL (column I) then add 90 days to show when "currency" will expire.

Originally Posted by JeteMc

Try the following array entered formulas**:
For Day Landing:

Formula:

Please Login or Register  to view this content.

For Night Landing:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

JeteMc, that did the trick. Thank you for your help.