Hey guys,

I'm a pilot and I keep my logbook in excel. I am required to fly at
least 6 instrument approaches within the past 180 days to be current.

Column A lists the date of each individual flight. Column F is the
number of instrument approaches flown on each flight. I'm trying to
figure out a formula to look at column F from the most recent date and
sum the approaches until it totals at least 6, and then add 180 days to
the last flight so I know when I lose my currency.

For example, I fly 2 approaches on Jan 1, 3 on Feb 10, and 1 on March
20.

I'll be current for 180 days after Jan 1. After Jun 30, I won't be
current because I've only got 4 approaches within the past 180 days
(Feb 10th and March 20th).
So I'm looking for a formula that will tell me I lose currency after
June 30th.

Assuming I fly 2 more approaches on, say, March 25th, I'll be current
until Feb 10th + 180 days. The approaches on Jan 1st don't matter
anymore because summing just 6 approaches from today's date would stop
as soon as it finds at least 6 approaches, 2 on Mar 25th, + 2 on Mar
20th, + 3 on Feb 10th.

Date Approaches
1/1/2005 2
2/10/2005 3
3/20/2005 2
3/25/2005 2

I think it's a simple formula if only I explained it clearly enough.
The values are entered in cells A50:A3000 and F50:F3000

Thanks a lot,
Scott


--
qflyer
------------------------------------------------------------------------
qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448
View this thread: http://www.excelforum.com/showthread...hreadid=382548

G50 =IF(ISBLANK(A50)=FALSE,SUM(F50:F$3000),"")
Copy down to G5000

H50 = IF(AND(G50=6,G51<6),A50+180,"")
Again, copy down to H5000.

Let me know if that's what you're looking for.

"qflyer" wrote in
message ...

Hey guys,

I'm a pilot and I keep my logbook in excel. I am required to fly at
least 6 instrument approaches within the past 180 days to be current.

Column A lists the date of each individual flight. Column F is the
number of instrument approaches flown on each flight. I'm trying to
figure out a formula to look at column F from the most recent date and
sum the approaches until it totals at least 6, and then add 180 days to
the last flight so I know when I lose my currency.

For example, I fly 2 approaches on Jan 1, 3 on Feb 10, and 1 on March
20.

I'll be current for 180 days after Jan 1. After Jun 30, I won't be
current because I've only got 4 approaches within the past 180 days
(Feb 10th and March 20th).
So I'm looking for a formula that will tell me I lose currency after
June 30th.

Assuming I fly 2 more approaches on, say, March 25th, I'll be current
until Feb 10th + 180 days. The approaches on Jan 1st don't matter
anymore because summing just 6 approaches from today's date would stop
as soon as it finds at least 6 approaches, 2 on Mar 25th, + 2 on Mar
20th, + 3 on Feb 10th.

Date Approaches
1/1/2005 2
2/10/2005 3
3/20/2005 2
3/25/2005 2

I think it's a simple formula if only I explained it clearly enough.
The values are entered in cells A50:A3000 and F50:F3000

Thanks a lot,
Scott


--
qflyer
------------------------------------------------------------------------
qflyer's Profile:
http://www.excelforum.com/member.php...o&userid=24448
View this thread: http://www.excelforum.com/showthread...hreadid=382548

On Mon, 27 Jun 2005 11:09:39 -0500, qflyer
wrote:

For example, I fly 2 approaches on Jan 1, 3 on Feb 10, and 1 on March
20.

I'll be current for 180 days after Jan 1. After Jun 30, I won't be
current because I've only got 4 approaches within the past 180 days
(Feb 10th and March 20th).

I believe your interpretation of the regulations is incorrect. The regulations
say nothing about 180 days. They refer instead to the "preceding six calendar
months". A "calendar month" is like the month of January, or February, etc.

So if we are now at July 31, the preceding six calendar months would be Jan,
Feb, Mar, Apr, May and Jun. So in the example above, you would be current
through 31 July, not 30 June.

Granted that there is a semantic problem if it is July 31; you have done six
approaches in July; and NONE in the preceding six calendar months (they've all
been done in THIS calendar month) (i.e. none since 1 Jan); but I'm pretty
certain of my interpretation.

I do this process using VBA, but I'll see if I can come up with a formula,
also.


--ron

Ron,

You're right about the regs. The 180 days is a company policy in place
from the company I fly for. We have requirements for 30, 90, and 180
days flight experience. Thanks for any assistance you can provide.

Scott


--
qflyer
------------------------------------------------------------------------
qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448
View this thread: http://www.excelforum.com/showthread...hreadid=382548

Barb,

