Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

formula for counting days

Greetings... hoping someone can help me out. a picture is below.... I am trying to get E1 (in the top table) to count days since my last flight in that specific aircraft.

when I enter a date and data in the bottom table, the data is populated in the top based on the Aircraft - the top is running totals of everything.


the formula displayed is what I thought would work... any help would be great.....


Anthony

User uploaded file

Posted on Aug 4, 2015 5:43 PM

Reply
13 replies

Aug 5, 2015 2:58 AM in response to t quinn

Hi AC,


Quinn is correct, provided the dates are in ascending order in column A. LOOKUP searches from the bottom of a table and returns the result value associated with the first occurrence of the exact value it is searching for. If it does not find the exact value, it will accept the (first occurrence of the) closest listed value that is not greater than the search value, and return the value associated with that occurrence.


Provided you always have at least one occurrence of each aircraft type listed in the running log, and keep the flight dates in chronological order, LOOKUP will return the correct value.


Regards,

Barry

Aug 5, 2015 4:23 AM in response to Amcginthy

Will the list in the lower table always be in ascending date order AND will the model of plane always be the same? I was assuming the example you provided was a degenerate case of the same model and the, generally, we would find other planes like this:

Date


9/20/1996

PA38-112

9/27/1996

PA38-112

9/27/1996

PA38-112

9/30/1996

PA38-112

7/21/2005

SR-20

6/14/2010

PA25-151

10/1/1997

PA38-112





Aug 5, 2015 5:09 AM in response to Wayne Contello

Hi Wayne,


The lower table will always be in ascending order, however there will be many instances where there are multiple entries on the same date. the model of the plane will change as well - again, could be multiple planes on the same day.


the top table will have a list of all planes I have ever flown, the bottom will be where i input the data. when I input a date and a model, I would like the top to know the days since the last flight.

Aug 5, 2015 6:56 AM in response to t quinn

I think quinn has the solution...


User uploaded file


In the top table:

C2=IFERROR(TODAY()−LOOKUP(B2, Table 4::B, Table 4::A), "")


this is shorthand for, select cell C2, then type (or copy and paste from here) the formula:

=IFERROR(TODAY()−LOOKUP(B2, Table 4::B, Table 4::A), "")


select cell C2, copy

select C2 thru the end of column C, paste


you should adjust the table name to match your table names...

C2=IFERROR(TODAY()−LOOKUP(B2, Table 4::B, Table 4::A), "")


The bolded table name "Table 4" should be named the same as the lower table in your picture

Aug 5, 2015 7:29 AM in response to Amcginthy

Essentially you are looking for the last occurrence of a duplicated value in a column.


If, like me, you prefer MATCH, which has more explicit options than the ancient LOOKUP (you can, for example, explicitly specify 'find largest value', etc.), then you can use something like your original formula but add an IF to cover situations where there isn't an exact match.


User uploaded file

In E2, copied down:


=TODAY()−IF(IFERROR(MATCH($B2,Running Log::$B,0),""),INDEX(Running Log::$A,MATCH($B2,Running Log::$B,1),0))


In F2, copied down (for testing to see if it's retrieving the right date-time):


=IF(IFERROR(MATCH($B2,Running Log::$B,0),""),INDEX(Running Log::$A,MATCH($B2,Running Log::$B,1),0))


Since you can have more than one flight on the same date I expanded the format to show the time while testing. You'll note the formula correctly locates the last occurrence.


If you're getting different results using this formula in your table, then it could be that the merged cells in the 'Running Log' table are throwing it off. In general suggest avoiding merged cells wherever possible in tables that will be referenced by INDEX MATCH and LOOKUP formulas.


SG




Data used for testing:


Date

Type

9/20/1996 0:00:00

PA38-112

9/27/1996 0:00:00

PA38-112

9/27/1996 12:00:00

PA38-112

9/30/1996 0:00:00

PA38-112

9/30/1996 18:00:00

PA38-112

7/21/2005 0:00:00

SR-20

6/14/2010 0:00:00

PA25-151

6/14/2010 17:00:00

PA25-151

10/1/1997 0:00:00

SR-20

Aug 5, 2015 9:19 AM in response to Amcginthy

After studying your screenshot I see you do not have merged cells in the 'Running Log' table itself. I experimented more to simplify my formula above and came up with this:


User uploaded file


=IFERROR(TODAY()−INDEX(Running Log::$A,MATCH($B1,Running Log::$B,1)),"")


If that looks familiar, it probably should.... It turns out to be identical to yours!


It's working as expected here, and also the result in your screenshot seems to be correct. So I'm curious why you conclude your original formula is not doing the right thing... other than producing a spurious result if you have a typo in column B (which you can guard against by formatting column B as Pop-Up Menu).


SG

formula for counting days

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.