To create a series of dates in a range, with just weekdays (Monday through Fridays), you first enter your starting date in a cell (B2, in the example below), and then enter the following formula in the cell below that cell.
I think we need to explain the above formula a little bit. First off, when trying to develop formulas of this complexity – do not attempt to write it all at once.
This is what is known as a megaformula – a miniature program – squeezed into a single formula.
When extracting the last name like this – we use the RIGHT function – but it is not much use unless we know where the last name starts. It starts immediately after the last space character. We can find that with the FIND function. Howver FIND works from the left. If we could figure out how many space characters there are – we would be set. We do that by removing the space characters and seeing how much shorter the name is.
The whole procedure is illustrated below..
As an alternative to developing these megaformulas, you could also code this in Open Basic – but for that, we will wait another day.