Identifying Trends, Predicting Dates

Here's an interesting one.

I was asked the other day if it's possible for Excel to spot trends in a list of dates and predict the next possible date.

For example, look at the following dates:

02/07/2012, 10/07/2012, 18/07/2012, 26/07/2012

They represent a Monday, Tuesday, Wednesday and Thursday, with each date falling in a following week. Let's say that they appear in row 1, with Monday's date in cell A1, Tuesdays in cell B1 etc. Is it possible with VBA to spot this trend and enter 03/08/2012, which would be the following Friday, into E1?

The question I was asked was more complex than that, i.e. the dates wouldn't be as straightforwardly progressive as those above, but it's basically the same thing.

If it's not possible, then that's fine, but I thought if anyone's going to know, it's you guys.

Thanks Tom, as a simple solution to a regular pattern that works fine.

However, when dates entered are spaced uniformly it begins the sequence from the beginning. The dataset it would be applied to is large and the dates would be erratic. I was thinking about spotting trends in the dates i.e. the majority of the dates were Mondays, with a couple of weeks between them therefor it's logical that the next date would be this coming Monday.

I think my question is too broad. I'll go away and see if I can narrow it somewhat.

I'm sure this kind of pattern matching is possible in Excel/VBA. Almost certainly it is not something that is 'built in', so you will have to teach Excel/VBA how to do it (and that is probably the hard part).

For the sequence give, if you take the difference of successive dates (example: 10/7-2/7) you will get 8. If you do this for each pair, you will always get 8, and the pattern is easy to see: x(i+1) = 8 + x(i).

It seems to me that the first step in developing a pattern matching algorithm like this is to identify the kinds of patterns you expect to see. Then you can start to build an algorithm that will find those patterns.