Help figuring out how to automate a pattern

1. 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!

2. Re: Help figuring out how to automate a pattern

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

3. Re: Help figuring out how to automate a pattern

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

4. Re: Help figuring out how to automate a pattern

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

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

5. Re: Help figuring out how to automate a pattern

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!!!!

6. Re: Help figuring out how to automate a pattern

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.

7. Re: Help figuring out how to automate a pattern

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

(42217 being the number corresponding to Aug 2014)

8. Re: Help figuring out how to automate a pattern

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!

9. Re: Help figuring out how to automate a pattern

Originally Posted by Glenn Kennedy

=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.

10. Re: Help figuring out how to automate a pattern

OK. Just be aware that if there is a month missing from your list, JieJenn's formula may not behave correctly!!

11. Re: Help figuring out how to automate a pattern

Originally Posted by Glenn Kennedy

=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?

12. Re: Help figuring out how to automate a pattern

I suggest this http://www.cpearson.com/Excel/datetime.htm#SerialDates to explain how Excel stores date/time information.

13. Re: Help figuring out how to automate a pattern

Give this one a try. Turns out you don't need the helper value 0
=IF(A2="","",IF(MONTH(A2)<>IFERROR(MONTH(A1),0),C1+IFERROR((YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1),1),C1))

14. Re: Help figuring out how to automate a pattern

No. Excel hasn't a clue about dates. To it, a date is a special formatting that's applied to a number: the number (± fraction) of days since 00 Jan 1900 (yea, don't ask...). So when Excel sees 42217, formatted as a date, YOU see August 1st 2015; being 42217 days sice the zeroth January 1900.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1