In principle, the formula is pretty simple, you just need to break it down into parts. It starts like this:

=IF(AND(condition1,condition2,condition3),"X","At least 1 out of date training")

where condition1 is all values in E:O must be future dated, condition2 is P must be after the first of the month, and condition3 is Q:S are dates (non-empty cells). The AND requires all conditions to be true to get an overall true value, which would return the X.

Condition1 starts out as

COUNTIF(range1,"<"&TODAY())

Again, pretty simple. It just counts all the cells in range1 that have a date less than today. TODAY() requires the () even though there are no parameters, just for consistency with other functions. Now here's where it starts to look complicated. range1 is in another workbook. In order to refer to a range in another workbook, you need to give the whole address of the workbook, sheet name, and range. That's done like this:

'[Training tracker.xlsx]Training'!$A$5:$A$15

where the spreadsheet is in [], and the spreadsheet and sheet name is in two single quotes and the range on the sheet must follow a ! sign. In this particular formula, it's even worse, since we don't know exactly where the range is. We know the overall range ($E$5:$O$15), but we need to just look at the row corresponding to the pilot name. INDEX can help us out.

INDEX($E$5:$O$15,2,0)

The last 2 parameters here usually tell us how far down and across to go in the given range, so 2,3 would be 2 rows down and 3 columns across, so 2,3 would give us G6. But if you give it a 0 in the columns parameter, it returns all the columns of the initial range, so 2,0 gives us E6:O6. So we use MATCH to give us the row we want, then we use INDEX to give us the specific range within the overall range, then we use COUNTIF to count the dates less than today. The fact that we have to use the remote reference version of the range ('[Training tracker.xlsx]Training'!$A$5:$A$15) instead of just $A$5:$A$15 makes it looks much more complicated than it is.

Incidentally, I had to use the MATCH function 3 times in the formula, once for each condition. If you could use a helper column, you could put the MATCH function in it, then refer to that helper column 3 times in the formula and shorten it a fair amount, and make it more efficient.

Allowing for all the INDEX/MATCH/Remote range manipulations, the second condition is just:

cell>(EOMONTH(TODAY(),-1)

we find the cell from the training workbook, and make sure it's after the end-of-month date from the previous month. EOMONTH does that, given the -1 parameter.

And the third condition just works out to:

COUNT(range)=3

COUNT tells us how many numeric values are in the range, and dates are numbers.

So overall, the process is to organize your sheet in a logical fashion, keeping similar types of data together (which you found out when you rearranged some columns), figure out what rules you want to apply, then figure out what functions handle those rules. A lot of that is just experience, if you didn't know the EOMONTH function existed, you'd have a tougher time figuring out how to apply that rule. The whole formula looks complicated, but if you just concentrate on one piece at a time, it's not so bad.

I hope this helped! Let me know if you have any more questions.