dynamic date range

**weeble33** · 07-08-2013, 12:54 PM

Hello,

A 2-parter, I'm asking in the same thread because they are very similiar:

1.
I have a sheet 2 years of dates, incremented by months as labels across Row 2. The format is 201202, 201203, etc. up to 201401. The idea is in one of the end columns, I have today's data (201307) and from there it's plus or minus to give me +6months on the high end, -18months on the low end. I want a formula that will change all these dates based on the current month. I was able to get today year and month in a format the fits the above scheme:

Please Login or Register  to view this content.

It's the rest of cells I'm having trouble with. I can't just subtract or add from the original cell (lets call it T2, todays date). If I just do a T2-1, then by the time I get to 201301, the next down will be 201300, then 201299, so not exactly proper dates. I tried doing:

Please Login or Register  to view this content.

It's a manual process because dragging the formula doesn't up the +1 qty. However even doing it manually, I still get the issue above where going down in date I get: 201302, 201301, 20130, 2013-01, 2013-02, etc.

2.
So all the above is in Row 2, above each of those date in Row 1, I have year and quarter. So above 201307 is 2013 Q3, above 201306 is 2013 Q2, etc. (standard quarterly system based on calendar year). Any ideas how to get this one to be formulaic as well?

Thank you

**TMS** · 07-08-2013, 01:22 PM

If you use TEXT will get a text value in the cell, not a number representing a date.

Why not use the real date and format it how you want to see it?

=DATE(YEAR(TODAY()), MONTH(TODAY())+1,1) and format as yyyymm

That will give you a true date so, when you add or subtract 1 it will also be a valid data.

Regards, TMS

**weeble33** · 07-08-2013, 01:23 PM

Well, I sorta figured out workaround to Part 1.

Please Login or Register  to view this content.

Like before, I have to manually adjust each cell to have +/- after the months function, but at least using the date function, a negative number will subtract from the year, and a positive number over 12 will add to the years. So now this function:

Please Login or Register  to view this content.

will give me the proper 201212. This works, but it still can't be drag and filled. Luckily I'm only working with 24 months...for now.

I still haven't figured out Part 2 though.

**weeble33** · 07-08-2013, 01:26 PM

TMShucks, I'm glad we are sort of on the same page. I can't have a "real" date in the cell even if it's formatted like I need it. The problem is these labels are used in funcitons looking at a column in a data set who's values are the type I've listed; yyyymm. So when these values are looked up, it's not apples to apples

Oh and the values I am looking up are formatted as numbers, but a simple +0 at the end of the formula forces the number format.

**weeble33** · 07-08-2013, 01:42 PM

Alright, I got part 2 as well using the results of Part1. So if 201201 is written in B2, then in B1:

Please Login or Register  to view this content.

I still think there's a more elegant, drag&fill solution to part 1.

**TMS** · 07-08-2013, 01:44 PM

You take your pick. If you want to be able to drag and autofill and get dates, you need to have dates in the cell.

If you put =DATE(YEAR(TODAY()), MONTH(TODAY())+1,1) in a cell and format it as "yyyymm" you will get 201308 which displays as a number ... but that's not the underlying number; it's 41487.

So, if you're trying to match against it, adding 0 to number 201308 won't work.

You need to convert the thing you are looking for to a date. Something like: =MATCH(DATE(LEFT(F1,4),RIGHT(F1,2),1),A:A,0) where cell F1 has 201308.

As long as both things are converted to dates, you should have no problem.

Regards, TMS