Incrementing dates in yymmdd format based on days of week

For reasons that are far too complex to explain but suffice to say that the input can't be changed I have a spreadsheet that has a column of dates in yymmddd format with no separator so 160525 for example.

Each date needs to be incremented in the next column by different criteria, sometimes it will be just to be the same day of each month, other times it will need to be say the Friday of every week.

Taking the first row to begin with this has the date 160623 in Cell C2 and I need to increment this so that subsequent cells show the 23rd of every month.

So at the moment I've broken down the date in cells E2, F2, G2 to show yy mm dd respectively. I've set the format of these fields to custom 00 in order to preserve the leading 0 for months and days that is needed.

In H2 I've then got this formula: =TEXT(E2,"00")&TEXT(F2+1,"00")&TEXT(G2,"00") Whilst that works once we get to January the date is going to show as 161323 when it should be 170123. I can't work out how to amend this formula to cope with that and preserve the leading 0's where applicable.

Jumping the gun a bit but in subsequent rows I need to change the date from say 160527 to be the Friday of every week thereafter. How can I write a formula to do that when that would require the date to be in a standard date format to begin with and then it needs converting back into a non standard date format??? Head spinning.

It's probably best to convert to proper dates using this:

=DATE(2000+left(C2,2),MID(C2,3,2),RIGHT(C2,2))

Then it will be a lot easier to do the other manipulations that you mention. You can convert back to your format using this:

=TEXT(date_cell,"YYMMDD")

Hope this helps.

Pete

Originally Posted by Pete_UK

It's probably best to convert to proper dates using this:

=DATE(2000+left(C2,2),MID(C2,3,2),RIGHT(C2,2))

Then it will be a lot easier to do the other manipulations that you mention. You can convert back to your format using this:

=TEXT(date_cell,"YYMMDD")

Hope this helps.

Pete

Ahh yes I can certainly see how this would be easier. Thank you, I didn't know you could do that. I'll give that a try.