Home |
Search |
Today's Posts |
#1
|
|||
|
|||
More Pilot Logbook Help....
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering ? | Excel Worksheet Functions | |||
create daily logbook | Excel Discussion (Misc queries) |