+ Reply to Thread
Results 1 to 13 of 13

Format issue with changing a number letter combination to a date

  1. #1
    Registered User
    Join Date
    08-23-2014
    Location
    Missouri
    MS-Off Ver
    7
    Posts
    5

    Format issue with changing a number letter combination to a date

    I'm trying to take something that's a date code and change it to a date without having to manually enter almost 300 entries. Help.

    It looks something like:


    12AU14

    I want to format it to be 8-12-14. Thanks!

  2. #2
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Format issue with changing a number letter combination to a date

    You have to enter 12 entries -- the month abbreviations that you use.
    Apart from AU, what's the rest?
    Please click the * below if this helps

  3. #3
    Registered User
    Join Date
    08-15-2014
    Location
    St.Petersburg, Russia
    MS-Off Ver
    MSO Excel 2010
    Posts
    20

    Re: Format issue with changing a number letter combination to a date

    Try this (replace reds with your month abbreviations
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by hohlick; 09-12-2014 at 09:22 AM. Reason: error

  4. #4
    Registered User
    Join Date
    08-23-2014
    Location
    Missouri
    MS-Off Ver
    7
    Posts
    5

    Re: Format issue with changing a number letter combination to a date

    Sorry, it took me so long to reply. So far I have five months entered. AP=April MA=May JU=June JY=July AU=August and so forth. JasperD does this answer what you were asking? It looks to me that the code will remain first two letters except JU and JY.

    Thank you Hohlick I will try that tomorrow morning!

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Format issue with changing a number letter combination to a date

    You can try on thing..
    Try Find and replacing ..
    Each month... ie "JA" with "-01-" and so on ( Without Quotes..)

    I guess that will do the job...
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  6. #6
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Format issue with changing a number letter combination to a date

    Or try this basic code for that...
    Please Login or Register  to view this content.
    This will do it automatically..
    Although it is untested...
    Last edited by Vikas_Gautam; 09-13-2014 at 02:20 AM.

  7. #7
    Registered User
    Join Date
    08-23-2014
    Location
    Missouri
    MS-Off Ver
    7
    Posts
    5

    Re: Format issue with changing a number letter combination to a date

    What do you mean application input box?

    So I would enter starting Sub replace month correct? I'm going to try this now.

  8. #8
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Format issue with changing a number letter combination to a date

    Please Login or Register  to view this content.
    will throw an Input Box where you will select the range you are gonna target for replacement...

    And One important thing..
    Before try the code.. CHANGE the date format of your Computer to DD-MM-YYYY because the position of "AU" is in the middle..
    this will resist the month to become a day...
    Last edited by Vikas_Gautam; 09-12-2014 at 11:34 PM.

  9. #9
    Registered User
    Join Date
    08-23-2014
    Location
    Missouri
    MS-Off Ver
    7
    Posts
    5

    Re: Format issue with changing a number letter combination to a date

    Quote Originally Posted by hohlick View Post
    Try this (replace reds with your month abbreviations
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The word color keeps highlighting as an error. I like this idea. I can't find the error though.

  10. #10
    Registered User
    Join Date
    08-23-2014
    Location
    Missouri
    MS-Off Ver
    7
    Posts
    5

    Re: Format issue with changing a number letter combination to a date

    I don't know how to find the input box in Excel 2010. I have already formated the cells.

  11. #11
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Format issue with changing a number letter combination to a date

    No NewBie..
    You don't need to find the InputBox..

    its a vba code ("Macro" you know..)

    Copy the code..
    Go on to your excel sheet and Press ALT + F11
    and INSERT a Module there ..
    Paste the code there ...
    press F5 to run the code....

    Enter your targeted range..
    click ok...
    thats it..

  12. #12
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Format issue with changing a number letter combination to a date

    Check the attached file..
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    08-15-2014
    Location
    St.Petersburg, Russia
    MS-Off Ver
    MSO Excel 2010
    Posts
    20

    Re: Format issue with changing a number letter combination to a date

    Quote Originally Posted by Newbiedine View Post
    The word color keeps highlighting as an error. I like this idea.
    I wrote this formula in non-English Excel, and then translated it manually. I think that the matter is in array delimiter. Try to change "," between words to ";" or ":", I do not sure which delimiter is correct in English Excel

+ 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: 12
    Last Post: 12-13-2013, 04:29 PM
  2. Changing number to date time format
    By Saw-whet Owl in forum Excel General
    Replies: 6
    Last Post: 12-10-2012, 08:15 PM
  3. Need Validation formula for a Letter Number Combination
    By Talo in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-19-2012, 12:22 PM
  4. Replies: 17
    Last Post: 07-16-2009, 03:12 AM
  5. Format:change headers from letter to number/number to letter
    By Biff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM

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