Excell 2002 Windows XP service pack 3
I have a worksheet that tracks pilot flight times by month, then totals and checks aginst quartery and annual limits. I have most of the automation complete. However I have been making a new sheet each month.
There is a data box for the basic information : Name, month, days in month ( I need an if statement tied to month entered to fill in the form where required ,30,31,28 or 29), Year, days off.
That part works well.
What I am trying to do is have the month go in C29 and year go in D29. Then find that date in the table of hours (K18:J41) and place that in H29. Then fill in UPwards the previous 10 months in the same table.
K18:J41 will wxpand as time goes by. and incorporates the previous two years minimum.
I am attempting to post the sheet.
Thanks
Here are a few formulas that may make your work a bit easier. More info is needed to get a full understanding of what you are trying to do.
Only fill in the tinted areas. The plain (non * ) background cells contain formulas. The dates in column B and column J must be entered as d/m/y so the vlookup will work.
The cell formats for column B is "mmm", C is "0", D is "yyyy"; J is "dd mmm yy"
Excel Jeanie (see link after table) will make posting your worksheet very easy.
Sheet3 *ABCDEFGHIJK1NameMonthDays in MonthYearDays Off******2***********3***********4***********5***********6***********7***********8***********9***********10***********11***********12***********13***********14***********15***********16MarkJul312007**61****17MarkAug312007**81****18MarkSep302007**78****19MarkOct312007**76**01 Oct 067120MarkNov302007**83**01 Nov 067621MarkDec312007**69**01 Dec 067922MarkJan312008**75**01 Jan 078223MarkFeb292008**71**01 Feb 078624MarkMar312008**73**01 Mar 076525MarkApr302008**73**01 Apr 077826MarkMay312008**67**01 May 076127MarkJun302008**63**01 Jun 076328MarkJul312008**76**01 Jul 076129MarkAug312008**72**01 Aug 078130*********01 Sep 077831*********01 Oct 077632*********01 Nov 078333*********01 Dec 076934*********01 Jan 087535*********01 Feb 087136*********01 Mar 087337*********01 Apr 087338*********01 May 086739*********01 Jun 086340*********01 Jul 087641*********01 Aug 0872Spreadsheet FormulasCellFormulaB16=DATE(YEAR(B17),MONTH(B17)-1,1)C16=DATE(YEAR(B16),1+MONTH(B16),1)-DATE(YEAR(B16),MONTH(B16),1)D16=YEAR(B16)G16=VLOOKUP(B16,$J$19 K$94,2,FALSE)B17=DATE(YEAR(B18),MONTH(B18)-1,1)C17=DATE(YEAR(B17),1+MONTH(B17),1)-DATE(YEAR(B17),MONTH(B17),1)D17=YEAR(B17)G17=VLOOKUP(B17,$J$19K$94,2,FALSE)B18=DATE(YEAR(B19),MONTH(B19)-1,1)C18=DATE(YEAR(B18),1+MONTH(B18),1)-DATE(YEAR(B18),MONTH(B18),1)D18=YEAR(B18)G18=VLOOKUP(B18,$J$19K$94,2,FALSE)B19=DATE(YEAR(B20),MONTH(B20)-1,1)C19=DATE(YEAR(B19),1+MONTH(B19),1)-DATE(YEAR(B19),MONTH(B19),1)D19=YEAR(B19)G19=VLOOKUP(B19,$J$19K$94,2,FALSE)B20=DATE(YEAR(B21),MONTH(B21)-1,1)C20=DATE(YEAR(B20),1+MONTH(B20),1)-DATE(YEAR(B20),MONTH(B20),1)D20=YEAR(B20)G20=VLOOKUP(B20,$J$19K$94,2,FALSE)B21=DATE(YEAR(B22),MONTH(B22)-1,1)C21=DATE(YEAR(B21),1+MONTH(B21),1)-DATE(YEAR(B21),MONTH(B21),1)D21=YEAR(B21)G21=VLOOKUP(B21,$J$19K$94,2,FALSE)B22=DATE(YEAR(B23),MONTH(B23)-1,1)C22=DATE(YEAR(B22),1+MONTH(B22),1)-DATE(YEAR(B22),MONTH(B22),1)D22=YEAR(B22)G22=VLOOKUP(B22,$J$19K$94,2,FALSE)B23=DATE(YEAR(B24),MONTH(B24)-1,1)C23=DATE(YEAR(B23),1+MONTH(B23),1)-DATE(YEAR(B23),MONTH(B23),1)D23=YEAR(B23)G23=VLOOKUP(B23,$J$19K$94,2,FALSE)B24=DATE(YEAR(B25),MONTH(B25)-1,1)C24=DATE(YEAR(B24),1+MONTH(B24),1)-DATE(YEAR(B24),MONTH(B24),1)D24=YEAR(B24)G24=VLOOKUP(B24,$J$19K$94,2,FALSE)B25=DATE(YEAR(B26),MONTH(B26)-1,1)C25=DATE(YEAR(B25),1+MONTH(B25),1)-DATE(YEAR(B25),MONTH(B25),1)D25=YEAR(B25)G25=VLOOKUP(B25,$J$19K$94,2,FALSE)B26=DATE(YEAR(B27),MONTH(B27)-1,1)C26=DATE(YEAR(B26),1+MONTH(B26),1)-DATE(YEAR(B26),MONTH(B26),1)D26=YEAR(B26)G26=VLOOKUP(B26,$J$19:$K$94,2,FALSE)B27=DATE(YEAR(B28),MONTH(B28)-1,1)C27=DATE(YEAR(B27),1+MONTH(B27),1)-DATE(YEAR(B27),MONTH(B27),1)D27=YEAR(B27)G27=VLOOKUP(B27,$J$19:$K$94,2,FALSE)B28=DATE(YEAR(B29),MONTH(B29)-1,1)C28=DATE(YEAR(B28),1+MONTH(B28),1)-DATE(YEAR(B28),MONTH(B28),1)D28=YEAR(B28)G28=VLOOKUP(B28,$J$19:$K$94,2,FALSE)C29=DATE(YEAR(B29),1+MONTH(B29),1)-DATE(YEAR(B29),MONTH(B29),1)D29=YEAR(B29)G29=VLOOKUP(B29,$J$19:$K$94,2,FALSE) Excel tables to the web - Excel Jeanie Html 4
Thanks, It is quite amazing your form is close to what I have. I am still trying to paste a copy. I will work on the formulas and see what they do.
Mark
test #160;ABCDEFGHIJKLMN2#160;#160 ilot Namejoe bob#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;3#160;#160;MonthApril#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;4#160;#160;days in month30#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;5#160;#160;Year2008#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;6#160;#160;Rest Periods2#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;7#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;8#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;9#160;#160;8/15/2008#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;10#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;=IF#40;#40;#40;#40;#40;F6=#34;January#34;,31,5,If#40;F6= #34;February#34;, 28,5,IF#40;f6=#34;April#34;,30,5#41;#41;#41;#41;#41;#41;#41;#160;#160;#160;11#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;12#160;XXXXXX Company, Inc.#160;#160;#160;#160;#160;#160;13#160;Flight Time Limits Worksheet#160;#160;#160;#160;#160;#160;14#160;Name#58;joe bob#160;Month#58;AprilYear#58;2008#160;08/2008#160;#160;#160;#160;15#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;16#160;This form is to be used as a worksheet for pilots to determine their hours of availability for the next month. #160;The form should be completed on the pilot#39;s last duty day of the month and submitted to the Chief Pilot. The Chief Pilot will use the informati#160;#160;#160;#160;#160;flight times17#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;18#160;#160;MonthYear#160;#160;#160;Hours#160;#160;January31200710.519#160;A.May2007Complete lines A thru K starting eleven months prior to this month. #160;Include all Commercial flight time.1A20.7#160;#160;February28200720.920#160;B.June20071B27.6#160;#160;March31200712.321#160;C.July20071C24.8#160;#160;April30200723.322#160;D.August20071D4#160;#160;May31200717.123#160;E.September20071E15.2#160;#160;June30200725.224#160;F.October20071F26.4#160;#160;July3120072525#160;G.November20071G3.3#160;#160;August312007426#160;H.December20071H19.3#160;#160;September30200715.227#160;I.January20081I19#160;#160;October31200726.428#160;J.February20081J19#160;#160;November302007729#160;K.March20081K19#160;206.2December31200719.330#160;2Maximum hours for a 12 month period.21400#160;#160;January3120080Spreadsheet FormulasCellFormulaC9=TODAY#40;#41;K12=IF#40;AC183#62;0,#34;DUE#34;,IF#40;AG183#62;0,#34;CHECK#34;, #34;#34;#41;#41;C14=D2F14=D3H14=D5J14=C9N18=SUM#40;O18#58;Q18#41;N19=SUM#40;O19#58;Q19#41;N20=SUM#40;O20#58;Q20#41;N21=SUM#40;O21#58;Q21#41;N22=SUM#40;O22#58;Q22#41;N23=SUM#40;O23#58;Q23#41;N24=SUM#40;O24#58;Q24#41;N25=SUM#40;O25#58;Q25#41;N26=SUM#40;O26#58;Q26#41;N27=SUM#40;O27#58;Q27#41;N28=SUM#40;O28#58;Q28#41;J29=SUM#40;N18#58;N29#41;N29=SUM#40;O29#58;Q29#41;N30=SUM#40;O30#58;Q30#41; Excel tables to the web #62;#62; Excel Jeanie HTML 4
The above is part of the form.
the green boxes are manually filled in. and K thru N are manual.
I need H43 to list the number of days in the month listed in D2, probably comming from L.
I need to make this as easy to do with the fewest imputs required. It should be easy to calculate any month that is desired as long as data was entered.
Ok, I have most of it working. Ugly, but working.
But H28 is not the right number.. What did I do wrong? I could not get anything till changed the last of the lookup to true. now they are all giving the previous month?test #160;BCDEFGHIJKLM2#160ilot Namejoe bob#160;#160;#160;#160;#160;#160;#160;#160;#160;3#160;Month15 Apr 08#160;#N/A#160;#160;#160;#160;#160;#160;#160;4#160;days in month30#160;#160;#160;#160;#160;#160;#160;#160;#160;5#160;Year2008#160;#160;#160;#160;#160;#160;#160;#160;#160;6#160;Rest Periods2#160;#160;#160;#160;#160;#160;#160;#160;#160;7#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;8#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;9#160;8/16/2008#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;10#160;#160;#160;#160;#160;#160;#160;#160;#160;=IF#40;#40;#40;#40;#40;F6=#34;January#34;,31,5,If#40;F6= #34;February#34;, 28,5,IF#40;f6=#34;April#34;,30,5#41;#41;#41;#41;#41;#41;#41;#160;#160;11#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;12XXXXXX Company, Inc.#160;#160;#160;#160;#160;13Flight Time Limits Worksheet#160;#160;#160;#160;#160;14Name#58;joe bob#160;Month#58;AprilYear#58;2008#160;08/2008#160;#160;#160;15#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;16This form is to be used as a worksheet for pilots to determine their hours of availability for the next month. #160;The form should be completed on the pilot#39;s last duty day of the month and submitted to the Chief Pilot. The Chief Pilot will use the informati#160;#160;#160;#160;flight times17#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;18#160;MonthYear#160;#160;#160;Hours#160;#160;31-Jan-20073110.519A.May2007Complete lines A thru K starting eleven months prior to this month. #160;Include all Commercial flight time.1A23.3#160;#160;28-Feb-20072820.920B.June20071B17.1#160;#160;31-Mar-20073112.321C.July20071C25.2#160;#160;30-Apr-20073023.322D.August20071D25.0#160;#160;31-May-20073117.123E.September20071E4.0#160;#160;30-Jun-20073025.224F.October20071F15.2#160;#160;31-Jul-2007312525G.November20071G26.4#160;#160;31-Aug-200731426H.December20071H7.0#160;#160;30-Sep-20073015.227I.January20081I19.3#160;#160;31-Oct-20073126.428J.February20081J0.0#160;#160;30-Nov-200730729K.March20081K1.3#160;206.231-Dec-20073119.330L.April2008#160;#160;1L0.0#160;#160;#160;#160;#160;312Maximum hours for a 12 month period.21400#160;#160;31-Jan-2008310323Total lines 1A thru 1K.3163.8#160;#160;29-Feb-2008291.3334Subtract line 3 from line 2.41236.2#160;#160;31-Mar-2008310345Maximum hours for two consecutive quarters.5800#160;#160;30-Apr-2008305.5356Total line 1G thru 1K.654#160;#160;31-May-2008310367Subtract line 6 from line 5.7746#160;#160;30-Jun-2008302.3378Maximum hours for this quarter.8500#160;#160;31-Jul-2008311.5389Total lines 1J thru 1K.91.3#160;#160;31-Aug-20083103910Subtract line 9 from line 8.10498.7#160;#160;30-Sep-20083004011Enter the lesser of line 4, 7 or 10.11498.7#160;#160;31-Oct-2008310Spreadsheet FormulasCellFormulaF3=EOMONTH#40;D3-40#41;C9=TODAY#40;#41;K12=IF#40;AB183#62;0,#34;DUE#34;,IF#40;AF183#62;0,#34;CHECK#34;, #34;#34;#41;#41;C14=D2F14=D3H14=D5J14=C9L18=K18M18=SUM#40;N18#5818#41;C19=DATE#40;YEAR#40;C20#41;,MONTH#40;C20#41;-1,1#41;D19=YEAR#40;C19#41;H19=VLOOKUP#40;C19,$K$18#58;$M$94,3,TRUE#41;L19=K19M19=SUM#40;N19#5819#41;C20=DATE#40;YEAR#40;C21#41;,MONTH#40;C21#41;-1,1#41;D20=YEAR#40;C20#41;H20=VLOOKUP#40;C20,$K$18#58;$M$94,3,TRUE#41;L20=K20M20=SUM#40;N20#5820#41;C21=DATE#40;YEAR#40;C22#41;,MONTH#40;C22#41;-1,1#41;D21=YEAR#40;C21#41;H21=VLOOKUP#40;C21,$K$18#58;$M$94,3,TRUE#41;L21=K21M21=SUM#40;N21#5821#41;C22=DATE#40;YEAR#40;C23#41;,MONTH#40;C23#41;-1,1#41;D22=YEAR#40;C22#41;H22=VLOOKUP#40;C22,$K$18#58;$M$94,3,TRUE#41;L22=K22M22=SUM#40;N22#5822#41;C23=DATE#40;YEAR#40;C24#41;,MONTH#40;C24#41;-1,1#41;D23=YEAR#40;C23#41;H23=VLOOKUP#40;C23,$K$18#58;$M$94,3,TRUE#41;L23=K23M23=SUM#40;N23#5823#41;C24=DATE#40;YEAR#40;C25#41;,MONTH#40;C25#41;-1,1#41;D24=YEAR#40;C24#41;H24=VLOOKUP#40;C24,$K$18#58;$M$94,3,TRUE#41;L24=K24M24=SUM#40;N24#5824#41;C25=DATE#40;YEAR#40;C26#41;,MONTH#40;C26#41;-1,1#41;D25=YEAR#40;C25#41;H25=VLOOKUP#40;C25,$K$18#58;$M$94,3,TRUE#41;L25=K25M25=SUM#40;N25#5825#41;C26=DATE#40;YEAR#40;C27#41;,MONTH#40;C27#41;-1,1#41;D26=YEAR#40;C26#41;H26=VLOOKUP#40;C26,$K$18#58;$M$94,3,TRUE#41;L26=K26M26=SUM#40;N26#58;P26#41;C27=DATE#40;YEAR#40;C28#41;,MONTH#40;C28#41;-1,1#41;D27=YEAR#40;C27#41;H27=VLOOKUP#40;C27,$K$18#58;$M$94,3,TRUE#41;L27=K27M27=SUM#40;N27#58;P27#41;C28=DATE#40;YEAR#40;C29#41;,MONTH#40;C29#41;-1,1#41;D28=YEAR#40;C28#41;H28=VLOOKUP#40;C28,$K$18#58;$M$94,3,TRUE#41;L28=K28M28=SUM#40;N28#58;P28#41;C29=DATE#40;YEAR#40;C30#41;,MONTH#40;C30#41;-1,1#41;D29=YEAR#40;C29#41;H29=VLOOKUP#40;C29,$K$18#58;$M$94,3,TRUE#41;J29=SUM#40;M18#58;M29#41;L29=K29M29=SUM#40;N29#58;P29#41;C30=D3H30=VLOOKUP#40;C30,$K$18#58;$M$94,3,TRUE#41;L31=K31M31=SUM#40;N31#58;P31#41;H32=SUM#40;H19#58;H29#41;L32=K32M32=SUM#40;N32#58;P32#41;H33=H31-H32L33=K33M33=SUM#40;N33#58;P33#41;L34=K34M34=SUM#40;N34#58;P34#41;H35=SUM#40;H25#58;H29#41;L35=K35M35=SUM#40;N35#58;P35#41;H36=H34-H35L36=K36M36=SUM#40;N36#58;P36#41;L37=K37M37=SUM#40;N37#58;P37#41;H38=SUM#40;H28#58;H29#41;L38=K38M38=SUM#40;N38#58;P38#41;H39=H37-H38L39=K39M39=SUM#40;N39#58;P39#41;H40=MINA#40;H33,H36,H39#41;L40=K40M40=SUM#40;N40#58;P40#41; Excel tables to the web #62;#62; Excel Jeanie HTML 4
I also had to add line 30 to get information from D3, to set up c29
If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
Leave the 4th value of VLOOKUP = FALSE, for this worksheet, NA is better than a wrong value
The value you are looking up (in column C) is the first of the month; the value in the table you are comparing it to (in column K) is the last of the month.
Change the formula in C30 to be this:
=DATE(YEAR(D3),MONTH(D3)+1,1)-1
and formatted as "mmmm"
this will make the date in column C the last day of the month that is in D3
Change the formula in C29 to be this:
=DATE(YEAR(C30),MONTH(C30),1)-1
and formatted as "mmmm"
this will make the date in C29 the last day of the month prior to the month in C30. Copy this formula up through C19
This should give you the correct values for the VLOOKUP in column H.
D30 should be
=YEAR(C30)
IRT "with the fewest inputs required" the following is submitted:
D4 could be set to:
=DATE(YEAR(D3),MONTH(D3)+1,1)-DATE(YEAR(D3),MONTH(D3),1)
D5 could be set to:
=YEAR(D3)
Make K17 the date the pilot started with the company, then
K18: =DATE(YEAR(K17),MONTH(K17)+1,1)-1
L18: =DAY(K18)
K19: =DATE(YEAR(K18),MONTH(K18)+2,1)-1
L19: =DAY(L19)
Copy K19 amp; L19 down to the end of the lookup table (20 year pilots => 240 rows)
I would lengthen or highlight the range of your lookup table ($J$19:$K$94) so that data is not recorded outside the lookup area.
Why is K30:M30 blank? If you need to distinguish blocks years, you can use conditional formatting to color alternating years (or quarters) with a different background color.
Hope this helps.
K30:M30 is blank because that row gets hidden. I could not make C29 equal D3 - one month (previous). So I made a row and hid it.
The form as it prints has to remain the same as it is "approved" I am not sure if I can convince the boss to sumit a change.
I will make the formula changes,
Thanks for your help.
Thanks I have made the changes, And you are correct N/A is better then the wrong number. H28 and up (down?) shows N/A and I haven't figured out my error.
H29 amp; 30 work. I would like to remove line 30 But C29 needs to be one month less then D3.test (2) #160;BCDEFGHIJKLM2#160;Pilot Namejoe bob#160;#160;#160;#160;#160;#160;#160;#160;#160;3#160;Month15 Apr 08#160;#N/A#160;#160;#160;#160;#160;#160;#160;4#160;days in month30#160;#160;#160;#160;#160;#160;#160;#160;#160;5#160;Year2008#160;#160;#160;#160;#160;#160;#160;#160;#160;6#160;Rest Periods2#160;#160;#160;#160;#160;#160;#160;#160;#160;7#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;8#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;9#160;8/16/2008#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;10#160;#160;#160;#160;#160;#160;#160;#160;#160;=IF#40;#40;#40;#40;#40;F6=#34;January#34;,31,5,If#40;F6= #34;February#34;, 28,5,IF#40;f6=#34;April#34;,30,5#41;#41;#41;#41;#41;#41;#41;#160;#160;11#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;12XXXXXX Company, Inc.#160;#160;#160;#160;#160;13Flight Time Limits Worksheet#160;#160;#160;#160;#160;14Name#58;joe bob#160;Month#58;AprilYear#58;2008#160;08/2008Start Date#160;#160;15#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;16This form is to be used as a worksheet for pilots to determine their hours of availability for the next month. #160;The form should be completed on the pilot#39;s last duty day of the month and submitted to the Chief Pilot. The Chief Pilot will use the informati#160;#160;1/1/2007#160;flight times17#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;18#160;MonthYear#160;#160;#160;Hours#160;#160;31-Jan-20073110.519A.May2007Complete lines A thru K starting eleven months prior to this month. #160;Include all Commercial flight time.1A#N/A#160;#160;28-Feb-20072820.920B.June20071B#N/A#160;#160;31-Mar-20073112.321C.July20071C#N/A#160;#160;30-Apr-20073023.322D.August20071D#N/A#160;#160;31-May-20073117.123E.September20071E#N/A#160;#160;30-Jun-20073025.224F.October20071F#N/A#160;#160;31-Jul-2007312525G.November20071G#N/A#160;#160;31-Aug-200731426H.December20071H#N/A#160;#160;30-Sep-20073015.227I.January20081I#N/A#160;#160;31-Oct-20073126.428J.February20081J#N/A#160;#160;30-Nov-200730729K.March20081K0.0#160;206.231-Dec-20073119.330L.April2008#160;#160;1L5.5#160;#160;#160;#160;#160;312Maximum hours for a 12 month period.21400#160;#160;31-Jan-2008310323Total lines 1A thru 1K.3#N/A#160;#160;29-Feb-2008291.3334Subtract line 3 from line 2.4#N/A#160;#160;31-Mar-2008310345Maximum hours for two consecutive quarters.5800#160;#160;30-Apr-2008305.5356Total line 1G thru 1K.6#N/A#160;#160;31-May-2008310367Subtract line 6 from line 5.7#N/A#160;#160;30-Jun-2008302.3378Maximum hours for this quarter.8500#160;#160;31-Jul-2008311.5389Total lines 1J thru 1K.9#N/A#160;#160;31-Aug-20083103910Subtract line 9 from line 8.10#N/A#160;#160;30-Sep-20083004011Enter the lesser of line 4, 7 or 10.11#N/A#160;#160;31-Oct-2008310Spreadsheet FormulasCellFormulaF3=EOMONTH#40;D3-40#41;D4=DATE#40;YEAR#40;D3#41;,MONTH#40;D3#41;+1,1#41;-DATE#40;YEAR#40;D3#41;,MONTH#40;D3#41;,1#41;D5=YEAR#40;D3#41;C9=TODAY#40;#41;K12=IF#40;AB183#62;0,#34;DUE#34;,IF#40;AF183#62;0,#34;CHECK#34;, #34;#34;#41;#41;C14=D2F14=D3H14=D5J14=C9K18=DATE#40;YEAR#40;K16#41;,MONTH#40;K16#41;+1,1#41;-1L18=DAY#40;K18#41;M18=SUM#40;N18#58;P18#41;C19=DATE#40;YEAR#40;C20#41;,MONTH#40;C20#41;-1,1#41;D19=YEAR#40;C19#41;H19=VLOOKUP#40;C19,$K$18#58;$M$94,3,FALSE#41;K19=DATE#40;YEAR#40;K18#41;,MONTH#40;K18#41;+2,1#41;-1L19=DAY#40;K19#41;M19=SUM#40;N19#58;P19#41;C20=DATE#40;YEAR#40;C21#41;,MONTH#40;C21#41;-1,1#41;D20=YEAR#40;C20#41;H20=VLOOKUP#40;C20,$K$18#58;$M$94,3,FALSE#41;K20=DATE#40;YEAR#40;K19#41;,MONTH#40;K19#41;+2,1#41;-1L20=DAY#40;K20#41;M20=SUM#40;N20#58;P20#41;C21=DATE#40;YEAR#40;C22#41;,MONTH#40;C22#41;-1,1#41;D21=YEAR#40;C21#41;H21=VLOOKUP#40;C21,$K$18#58;$M$94,3,FALSE#41;K21=DATE#40;YEAR#40;K20#41;,MONTH#40;K20#41;+2,1#41;-1L21=DAY#40;K21#41;M21=SUM#40;N21#58;P21#41;C22=DATE#40;YEAR#40;C23#41;,MONTH#40;C23#41;-1,1#41;D22=YEAR#40;C22#41;H22=VLOOKUP#40;C22,$K$18#58;$M$94,3,FALSE#41;K22=DATE#40;YEAR#40;K21#41;,MONTH#40;K21#41;+2,1#41;-1L22=DAY#40;K22#41;M22=SUM#40;N22#58;P22#41;C23=DATE#40;YEAR#40;C24#41;,MONTH#40;C24#41;-1,1#41;D23=YEAR#40;C23#41;H23=VLOOKUP#40;C23,$K$18#58;$M$94,3,FALSE#41;K23=DATE#40;YEAR#40;K22#41;,MONTH#40;K22#41;+2,1#41;-1L23=DAY#40;K23#41;M23=SUM#40;N23#58;P23#41;C24=DATE#40;YEAR#40;C25#41;,MONTH#40;C25#41;-1,1#41;D24=YEAR#40;C24#41;H24=VLOOKUP#40;C24,$K$18#58;$M$94,3,FALSE#41;K24=DATE#40;YEAR#40;K23#41;,MONTH#40;K23#41;+2,1#41;-1L24=DAY#40;K24#41;M24=SUM#40;N24#58;P24#41;C25=DATE#40;YEAR#40;C26#41;,MONTH#40;C26#41;-1,1#41;D25=YEAR#40;C25#41;H25=VLOOKUP#40;C25,$K$18#58;$M$94,3,FALSE#41;K25=DATE#40;YEAR#40;K24#41;,MONTH#40;K24#41;+2,1#41;-1L25=DAY#40;K25#41;M25=SUM#40;N25#58;P25#41;C26=DATE#40;YEAR#40;C27#41;,MONTH#40;C27#41;-1,1#41;D26=YEAR#40;C26#41;H26=VLOOKUP#40;C26,$K$18#58;$M$94,3,FALSE#41;K26=DATE#40;YEAR#40;K25#41;,MONTH#40;K25#41;+2,1#41;-1L26=DAY#40;K26#41;M26=SUM#40;N26#58;P26#41;C27=DATE#40;YEAR#40;C28#41;,MONTH#40;C28#41;-1,1#41;D27=YEAR#40;C27#41;H27=VLOOKUP#40;C27,$K$18#58;$M$94,3,FALSE#41;K27=DATE#40;YEAR#40;K26#41;,MONTH#40;K26#41;+2,1#41;-1L27=DAY#40;K27#41;M27=SUM#40;N27#58;P27#41;C28=DATE#40;YEAR#40;C29#41;,MONTH#40;C29#41;-1,1#41;D28=YEAR#40;C28#41;H28=VLOOKUP#40;C28,$K$18#58;$M$94,3,FALSE#41;K28=DATE#40;YEAR#40;K27#41;,MONTH#40;K27#41;+2,1#41;-1L28=DAY#40;K28#41;M28=SUM#40;N28#58;P28#41;C29=DATE#40;YEAR#40;C30#41;,MONTH#40;C30#41;,1#41;-1D29=YEAR#40;C29#41;H29=VLOOKUP#40;C29,$K$18#58;$M$94,3,FALSE#41;J29=SUM#40;M18#58;M29#41;K29=DATE#40;YEAR#40;K28#41;,MONTH#40;K28#41;+2,1#41;-1L29=DAY#40;K29#41;M29=SUM#40;N29#58;P29#41;C30=DATE#40;YEAR#40;D3#41;,MONTH#40;D3#41;+1,1#41;-1D30=YEAR#40;C30#41;H30=VLOOKUP#40;C30,$K$18#58;$M$94,3,FALSE#41;K31=DATE#40;YEAR#40;K29#41;,MONTH#40;K29#41;+2,1#41;-1L31=DAY#40;K31#41;M31=SUM#40;N31#58;P31#41;H32=SUM#40;H19#58;H29#41;K32=DATE#40;YEAR#40;K31#41;,MONTH#40;K31#41;+2,1#41;-1L32=DAY#40;K32#41;M32=SUM#40;N32#58;P32#41;H33=H31-H32K33=DATE#40;YEAR#40;K32#41;,MONTH#40;K32#41;+2,1#41;-1L33=DAY#40;K33#41;M33=SUM#40;N33#58;P33#41;K34=DATE#40;YEAR#40;K33#41;,MONTH#40;K33#41;+2,1#41;-1L34=DAY#40;K34#41;M34=SUM#40;N34#58;P34#41;H35=SUM#40;H25#58;H29#41;K35=DATE#40;YEAR#40;K34#41;,MONTH#40;K34#41;+2,1#41;-1L35=DAY#40;K35#41;M35=SUM#40;N35#58;P35#41;H36=H34-H35K36=DATE#40;YEAR#40;K35#41;,MONTH#40;K35#41;+2,1#41;-1L36=DAY#40;K36#41;M36=SUM#40;N36#58;P36#41;K37=DATE#40;YEAR#40;K36#41;,MONTH#40;K36#41;+2,1#41;-1L37=DAY#40;K37#41;M37=SUM#40;N37#58;P37#41;H38=SUM#40;H28#58;H29#41;K38=DATE#40;YEAR#40;K37#41;,MONTH#40;K37#41;+2,1#41;-1L38=DAY#40;K38#41;M38=SUM#40;N38#58;P38#41;H39=H37-H38K39=DATE#40;YEAR#40;K38#41;,MONTH#40;K38#41;+2,1#41;-1L39=DAY#40;K39#41;M39=SUM#40;N39#58;P39#41;H40=MINA#40;H33,H36,H39#41;K40=DATE#40;YEAR#40;K39#41;,MONTH#40;K39#41;+2,1#41;-1L40=DAY#40;K40#41;M40=SUM#40;N40#58;P40#41; Excel tables to the web #62;#62; Excel Jeanie HTML 4 |
Post at 2011-5-24 15:12
| This Author's Posts Only
