Formulas and Conditional Formatting

[Dooha]

I seem to have some how decided to produce the most complicated spreadsheet in the world (in my head anyway)

I have a formula -

=SUMIF(Flying!B7:B322,">"&EDATE(TODAY(),-9),Flying!L7:L322)

This tells me all the flying I have done in the last 9 months on a separate worksheet.

I have another column O7-O322 which is a remarks column detailing which exercise was carried out on each flight. Example O8 has 'Exercise 17', O9 has 'Exercise 18' written in it. I need to recognise only exercise 18 in column 'O', but for the past 9 months only. Is there anyway I can incorporate the formula above to recognise 'Exercise 18' in column O7-O322, or better still recognising Exercise 18 even if Exercise 18,12, and 19 were written in the same cell.

A slightly easier question -

Im trying to conditionally format a cell where the number turns Green if over 25:00 hrs. I can make it turn Green if the number 25 was in the cell, but when it's in the hours format 25:00, it doesnt recognise it. Not sure how to get around this.

 

[VoG]

For the easy one try

=A1>"25:00"+0

 

[Desu Nota from Columbus]

could you add this to the sumif?

SUMIF(Flying!B7:B322,">"&EDATE(TODAY(),-9), Flying!Find("Exercise 18", O2:O322)>0,Flying!L7:L322)

 

[Dooha]

No luck for me on either of those.

 

[VoG]

CF working here

Excel Workbook
	E	F
7		30:58
8		12:00
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7	1. / Formula is =F7>"25:00"+0	Abc

 

[Dooha]

CF working for me now aswell. Input error on my part, I'm sure you knew that would be the case.

Just got the tough one to crack now.

 

[T. Valko]

Just got the tough one to crack now.

What version of Excel are you using?

 

[Dooha]

2003 atm.

 

[T. Valko]

2003 atm.

You could do something like this...

In Excel 2003 the EDATE function is part of the Analysis ToolPak add-in. When using conditional formatting you can't directly refer to functions in the ATP. So, you need to create this named formula:

• Goto Insert>Name>Define
• Name: NineMonthsAgo
• Refers to: =EDATE(NOW(),-9)

Then, as your conditional formatting formula, you could use something like this:

=AND(B7>NineMonthsAgo,SEARCH("Exercise 18",O7))

 

[Dooha]

I think we are getting somewhere. I have created the named formula, and inserted the other formula. The formula seems work as it diplays as 'True', and 'False' if it is older than 9 months.

So need the formula to add all the hours from L7:L322, that are older than 9 months (taken from B7:B322), and have 'Exercise 18' (taken from O7:O322)

Because of the amount of flying and data involved for all the student pilots, I need this kinda spreadsheet to take the load off and the amount of time and inaccuracies.