Month Identification

**jkelly228** · 02-02-2011, 12:57 AM

Hello All,

This is very similar to another question I asked previously, but with some differences.

I have a column of data with a date in the format:

Jan 11, 2011
Feb 10, 2011
Dec 5, 2010
Nov 12, 2010
Mar 5, 2011

Etc

I need to identify the month and compare it to the current month So the output would be

Jan 11, 2011 -> Past Due
Feb 10, 2011 -> Current Month
Dec 5, 2010 -> Past Due
Nov 12, 2010 -> Past Due
Mar 5, 2011 -> CM+1

I think the code would look something like this

Please Login or Register  to view this content.

**DonkeyOte** · 02-02-2011, 04:09 AM

You could adjust the dates and apply a DateDiff calculation, or you could convert dates to numeric yymm value for comparison in your Select Case

Below uses an alternative approach - utilising Evaluate to update all entries simultaneously by applying the same yymm logic outlined above

Please Login or Register  to view this content.

modify sheet reference as nec.

**johnjohns** · 02-02-2011, 06:19 AM

Perhaps this formula also. If the date is in cell 'A1'

Please Login or Register  to view this content.

**jkelly228** · 02-02-2011, 11:45 AM

DonkeyOte,

I had to modify just a little the last 2 "_" underscores the program didn't like, so I removed those and it worked perfect. Not sure what the purpose of those are, but removing them didn't seem to alter the functionality.

In your code you have "-9.99E+307,0,1,2,3", what does this mean. As I am trying to learn as much as I can I want to make sure that I can duplicate this again if necessary. Thank you.

Johnjohns,

There was a missing ")" according to my program and I couldn't figure out where it was. I liked it because it was condense to one line, but ultimately I couldn't get it to work.

Thank you both for your help.

Kelly

**DonkeyOte** · 02-02-2011, 12:01 PM

Please Login or Register  to view this content.

the above will be lower than any number generated by the date vs date calculation: yymm - yymm

the integer result of the yymm - yymm calculation is applied to the lookup_vector:

Please Login or Register  to view this content.

and the associated value from the result vector is returned

Please Login or Register  to view this content.

so where result is:

< 0 ..... Past Due
0 .... Current Month
1 .... CM+1
2 .... CM+2
3+ .... Beyond

**DonkeyOte** · 02-02-2011, 12:57 PM

edit: just realised that this approach is fatally flawed - oh dear - not a good day!

yymm - yymm is not valid for this type of calculation given year cross overs and the need to separate 1 | 2 and 3+

Consider today is Dec 10th 2011 and the date being compared is 4th Dec 2012:

1201-1112

would result in "Beyond" rather than "CM+1"

I will post back shortly with a solution that actually works

**DonkeyOte** · 02-02-2011, 01:20 PM

In light of above...

Select Case approach:

Please Login or Register  to view this content.

Evaluate route:

Please Login or Register  to view this content.

Apologies for confusion caused - I should have thought that through.

**johnjohns** · 02-03-2011, 01:18 AM

Hi Kelly,

This is what I did. See the attachment

**DonkeyOte** · 02-03-2011, 03:56 AM

@johnjohns, that approach would fail for the exact same reasons as my initial suggestion - outlined in post # 6
ie were TODAY() say 10th Dec 2010 your results for Jan 2011 dates would be incorrect

Formula wise - alternative to that suggested in evaluate:

Please Login or Register  to view this content.

or per Evaluate

Please Login or Register  to view this content.

**johnjohns** · 02-03-2011, 04:08 AM

I tried with 10th Dec 2010 and you are right DonkeyOte. Sorry for the confusion.

**jkelly228** · 02-03-2011, 11:40 AM

Thank you both. I really appreciate the help and the follow up. I would never have realized it till it bit me towards the middle or Q4.

If you guys have time I would appreciate help with a VLOOKUP issue that I have been having. I have posted the code and a sample sheet in a different posting titled "Dynamic VLOOKUP".

Thank you both again.

Kelly

Month Identification

LinkBack

Thread Tools

Rate This Thread

Display

Month Identification

Re: Month Identification

Re: Month Identification

Re: Month Identification

Re: Month Identification

Re: Month Identification

Re: Month Identification

Re: Month Identification

Re: Month Identification

Re: Month Identification

Re: Month Identification

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions