I need to convert several alpha-numeric codes into a month and year.
The code format is as follows:
DC22G02292
The first character "D" is ignored. The second charater C represents the year and is defined as A=2007, B=2008, C=2009, D=2010, etc.
The next two characters (22) stand for the week of the year. Charaters 5-10 are ignored.
In this example the month and year would be May 2009.
Is there a way to create a formula in Excel that will make this conversion?
Last edited by Buchu; 06-02-2012 at 11:30 AM.
A number of weeks straddle two months by various numbers of days. What are the rules for weeks relative to months?
I came up with this (assumes you want to start numbering weeks based on Jan 1 of year determined by 2nd character) - format result as mmm yy
=DATE(1942+CODE(MID(A2,2,1)),1,1)+(MID(A2,3,2)-1)*7
The work week calendar vaires somewhat by year, but we can assume the following:
January weeks 1-4
February weeks 5-8
March weeks 9-12
April weeks 13-17
May weeks 18-21
June weeks 22-25
July weeks 26-30
August weeks 31-34
Sepetember weeks 35-39
October weeks 40-43
November weeks 44-47
December weeks 48-52
At first, I thought you were using a 4-4-5 fiscal quarter method...but, evidently not.
Try this:
Is that something you can work with?
Please Login or Register to view this content.
Hi Buchu,
I had your DC22G02292 in C18 - but I got June 2009 with your criteria:
Please Login or Register to view this content.
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Ron - looks like this is going to work just fine! I have one more step to go that is:
I need to calculate the number of months between two different date formats. An example of the first format is :
3/28/2008
The second format is what we determined previousy, only a month and year:
June 2010
Is there an easy way to caluclate the number of months between 3/28/2008 and June 2010?
Thanks!
Last edited by Buchu; 06-02-2012 at 08:50 PM.
Looks like this would work too! - need help running this in excel though.Originally Posted by xladept
Hi Buchu,
I had your DC22G02292 in C18 - but I got June 2009 with your criteria:
Please Login or Register to view this content.
Please Login or Register to view this content.
Hi Buchu,
You need to put the function in a standard module then you can use it just like any other Worksheet function.
Please Login or Register to view this content.Please Login or Register to view this content.
Not sure what that means - do you have an example of how to do this?Hi Buchu,
You need to put the function in a standard module then you can use it just like any other Worksheet function.
Please Login or Register to view this content.Please Login or Register to view this content.
Hello Buchu
right-click on the tab
choose - view code
in the window that will appear click insert menu - choose module
thenpaste the code given above by xladept in the window.close.
then you can use
=MoYr(C18)
btw save the workbook as macro-enabled workbook.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Hi Buchu,
Normal View:
Formula View:Please Login or Register to view this content.
Does this work for you?Please Login or Register to view this content.
Hi,Hi Buchu,
Normal View:
Formula View:Please Login or Register to view this content.
Does this work for you?Please Login or Register to view this content.
Yes thats works thanks! I also discoverd the "DATEDIF" function
=DATEDIF(A1,A2,"m")
This function will subtract the two dates - you can have the result in days "d", months "m" or years "y". Try it out on your birthday to today's date to see how many months or days old you are. LOL!
Hi Buchu,
Thanks for the function - Good Luck!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks