Adding if in the last six months

[computerman0219]

Hello everyone I am working on a Pilot Logbook for excel and we have to log 6 approaches every six months to stay current. So what I am trying to do is have the formula look down the date column and if the date is within 6 months from today then add the approaches made within that time frame and if its outside the time frame then do not count them.

I have downloaded someones else logbook for excel and they use this formula

=SUM(IF((FlightLog!A18:A1014>=(TODAY()-180)),FlightLog!G18:G1014))

I noticed though if you click the cell the formula is in it will show the formula but will have an { before and } after the code. If you click on the code the {} will disappear.

I say that to say I used that code (obviously to fit my spreadsheet) and it does not work. here is my code

=(SUM(IF((LOGBOOK!A5:A50>=(TODAY()-180)),LOGBOOK!T5:T50))

If I test it (by putting I did 5 approaches within 6 months) will come up 0 instead of 5. No matter what number I put it it will always = 0.

Any idea?

Thanks in advance!

 

[computerman0219]

I'm sure there is a macro that can do this. I'm actually creating a form and spreadsheet using google docs to make an online logbook that I can update from anywhere. I'm getting tired of having to go thru and separate out my fixed wing time from my rotor wing time every time I have to fill out an 8710! Too bad I didn't know anything about excel 15 years ago when I started. If I get a chance I will see if I can figure out the macro. Shouldn't be too hard.

I too am a BIG fan of using Google Doc (any Google product for that matter) however when I try to upload my logbook to the Google Spreadsheet it does not work. I am guessing that the file is to big (close to 20MB) I have a lot of formulas in there as well. Any suggestions?

Also how did you link a form to the spreadsheet in Google Docs?

 

[pdxstarflyer]

One thing I'm noticing as I get back into spreadsheets and am using google docs for the first time, is that the functions in excel don't always work the same way, or at all, in google docs. However, many funtions are easier to use in google docs than they are in excel... it's a balance. I'd check and see if any of the functions you are trying to import into a google spreadsheet need to be tweaked a bit to work properly there. I don't think file size is an issue. You have over 7500MB of file space available.

About linking the form... create the form first and then google will automatically create the spreadsheet for it. From there you can copy over your info from your existing spreadsheet.

 

[computerman0219]

pdxstarflyer whats your email and I will email my Excel Logbook and see how you like it and see if you would change anything...and maybe you can get it to work on Google Docs.

 

[Sandeep Warrier]

Ok I finally got everything like I want it as far as the columns and formulas go.

Now I two questions to see if anyone of you can help me...

1) Is there a way that I can keep cells from being easily edited after they have data entered into them?? Another words once I fill out a row of information how can I make it so that I dont accidentally change a value? It dont necessarily have to lock the cells just so I dont "fat finger" and change a value.

2) Is there a simple way to make a form or worksheet that will input the data into the main sheet? That way it looks more simpler and that way I am not seeing all the other data when inputing information, only the information needed for that entry?

Thanks in advance!

1. I don't think there is any straight forward way to do this (without locking and sheet protection).

2. You could probably have an "input" worksheet, with specific cells designed to accept input, and then a macro which will transfer the data to the next available row in your main sheet.

For example, if Sheet 1 is used as input (cells A2, B2, A5, D5, E10), and Sheet 2 is where your main data is stored (in columns A to E) then this will transfer the data from sheet1 to sheet2

Sub Test()
    Dim varData As Variant
    Dim rCell As Range, i As Long
    ReDim varData(0 To 4)
    For Each rCell In Sheets("Sheet1").Range("A2,B2,A5,D5,E10")
        varData(i) = rCell.Value
        i = i + 1
    Next rCell
    With Sheets("Sheet2")
        .Range("A" & .Rows.Count).End(xlUp).Offset(1).Resize(, UBound(varData) + 1) = varData
    End With
End Sub

I think I have the calendar month issue worked out...

=SUMIF(FlightLog!A18:A1014,">="&(EDATE(TODAY(),-6)),FlightLog!G18:G1014)

This seems to be the easiest way to get what you require. I was trying out something that didn't use EDATE, but it became a huge formula and didn't return correct results all the time.