I have created my required result using 5 interim formulas.

It works but I was hoping it could be simplified (rather than merging into one big MEGA formula)

Any help appreciated

Thanks

One way to shorten it is if there was a way (process-wise) to split out the date (in the "Name" column) into 2 distinct fields. It looks like inconsistent data in the "Name" field is a big headache. (i.e. the "full stops" in the last 3 records of the sample).

If the data was already captured as a separate field, it would be easy to do an either-or formula on the data.

Unfortunately, I think nesting your routines into a big, ugly formula might be your best bet with this data source.

One additional concern I have going forward with this process is that you have built a set of formulas that take care of the current crop of data anomalies. Going forward, you might want to evaluate what other pieces of "odd data" might do to your formulas and the downsteam use of that data. For example, if a data element identified itself as occurring on February 31, it looks like your formulas would accept it and keep that bad date. Excel does NOT recognize that as a date, but rather treats it the same as text entered into a numeric defined data cell. Working in some validation routines might be wise, depending on how "big" this process will be.

Good luck!