That works but not exactly what I'm looking for...that procedure shows
the total # of approaches flown so as the dates progress the instrument
approaches decrease accordingly. The formula in the next cell over does
give me the correct answer, but it only shows in one cell (the cell
matching the last approach flown required to meet currency...so I still
have to scroll through the flights to see what date I'm current 'til.
I'm looking for one formula to go in one cell that does all that.
Maybe that's too much / impossible, but I'm hoping someone more
knowledgeable than I can figure this one out for me.

Thanks


--
qflyer
------------------------------------------------------------------------
qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448
View this thread: http://www.excelforum.com/showthread...hreadid=382548

On Mon, 27 Jun 2005 22:55:16 -0500, qflyer
wrote:

Ron,

You're right about the regs. The 180 days is a company policy in place
from the company I fly for. We have requirements for 30, 90, and 180
days flight experience. Thanks for any assistance you can provide.

Scott

Oh, I see.

Would a VBA solution be acceptable?

I keep a logbook in Excel, and use a VBA routine to compute various parameters
and display them in a message box upon running the macro.

If you understand VBA, you should be able to modify it to pick up your column
headings, and adjust the computations to reflect your company's flight
experience requirements.


--ron

On Mon, 27 Jun 2005 22:55:16 -0500, qflyer
wrote:


Ron,

You're right about the regs. The 180 days is a company policy in place
from the company I fly for. We have requirements for 30, 90, and 180
days flight experience. Thanks for any assistance you can provide.

Scott

Well, here is a very simple UDF written in VBA that should get you started.

It assumes your Dates are in Column A and your approaches are in Column B.

It will display your current currency date, regardless of whether it is before
or after TODAY. It can be extensively modified depending on your requirements
and needs.

To use it, enter
=instcur()
in any cell.

To enter it, <alt-F11 opens the VB Editor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code below
into the window that opens:

==================
Option Explicit

Function InstCur() As Date
Application.Volatile
Dim i As Long
Dim LastRow As Long
Dim AppCount As Long
Const ReqAppCount As Long = 6

LastRow = Range("A65535").End(xlUp).Row

For i = LastRow To 1 Step -1
AppCount = AppCount + Cells(i, 2).Value
If AppCount = ReqAppCount Then Exit For
Next i

InstCur = Cells(i, 1) + 180

End Function
==================


--ron

Ron Rosenfeld Wrote:
On Mon, 27 Jun 2005 22:55:16 -0500, qflyer
wrote:

Ron,

You're right about the regs. The 180 days is a company policy in
place
from the company I fly for. We have requirements for 30, 90, and 180
days flight experience. Thanks for any assistance you can provide.

Scott

Oh, I see.

Would a VBA solution be acceptable?

I keep a logbook in Excel, and use a VBA routine to compute various
parameters
and display them in a message box upon running the macro.

If you understand VBA, you should be able to modify it to pick up your
column
headings, and adjust the computations to reflect your company's flight
experience requirements.


--ron

Ron, I've got a little experience with VBA. I know I couldn't write it
myself, but if I have a working example, I'm sure I can modify it to fit
my needs. I'd greatly appreciate it.

Thanks
Scott


--
qflyer
------------------------------------------------------------------------
qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448
View this thread: http://www.excelforum.com/showthread...hreadid=382548

Well, here is a very simple UDF written in VBA that should get you
started.

It assumes your Dates are in Column A and your approaches are in Column
B.

It will display your current currency date, regardless of whether it is
before
or after TODAY. It can be extensively modified depending on your
requirements
and needs.

To use it, enter
=instcur()
in any cell.

To enter it, <alt-F11 opens the VB Editor. Ensure your project is
highlighted
in the project explorer window, then Insert/Module and paste the code
below
into the window that opens:

==================
Option Explicit

Function InstCur() As Date
Application.Volatile
Dim i As Long
Dim LastRow As Long
Dim AppCount As Long
Const ReqAppCount As Long = 6

LastRow = Range("A65535").End(xlUp).Row

For i = LastRow To 1 Step -1
AppCount = AppCount + Cells(i, 2).Value
If AppCount = ReqAppCount Then Exit For
Next i

InstCur = Cells(i, 1) + 180

End Function
==================


--ron


Ron,

I created a simple workbook with just a few dates in A and a few
approaches in B, and this works perfectly.

However, being the novice that I am, I don't understand how to change it
from looking in column B for the approaches to looking at column F where
I enter my approaches.

Also, I don't know if this matters, but my individual logbook entries
start in row 50. The rows above 50 are used for a summary of flight
time, etc...

One final request...would you be able to post the code you use for your
logbook that uses the 6 calendar months rather than 180 days? That could
be useful to me in the future if I ever move to a different company
without a 180 day requirement.

Thanks so much,
Scott


--
qflyer
------------------------------------------------------------------------
qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448
View this thread: http://www.excelforum.com/showthread...hreadid=382548

On Tue, 28 Jun 2005 20:47:35 -0500, qflyer
wrote:


Well, here is a very simple UDF written in VBA that should get you
started.

It assumes your Dates are in Column A and your approaches are in Column
B.

It will display your current currency date, regardless of whether it is
before
or after TODAY. It can be extensively modified depending on your
requirements
and needs.

To use it, enter
=instcur()
in any cell.

To enter it, <alt-F11 opens the VB Editor. Ensure your project is
highlighted
in the project explorer window, then Insert/Module and paste the code
below
into the window that opens:

==================
Option Explicit

Function InstCur() As Date
Application.Volatile
Dim i As Long
Dim LastRow As Long
Dim AppCount As Long
Const ReqAppCount As Long = 6

LastRow = Range("A65535").End(xlUp).Row

For i = LastRow To 1 Step -1
AppCount = AppCount + Cells(i, 2).Value
If AppCount = ReqAppCount Then Exit For
Next i

InstCur = Cells(i, 1) + 180

End Function
==================


--ron


Ron,

I created a simple workbook with just a few dates in A and a few
approaches in B, and this works perfectly.

However, being the novice that I am, I don't understand how to change it
from looking in column B for the approaches to looking at column F where
I enter my approaches.

The Cells property has row number and column number as its arguments. So for
Column F, you would change the column argument from 2 to 6:

AppCount = AppCount + Cells(i, 6).Value
^


Also, I don't know if this matters, but my individual logbook entries
start in row 50. The rows above 50 are used for a summary of flight
time, etc...

It shouldn't make much difference since, at this point in your career, the loop
will end before it gets back to 50. but for good practice, you could change
the loop entry to read:

For i = LastRow To 50 Step -1
^^


One final request...would you be able to post the code you use for your
logbook that uses the 6 calendar months rather than 180 days? That could
be useful to me in the future if I ever move to a different company
without a 180 day requirement.


I do things much differently in my own log book. Right now I keep track of a
number of data points, so when I analyze, I step forward through every record
(row) in the logbook and accumulate the data for which I am looking. I make
use of multidimensional arrays for this.

In addition, it is set up so I can add columns and other data that I might want
to summarize or change without extensive rewriting.

Some of the data I summarize is for legal purposes; some for the insurance
company; and some for personal purposes.

The worksheet itself is set up with column headings in row 1. The headings in
which I am interested are listed in the Select Case statement; although there
are other columns besides those.

The headings of my analysis summary are stored in Msg(0,0) through Msg(10,0)
and you can see where I set that up at the beginning.

If you want to mimic it, set up your sheet with column names in Row 1; and have
at least some of them match what I'm using.

This is certainly not ready for distribution; it was written for my personal
use so doesn't have to be too fancy. In addition, I use another routine for
data entry, and because of that restriction, I don't have any sanity checks on
the data. Those are done, to some extent, at the time of data entry.

To check for the expiration date of IFR currency; I set up a two dimensional
array Months(0 to 6,0). The first element contains the first day of the month
for each month going back six calendar months from TODAY. So if I ran it
today, the array would look like:

?months(0,0)
6/1/2005
?months(1,0)
5/1/2005
?months(2,0)
4/1/2005
..
..
..
?months(6,0)
12/1/2004

As I am analyzing each record, if there is an IFR app logged on that record,
that number (number of approaches) gets added to the appropriate bin in Months.
So when I'm done, I have 7 monthly bins with the number of IFR apps I did in
each month.

So then I step backwards through each month until I get to six (6). If I don't
get to six, then I output "Not Current".

If I do get to six, then I add seven months and subtract one day to the month
date of the bin in which I got to six.

Good luck! There's probably some word-wrap problems in the Sub, but the VB
module should catch it.

-------------------------
Row 1 of Worksheet: Note that IFR Apps and Gnd Trnr are the only header
columns with spaces.

Date Make/Model N# From To
Purpose Notes ASEL ASES AMEL XC
Day Night IFR Hood IFR Apps
Gnd Trnr Dual SIC PIC Total

===============================
Sub Analyze()
Dim LastRow As Long, LastColumn As Long
Dim c As Range
Dim i As Integer, j As Integer, k As Integer
Dim Fmt1 As String, Fmt2 As String, Fmt3 As String
Dim Response As Integer
Dim Days90 As Date, Months(6, 1) As Variant
Dim Msg(10, 2) As Variant, ColHeads() As String, title As String, Prompt As
String
Dim TotalHours As Double, TotalXC As Double, TotalIFR As Double
Dim Last90 As Double, IFRApps As Integer
Dim Years1 As Date


LastRow = ActiveSheet.Range("a65536").End(xlUp).Row
LastColumn = ActiveSheet.Range("iv1").End(xlToLeft).Column
Days90 = DateAdd("d", -90, Date)
Years1 = DateAdd("yyyy", -1, Date)

For i = 0 To 6
Months(i, 0) = DateSerial(Year(Date), Month(Date) - i, 1)
Months(i, 1) = 0
Next i

Fmt1 = "#,##0.0"
Fmt2 = "0"
Fmt3 = "m/d/yyyy"

ReDim ColHeads(LastColumn)

Msg(0, 0) = "Total Hours as PIC: "
Msg(0, 2) = Fmt1
Msg(1, 0) = "Total Cross-Country Hours: "
Msg(1, 2) = Fmt1
Msg(2, 0) = "Total IFR Hours: "
Msg(2, 2) = Fmt1
Msg(3, 0) = "PIC Hours Last 90 days: "
Msg(3, 2) = Fmt1
Msg(4, 0) = "IFR Approaches in Last 6 months: "
Msg(4, 2) = Fmt2
Msg(5, 0) = "IFR Current until: "
Msg(5, 2) = Fmt3
Msg(6, 0) = "Total ASEL hours: "
Msg(6, 2) = Fmt1
Msg(7, 0) = "Total Day hours: "
Msg(7, 2) = Fmt1
Msg(8, 0) = "Total Night hours: "
Msg(8, 2) = Fmt1
Msg(9, 0) = "PIC Hours Since " & Format(Years1, "m/d/yy") & ": "
Msg(9, 2) = Fmt1
Msg(10, 0) = "Total Time: "
Msg(10, 2) = Fmt1

For i = 1 To LastColumn
ColHeads(i - 1) = Cells(1, i)
Next i

title = "Currency"

For i = 2 To LastRow
For j = 0 To LastColumn - 1
Select Case ColHeads(j)

Case Is = "PIC"
Msg(0, 1) = Msg(0, 1) + Cells(i, j + 1)
If Cells(i, 1) Days90 Then
Msg(3, 1) = Msg(3, 1) + Cells(i, j + 1)
End If
If Cells(i, 1) Years1 Then
Msg(9, 1) = Msg(9, 1) + Cells(i, j + 1)
End If
Case Is = "IFR"
Msg(2, 1) = Msg(2, 1) + Cells(i, j + 1)
Case Is = "XC"
Msg(1, 1) = Msg(1, 1) + Cells(i, j + 1)
Case Is = "IFR Apps"
For k = 0 To 6
If Cells(i, 1) Months(k, 0) Then
Months(k, 1) = Months(k, 1) + Cells(i, j + 1)
End If
Next k
Case Is = "ASEL"
Msg(6, 1) = Msg(6, 1) + Cells(i, j + 1)
Case Is = "Day"
Msg(7, 1) = Msg(7, 1) + Cells(i, j + 1)
Case Is = "Night"
Msg(8, 1) = Msg(8, 1) + Cells(i, j + 1)
Case Is = "Total"
Msg(10, 1) = Msg(10, 1) + Cells(i, j + 1)

End Select
Next j
Next i

Msg(4, 1) = Months(6, 1) 'IFR Approaches

For j = 0 To 6
If Months(j, 1) = 6 Then
Exit For
End If
Next j

If j = 7 Then
Msg(5, 1) = "!NOT CURRENT!"
Else
Msg(5, 1) = DateAdd("m", 7, Months(j, 0)) - 1
End If

For i = 0 To UBound(Msg)
Prompt = Prompt & Msg(i, 0) & Format(Msg(i, 1), Msg(i, 2)) & Chr(10)
Next i

Response = MsgBox(Prompt, vbOKOnly, title)

End Sub
========================
--ron

Ron,

Thanks a lot for the help...the new one works perfectly. The
additional stuff on how you manage yours is a bit over my head, but
I'll work on it over the next few days (maybe months lol) and see what
I can do.

Thanks again,
Scott


--
qflyer
------------------------------------------------------------------------
qflyer's Profile: http://www.excelforum.com/member.php...o&userid=24448
View this thread: http://www.excelforum.com/showthread...hreadid=382548

On Wed, 29 Jun 2005 21:33:45 -0500, qflyer
wrote:


Ron,

Thanks a lot for the help...the new one works perfectly. The
additional stuff on how you manage yours is a bit over my head, but
I'll work on it over the next few days (maybe months lol) and see what
I can do.

Thanks again,
Scott

You're welcome. Glad to help.

--ron