Filter or Formula for second to last cel

[FlorisCQ]

Dear all, 

I have tried to find a solution for my challenge, however I haven't managed to find the right answer. 

In an Excel sheet, I have multiple rows with data divided over different amounts of columns. The only thing these rows have in common, is that the second to last filled cell in each row has a date (dd/mm/yyyy or dd-mm-yyyy).

I need a formula that shows this date. In orther words, I would need a formula that copies the second to last filled cell of each row into a column.

To visualize my question: looking at the table below; is there a formula (or other function) that automatically copies the dates from the yellow cells into the first (yellow) column? 

Many Thanks!

[#2 · Accepted solution]

@FlorisCQ 

=LOOKUP(9^9,B1:M1)

Did I mention that your data has a bad structure?

In fact I dare to say it has no strucure at all.

[#3]

@FlorisCQ 

For example in A2:

=LOOKUP(1000000, B2:XFD2)

In Dutch:

=ZOEKEN(1000000; B2:XFD2)

Format A2 as a date, then fill down.

[#4]

@Detlef_Lewin Thank you for your help! Seems like your formula works perfectly! 

I know it seems there is no structure; that's why I needed this formula 🙂

thanks for your help! 

[#5]

@FlorisCQ 

A method that will return the penultimate field of each record, irrespective of its content, might be

= BYROW(data, LAMBDA(record, CHOOSECOLS(FILTER(record, record<>""),-2)))

It takes each record in turn and removes the blanks to the right.  Once done, it takes the second rightmost field and returns the results as a column array.