Formula for date since last entered value

For your Takeoff item, enter the following ARRAY formula where you want the days to appear:

{=TODAY()-MAX(IF($L$14:$L$59=1,$A$14:$A$59))}

Don't forget to press Ctrl+Shift+Enter to make it an array formula.

You can use the same principle for the others and potentially use conditional formatting to highlight if it's over the number of days required.

Hi Pat,

Item 1 should be fairly straightforward - you can just change the formula to show >0 instead of =1.

Items 2 and 3 are a bit trickier, partly because of the way the data is structured. My problem would be that if you have an entry in 1B and 1C say for a particular date, how would you know which one to assign the take-off, landing, etc to?

I've got a suggestion on how you could change your data layout to make it a bit more flexible by using a table, then you could get your other data with a pivot table. I'll knock up a quick example for you... watch this space

Hi again Pat,

I had a play around with my table idea, but when I looked a bit more at your data I wasn't sure that I have a clear enough understanding of how you're using your spreadsheet and, although it would make it more flexible in some ways (e.g. if you add more aircraft), it might make your data entry take longer. Something that needs a bit more consideration I think.

So I had a bit more of a think about your 3 questions and have come up with a formula which I think covers them all. I put your aircraft references in E3 to H3,
then this formula in E4, so the MQ-1 Takeoff days for aircraft 1B:

{=IF(TODAY()-MAX(IF($L$14:$L$59+$B$14:$B$59>=2,$A$14:$A$59))=TODAY(),0,TODAY()-MAX(IF($L$14:$L$59+$B$14:$B$59>=2,$A$14:$A$59)))}

Note that it's still an array formula so you need to press Ctrl+Shift+Enter each time you edit it.

You can then copy this across and down for your other aircraft and Takeoff, landing, etc, but you'll need to adjust the ranges in each case. This checks to see if the result of the calculation is the same as today's date and puts 0 if it is, or runs the MAX(IF...) calculation if it isn't.

One other way you can make the formula a bit easier to read and to save you adjusting ranges when you add more rows is to convert your existing data to a table. I did this in the attached example and the formula in E4 now looks like:

{=IF(TODAY()-MAX(IF(Table1[Takeoff]+Table1[1B]>=2,Table1[Date]))=TODAY(),0,TODAY()-MAX(IF(Table1[Takeoff]+Table1[1B]>=2,Table1[Date])))}

It's exactly the same as the one above, but using the column headers just makes it easier to read and auto adjusts as the rows grow.

Hope that gives you enough to be going on with

I just had one of those "derrr" moments Of course you can just check the total of the airplane column to see if you entered anything there!

{=IF(SUM(Table1[1B])=0,0,TODAY()-MAX(IF(Table1[Takeoff]+Table1[1B]>=2,Table1[Date])))}

Sorry - it was quite late in the evening when I posted before

Hi Pat,

See you're trying new things already

Let me take a look and I'll get back to you. Might not be tonight (it's 11:15 pm in England!) but I will see what I can do in the morning.

Cheers
Shirley

Hi Pat,

I've had a look at your latest version and your questions:

1. To merge the 1 Takeoff for both 1B and 1C I've just added both of those columns into the calculation so it now looks like this:

{=IF(Table1[[#Totals],[1B]]+Table1[[#Totals],[1C]]=0,0,TODAY()-MAX(IF(Table1[Takeoff]+Table1[1C]+Table1[1B]>=2,Table1[Date])))}

As you can see, the first part is now looking at the #Totals row in the table, which links into...

2. As you found out, you can add a "Total Row" to your table with the option on the table design tab. This is a great feature as it will just update the totals as you add more rows and will always stay at the bottom. As shown in Item 1, you can also use these #Totals in your formulas. If you want to add a new row to your table, go to the last column (comments) and select the last row ABOVE the Total row and press the Tab key. This will add a new row to your table, including any formatting. If you had formulas in any of your rows, it would also copy these down to the new row automatically.

3. For your Flight hours total, I've used the SUMIF formula which is where I think you were going with this.

=SUMIF(Table1[1B],1,Table1[Primary Time])

This doesn't need to be an Array formula by the way. At the moment it only includes the Primary Time, but you could extend it to use Secondary time as well if you wanted to.

I changed another couple of things, just as suggestions:

In your first area for "Days Since", I changed the Due Days to a number and added a new column for "Warn". I've then used these to drive some conditional formatting in column B. When the number in B hits the Warn figure it turns Amber, then turns red when it is equal to or above the Due days giving you a RAG status.

I also added conditional formatting to your airplane columns (1B, 1C, 9 and C table columns) which highlights them in red if you put in a tail number but none of these have a "1". It acts as a kind of warning that you have some data without an airplane assigned. I did this because I noticed that the Flight Hours total didn't match the Primary Time column total and noticed some missing data.

There are comments attached to some of the cells I changed just to give you a guide.

Hope all that helps. I've attached another new version for you.

Shirley