+ Reply to Thread
Results 1 to 10 of 10

Using VBA to return Month and Year from B306 which is June 2013

  1. #1
    Registered User
    Join Date
    06-06-2014
    Posts
    19

    Using VBA to return Month and Year from B306 which is June 2013

    Hi all, I hate to be asking for more help but lo and behold I'm back.

    I have a series of data from our software. The program gives the date as "B###" for example "B306" would be june 2013. The B is meaningless, the first number is the last digit of the year, the second and third numbers represent the month. (I have a theory that B means you are in the 10's and c would be 20's for they year but that isnt relavent here)

    What I am looking for is the most efficient way to put this in a more readable format such as June 2013 or 2013 June.

    I can modify the code to match my real data file (or at least it should be within my capabilities) but here is an example of the output example.xlsx

    I could go and write a messy "if" formula or a very poor, inefficient, code. I think a loop may work here but I am completely inexperienced with them and do not know. If anyone wants to suggest an applicable VBA solution I would be more than willing to research that type of solution and try to code it on my own. (which I would likely bring back to troubleshoot it when I hit a roadblock but i would be willing to try).
    Last edited by sdeaks; 06-25-2014 at 10:35 AM. Reason: Poor Previous Title :(

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Looking for optimal solution to simple problem

    Aren't we all, but unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Using VBA to return Month and Year from B306 which is June 2013

    Title appears to be fixed, here is my duct-tape approach without VBA:

    Please Login or Register  to view this content.
    It can certainly be done cleaner with arrays, but I'm bad at those.
    Won't work after 2019 though

  4. #4
    Registered User
    Join Date
    06-06-2014
    Posts
    19

    Re: Using VBA to return Month and Year from B306 which is June 2013

    My apologies good sir, I hope this title is more suiting. I am not sure if it describes my problem. I have a solution but it is slopy and inefficient so I am not sure if the title should have been more like "VBA code to replace long 'if' statement". I would be more than happy to change again to that if it is needed but I think the current title represents it better.

    Hoping to be past the issue now, I realize I could use a vlookup table over an "if statement" too but that only seems slightly better to me because as time goes on the vlookup table will need to be expanded and updated to reflect something such as the code for June 2015. It is safe to say that the spreadsheet I will be creating here is not going to be in the most experienced excel hands so I would like to try and make it as user friendly as possible.

    Thanks!

  5. #5
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Looking for optimal solution to simple problem

    Title appears to be fixed, here is my duct-tape approach without VBA:

    Please Login or Register  to view this content.
    It can certainly be done cleaner with arrays, but I'm bad at those.
    Won't work after 2019 though
    I would guess B is for the decade.


    To break that down to a more understandable form, CHOOSE function works as such...one input (an index number)determines which result is returned, so: =CHOOSE(Pickanumber , UpickedONE , UpickedTWO ,......) and so on

    The DAY((ABS(RIGHT(A1(,2 says to convert the rightmost two digits into an index number that can be read by the CHOOSE(the ABS part takes the 0 away and makes sure it is read as a number)
    The last half after the 201"&MID basically says to just add the text ", 201" and the index number right after the B, so 201_

    Hope this helps.
    Last edited by Speshul; 06-24-2014 at 05:01 PM.

  6. #6
    Registered User
    Join Date
    06-06-2014
    Posts
    19

    Re: Looking for optimal solution to simple problem

    That is definitely a solution for now, I would assume by 2019 these would be irrelevant anyways (and if they are relevant then well good for me and whomever helps because it means we have done a damn good job). I was actually unfamiliar with choose function but that was interesting to go through and see what you did. I love learning new ways to do things because there is always another application for it. Certainly cleaner than the IF and lasts longer than the vlookup(unless I just make the vlookup a table into the figurative end of time)

    Thanks for breaking it down further, I was not sure how the absolute value had figured in there. Awesome!

  7. #7
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Using VBA to return Month and Year from B306 which is June 2013

    I love choose. I had a choose choose between seven chooses once, each of which was a vlookup. It was fun.

    Here, this should keep you going until 2039, if you find out for sure the letter indicates decade. You can vlookup an array right in the formula so you don't have to worry about placing a little table somewhere to vlookup against. Works well for small tables.

    Please Login or Register  to view this content.
    If you put a { at the start, every time you enter a , excel reads it as next column within the row, but when you use a ; excel reads it as next row

    So, this would be a three by three table (I put cell references in there to visualize it but you can have any value. )

    Please Login or Register  to view this content.
    Last edited by Speshul; 06-24-2014 at 05:13 PM.

  8. #8
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Using VBA to return Month and Year from B306 which is June 2013

    Perhaps can you use next formula with a date format like "mmmm yyyy"
    =DATE(2010+MID(A1,2,1),RIGHT(A1,2)*1,1)
    If you are about the B or C first letter you could use
    =DATE(((LEFT(A1,1)="B")*2010+(LEFT(A1,1)="C")*2020)+MID(A1,2,1),RIGHT(A1,2)*1,1)
    Attached Files Attached Files
    Last edited by PCI; 06-24-2014 at 05:17 PM. Reason: Additional info added

  9. #9
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Using VBA to return Month and Year from B306 which is June 2013

    PCI wins, I think I overthought my formula a bit. haha

    At lease you learned about CHOOSE though right? :D

  10. #10
    Registered User
    Join Date
    06-06-2014
    Posts
    19

    Re: Using VBA to return Month and Year from B306 which is June 2013

    to Speshul: thanks for explaining those things to me. I didn't know much of that ! Your formula certainly did work but I do have to say I think Speshul has a better answer

    To PCI: Thank you vary much, quite the simple solution. I did not think It could be manipulated into working for a date function but hey, Im okay with ebing proven wrong. Well Done.

    And on another note it does work on the real worksheet so no issues there
    Last edited by sdeaks; 06-25-2014 at 10:30 AM. Reason: I am an idiot

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 11
    Last Post: 10-30-2013, 02:11 PM
  2. simple problem with drop down list. need solution
    By singhabhijitkumar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-17-2011, 06:20 AM
  3. solver - not always optimal solution ?
    By przemke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2009, 03:38 PM
  4. Excel Solver giving wrong optimal solution
    By gtg430i in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2008, 02:29 PM
  5. Is there a simple solution?
    By Bobby in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-11-2006, 03:40 PM

Tags for this Thread

Bookmarks

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