I'm assuming you know the column that the invalid date is in. For the example, I'll assume it is column A.
Add a column to your table. In the first non-header row of that column enter a formula that will throw an error when confronted by a non-Date and Time value.
Example:
Column A shows one sign that there is a non-date and time value in Row 9. If no text alignment setting has been set by the user, D&T values (and Numbers) will be aligned to the right , while Text values will be aligned to the left.
The upper formula shown below the table is entered in cell O2 as shown, then filled down to all cells in the column.
When asked to add a Duration value (2 days) to a D&T value, it returns a D&T value with the same format as the one in column A.
When asked to add a Duration value (2 days) to a Text value, it returns an error, which displays the error triangle shown in O9.
The second formula, entered as shown in cell P2, and filled down to the end of that column, used the same A2+DURATION expression (compacted by removing the 'weeks token and all of the commas and tokens after the 2 days)to create an error in the non-D&T row.
The formula passes the error message to ISERROR, and IF uses the resulting True or False to place either the Row number or a 1 in the cell containing that copy of the formula.
At the top of the table, Cell P1 contains a simple formula that gets the Maximum value in column P.
This value will be the number of the furthest down row of the table containing an error in the 'D&T+DURATION expression.
Regards,
Barry
