Any Excel guru's?

[TommyG]

I making a backup logbook on an excel spread sheet, I am looking for a way for it to highlight when currency for landing, approaches is up or about to be up. Anyone know how to do that?

 

[Cap'n Jack]

Do you mean when the sheet is opened? or do you want it to let you know when currency for a task is expired after an entry is made?

 

[AuntPeggy]

• Select the cell or column you wish to have highlighted.
• In the "Format" menu, select "Conditional Formatting".
• In the dialog that opens, select "Cell Value Is".
• In the next field, select "greater than".
• In the next field, select the cell with the criteria you want to apply or type in the criteria you want to apply.
• Select [Format] button.
• In the "Format Cells" dialog that opens, use the "Font", "Border", and "Patterns" tabs and other controls that format the cell as you want when the criteria occur. Press [OK] to accept the format.
• Add a second, third criteria if you wish.
• Click [OK] when satisfied.

 

[AggieMike88]

Building on what Peggy said, the date function NOW() can be made to return today's date. So if you have recorded the date of your last currency event, using NOW() and conditional formatting should accomplish what you want each time you open the sheet.

 

[jshall755]

I just did something similar in Google spreadsheet. In a data cell on each row I determine if the date in the first column was within the previous 90 days and if so return the number from the landings column. I added a little more logic to another cell for night currency to see if the time was logged at night.

For normal (day) currency: (A47 is the date column, I47 is the landings column)
=if(days360(A47,today())<=90,I47,0)
For night currency: (L47 is the night hours column)
=if(days360(A47,today())<=90,I47*if(L47>0,1,0),0)

Then I sum up the values from each column for my total landings in the past 90 days. Then that value can be used and presented with conditional formatting somewhere, but Google is being VERY slow in getting that feature implemented... grrr... but it should work in excel.

Jason

 

[TommyG]

Very cool, thanks. I will mess with the formulas tonight.

 

[Tom.L]

....

For normal (day) currency: (A47 is the date column, I47 is the landings column)
=if(days360(A47,today())<=90,I47,0)
For night currency: (L47 is the night hours column)
=if(days360(A47,today())<=90,I47*if(L47>0,1,0),0)

...

Jason

Is this how FAA counts days? 'days'360()' is a banking function that assumes that all months have 30 days. I.e. on Sep-05 these formulas would count landings on Jun-06 towards currency. In reality, Jun-06 is 91 days in the past.

Instead of using 'days360()', simply do the following:
IF(TODAY()-A47<=90,I47,0)

--
If you want to be really fancy and avoid extra columns, you can do the following:
( first assume that flight dates are in column A, starting at A3, and landing counts are in column B; and there are no more than 997 flights in this sheet (just enlarge the range below if there are more flights)):

{=SUMPRODUCT(B3:B999,if(A3:A999>=TODAY()-90,1,0))}
This will give you number of landings in the last 90 days.

Note the curly brackets -- they indicate a vector function. When you enter or modify a vector function, you HAVE to end with Ctrl-Shift-Enter.

 

[MBDiagMan]

Yes, conditional formatting is pretty handy.

I built a spreadsheet to track all my requirements for IR, Commercial and CFI. I used conditional formatting to turn an area green once that requirement is met.

 

[jshall755]

Instead of using 'days360()', simply do the following:
IF(TODAY()-A47<=90,I47,0)

Thanks, I didn't realize that about the days360 function. It does make more sense to just do the subtraction.

Jason

 

[JeffDG]

Dates, internally, are stored as decimal days since a specific date...I don't recall the first day of the Excel epoch.

If you have two dates in A2 and A3, then the days between them is =ROUND(A3-A2,0)