projecting an expiration date

[flightimer]

Howdy all,

I'm in charge of tracking a group of pilot's landings to ensure they
have had 3 night landings in the last 90 days, or conversely, when they
will no longer be "night landing current", the date which is 90 days
after their last three night landings.. got it?? Right now, we
manually enter the number of landings done on a particular day adjacent
to the date in a column for each pilot.

I am trying to automate the system so Excel will find the date of their
last night landing, search back to the date of their third previous
night landing, then show me 90 days from the date of that third
previous landing... clear?

Can anyone make suggestions toward this goal?

don't ya just love record keeping for the government

 

[BrianB]

You haven't said how you tell a night landing as different from a day
(?) landing. It may be a good idea to have an additional column or row
to put N or D against the landing time - this would certainly make the
process easier.

Regards
BrianB
=====================================

 

[Earl Kiosterud]

Flightimer,

=IF(LARGE(A2:A30,3)<(B1-90),"Not Current","Current")

The current date is in B1. You can substitute NOW(), but must ensure that
it's current, in case the worksheet hasn't calculated for a long period).

This doesn't differentiate night landings, as you haven't said how they're
indicated. It just looks at the dates, and sees if the 3rd largest is not
over 90 days old.

The date at which the pilot will become not current is given by:

=LARGE(A2:A27,3)+90