There is a pretty solution using an Array formula.

=MAX(IF(1=$A$4:$G$4, $A$3:$G$3, 0))

**How it works:** the conditional statement created an array of {*date* or *0*,...} based on whether there is a 1 in the range A4:G4. If there is, the value above it (*date-value*) is added to the array, otherwise *0* is added. The max function return the highest value in the array.

**Notes:**

**1.** The formulae must be typed in then instead of pressing ENTER, press CTRL-SHIFT-ENTER to specify that it is an array formula. You will know if you have done this correctly as the formula will display curly braces { } at each end. These can **not** be entered manually.

**2.** Ensure that all date cells are formatted to *date-type*. This includes the cell you put your formulae in :)

**3.** There is a potential bug if none of the dates have a 1 under it. The max function will find the largest value (*0*) and display it as a date, something like 1/0/1900.