If all cells in range contain a date value, then return the highest date

Hi Guys,

I'm more of a VBA man than worksheet formulas so play nice.

I need a worksheet formula for Column F

I have 5 (A-E) columns on a single row where each column represents a task to be completed.
Each column will either store a date in dd/mm/yyyy format, "N/A" or nothing.

If the column is blank: then the task is required, but has not yet been complete
If the column contains "N/A" then the task is not required
If the column contains a date in dd/mm/yyyy format then the task was required and was completed on the date stored in the cell.

A sixth column, "F" is the milestone column which will display the date the final task was completed.
Column F is to show a blank value when any of the columns A-E contain a blank
Column F is to show the highest date when at least one column A-E has a date stored in it
Column F is to show "N/A" if ALL of the columns A-E have "N/A" stored in the cell values

Like I said I could easily complete this with VBA but am wanting to solve the problem using simple worksheet formulas

Kind Regards
Jordan

In F2, try this:

=IF(COUNTIF(A2:E2,"N/A")=5,"N/A",IF(COUNTIF(A2:E2,""),"",MAX(A2:E2)))

Formatted as Date