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!

 

[Sandeep Warrier]

Hi & welcome to the Board!

The {} means that it is an array formula. To array enter a formula, you need to press Ctrl+Shift+Enter instead of just enter as you would usually do.

In any case, you do not require an array formula in this case.

This should also work (using just Enter)

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

 

[computerman0219]

Thanks that worked!!!!

I have one more question how do you make one color if its an "NO" answer and one color for an "YES" answer?

ex

=IF(A10>=6,"YES" (GREEN),"NO" (RED)

THANKS FOR THE HELP AND QUICK ANSWER!

 

[Sandeep Warrier]

Have a look at conditional formatting.

See http://www.contextures.com/xlcondFormat01.html

 

[computerman0219]

Great! Thanks!

 

[computerman0219]

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!

 

[pdxstarflyer]

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.

 

[pdxstarflyer]

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?

I just noticed the second part of your question. You really should check out Google Docs. You can import whatever you have made so far in Excel, but it also has easy templates for you to make a form for entering your data into the spreadsheet. And again, you can do it online from anywhere. If you want to export your spreadsheet to keep a back up or hard copy now and then you can do that too.

 

[pdxstarflyer]

Sandeep,

Is there a way to calculate a value based on calendar months rather than days? For example, 90 days in the past is different than 3 months into the past as, depending on the month 3 months can equal 90 days (jan, feb, mar), 89 days (feb, mar, apr), 92 days (mar, apr, may) or 91 days (apr, may, june... or a leap year).

In aviation some things are tracked using a calendar month concept rather than a 30-day month concept. I tried a few ideas off the top of my head to adjust your formula using the today() function, but I couldn't find anything that worked.

 

[pdxstarflyer]

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

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

That should give past totals based on the month rather than day.

I know it might seem like splitting hairs, but it's the difference between actually being current according to regulations or being in violation.