Help figuring out how to automate a pattern

Hi all,

I'm working on a project for work and there's thousands of rows of data. The issue I'm dealing with involves two columns "date" and "month": Here is an example:

Date	Month
8/10/2015	1
9/7/2015	2
9/19/2015	2
10/20/2015	3
11/8/2015	4
12/9/2015	5
12/15/2015	5
12/28/2015	5
1/28/2016	6
2/7/2016	7
2/13/2016	7
2/28/2016	7
3/25/2016	8
4/10/2016	9
5/8/2016	10
5/15/2016	10
5/30/2016	10
5/31/2016	10
and so on...	and so forth...

So basically I want the "month" column to count 1,2,3,4 (more like "1,1,1,1,1,2,2,2,2,3,3,3,3,3,3,3,3,4,4......) consecutively into infinity with corresponding changes in the "date" columns month. Because there may be multiple dates listed for certain months, there may be more 1's than 2's, etc etc. So in the example above, all of August 2015 is labeled "1" while September 2015 is labeled "2" and so on, and if the year changes from 2015 to 2016 then the count continues and does not restart. Anyone have any input or advice on how to accomplish this?

Currently, I am just filling in the "month" column by typing in 1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4 manually but I would love to automate this process if possible.

Thank you for any help! You guys are amazing here!

Originally Posted by Glenn Kennedy

What should a date in August 2016 be? 1 or 13?

Hi Glen,

So that depends on whether there is any data for June and July of 2016. In the example I gave, the last month with data is May 2016 which is labeled as month 10. If August 2016 was immediately the next data provided, then it would be labled as month 11 (the next month in the consecutive order). But if there were dates for June 2016 and July 2016 before August 2016, then June 2016 would be labeled month 11, July 2016 would be 12, and August 2016 would be month 13 yes. Hope that makes sense, let me know if not and I will try to be more specific Thank you again!!!!

Originally Posted by Glenn Kennedy

If it should be 1 again, this will do it:

=IF(MONTH(A1)-7<1,MONTH(A1)+5,MONTH(A1)-7)

Unfortunately it would not be 1 again, but thank you for this input!!! There should not be any months with the same number ever in this circumstance.

Originally Posted by JieJenn

Can you put a helper value in C1 and enter this formula in C2?
=IF(MONTH(A2)<>IFERROR(MONTH(A1),0),C1+1,C1)

FANTASTIC! This worked perfectly. Wow seems so simple. Thank you so much!!!!

Do you mind explaing why/how it works so perfectly? What does the "Month" and "A1" part of the formula do? You rock!

Originally Posted by Glenn Kennedy

How about this (assumes data start in A1):

=DATEDIF(42217, A1,"m")+1

(42217 being the number corresponding to Aug 2014)

Hi Glen,

Jenn's worked! I may try this though just to test it out .. if I do I will get back to you. Thank you again for all of your help.

Originally Posted by Glenn Kennedy

How about this (assumes data start in A1):

=DATEDIF(42217, A1,"m")+1

(42217 being the number corresponding to Aug 2014)

OK you were right about Jenn's formula not working if there is a month missing. I'd like it to even count missing months. so what do you mean by 42217 corresponds with August 2014. Doesn't 201408 (2014-08) correspond with August 2014?