Find last date visited

Trying to find the last date visited for the week based on text entered in F8 to L8. The problem is with "NWX" note - no visit due to weather. The formula in E8 sees this as a visit and changes the date as the last cell with data entered. Based on the data E8 would show the date listed above last visited cell in F7 to L7.

Example
1/11 1/12 1/13 1/14 1/15 1/16 1/17
JP JP NWX MJ MJ NWX NWX

So the Date of Last Visit should show 1/15

Excel 2003 doesn't support xlsx extension so update your profile about your real excel version

Hi
Use in E8 the following array formula (use CTRL+Shift+ENTER to enter the array formula)

Formula:

Please Login or Register  to view this content.

If you can use Excel 2010 or a more recente version of Excel try this regular formula

Formula:

Please Login or Register  to view this content.

I have updated the version. Thank you for the headsup.

Originally Posted by daddylonglegs

Try this formula in E8

=LOOKUP(2,1/(F8:L8<>"")/(F8:L8<>"NWX"),F7:L7)

Thanks the LOOKUP Formula that works, but with one issue. Sorry I didn't mention it before. The E8 cell starts the week off showing the hard entered dated from cell S8. So the issue is that if there are only NWX entered it comes back as #NA instead of the last date from cell S8.

What needs to be tweeked?

Originally Posted by wackonla

Thanks the LOOKUP Formula that works, but with one issue. Sorry I didn't mention it before. The E8 cell starts the week off showing the hard entered dated from cell S8. So the issue is that if there are only NWX entered it comes back as #NA instead of the last date from cell S8.

What needs to be tweeked?

I think I may have figured out the #NA problem with the IF(ISNA added to the above LOOKUP formula. Thank you for your help.

Originally Posted by daddylonglegs

OK, you can add an IFERROR function to cater for that, I.e.

=IFERROR(LOOKUP(2,1/(F8:L8<>"")/(F8:L8<>"NWX"),F7:L7),S8)

Thank you very much. I'm going to use that one. It's cleaner, less of a change of me missing a cell change in the formula.