+ Reply to Thread
Results 1 to 10 of 10

Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

  1. 08-05-2014, 02:06 PM #1
    jobie804
    jobie804 is offline
    Registered User
    Join Date
    08-05-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    Hi,

    I need help with converting a cell with Number series entered to the format of YMMDD and I need it to convert to MM/DD/YYYY

    Example: In the cell I have 81101 this needs to be converted to 11/01/2008

    Your help will be appreciated.
    Last edited by jobie804; 08-05-2014 at 02:37 PM.
    Reply With QuoteRegister To Reply
  2. 08-05-2014, 02:08 PM #2
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    Are there any dates with a year in 2010+ ?
    Or are they all in the 2000-2009 range?
    Reply With QuoteRegister To Reply
  3. 08-05-2014, 02:11 PM #3
    jobie804
    jobie804 is offline
    Registered User
    Join Date
    08-05-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    They're all for 2008 for this worksheet I'm working on. But there are separate sheets with YYMMDD formats on them as well for 2010-2014.
    Reply With QuoteRegister To Reply
  4. 08-05-2014, 02:11 PM #4
    cbatrody
    cbatrody is offline
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    Please try the following formula in B1, considering that you have the dates in column A

    =DATE(2000+LEFT(A1,1),MID(A1,LEN(A1)-4,2),RIGHT(A1,2))
    Reply With QuoteRegister To Reply
  5. 08-05-2014, 02:16 PM #5
    jobie804
    jobie804 is offline
    Registered User
    Join Date
    08-05-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    81101 is coming up as 9/1/2014 with that formula.
    Reply With QuoteRegister To Reply
  6. 08-05-2014, 02:19 PM #6
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    Try this to accomodate for 1 or 2 digit year.
    It still assumes the year is 2000+

    =(TEXT(RIGHT(A1,4)&2000+LEFT(A1,LEN(A1)-4),"00-00-0000"))+0
    Reply With QuoteRegister To Reply
  7. 08-05-2014, 02:21 PM #7
    cbatrody
    cbatrody is offline
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    =date((2000+left(a1,len(a1)-4)),(mid(a1,len(a1)-3,2)),(right(a1,2)))
    Reply With QuoteRegister To Reply
  8. 08-05-2014, 02:22 PM #8
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    =date(2000+left(text(a2,"00000000"),4),--mid(text(a2,"00000000"),5,2),--right(a2,2))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  9. 08-05-2014, 02:23 PM #9
    GeneralDisarray
    GeneralDisarray is offline
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    That's cause LEN(A1)-4 starts you at the first position (is passing in 81 for month).

    =DATE(2000+LEFT(A1,1),MID(A1,2,2),RIGHT(A1,2))

    That will work if a date like 1/1/2008 was entered as 80101.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  10. 08-05-2014, 02:38 PM #10
    jobie804
    jobie804 is offline
    Registered User
    Join Date
    08-05-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    Quote Originally Posted by GeneralDisarray View Post
    That's cause LEN(A1)-4 starts you at the first position (is passing in 81 for month).

    =DATE(2000+LEFT(A1,1),MID(A1,2,2),RIGHT(A1,2))

    That will work if a date like 1/1/2008 was entered as 80101.
    This worked. Thanks so much for the help!
    Reply With QuoteRegister To Reply
  11. 08-05-2014, 02:38 PM #11
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

    Maybe this

    =DATEVALUE(TEXT(A1,"0000\/00\/00"))

    A
    B
    1
    20141012
    10/12/2014
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to Convert date format ( DD/MM/YYYY) into MM/YYYY
    By PRADEEPB270 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2014, 07:25 AM
  2. [SOLVED] convert MM/DD/YYYY to DD/MM/YYYY while the data format is text
    By Vogelmann in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-18-2012, 02:43 PM
  3. [SOLVED] Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB
    By Boormo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-05-2012, 05:48 PM
  4. Convert cell category from Date to Text, keeping mm/dd/yyyy format
    By chachie22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2011, 05:00 PM
  5. hot to convert a date object into a text (format yyyy-mm-dd)
    By xianwinwin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2007, 12:07 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1