+ Reply to Thread
Results 1 to 6 of 6

Converting a Number to Date and Time

  1. #1
    Registered User
    Join Date
    08-07-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    22

    Converting a Number to Date and Time

    Good morning,

    I have one that's tricking me up a bit. I have some data that is presented in a date code of 201201012354 or (Jan. 1, 2012 at 23:54) and I'm trying to convert the string of numbers to a 01/01/2012 23:54 format but my =text formula has not been working. Anyone have any suggestions that might work.

    201201010354 I want to equal 01/01/2012 03:54
    201201010454 " " 01/01/2012 04:54

    I've tried using =Text(B1,"0000-00-00-00:00")+0 but that has returned the "Value" error.

    Thanks for any help you can provide.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Converting a Number to Date and Time

    Try this version

    =TEXT(B1,"0000-00-00 00\:00")+0
    Audere est facere

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Converting a Number to Date and Time

    Try this assuming the original number is in the format
    yyyymmddhhmm

    =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TEXT(RIGHT(A1,4),"00\:00")

  4. #4
    Registered User
    Join Date
    08-07-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Converting a Number to Date and Time

    Quote Originally Posted by Jonmo1 View Post
    Try this assuming the original number is in the format
    yyyymmddhhmm

    =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TEXT(RIGHT(A1,4),"00\:00")
    Thanks Jonmo.

    I had tried =DATE(YEAR(LEFT(B1,4)),MONTH(MID(B1,5,2)),DAY(MID(B1,7,2))) but when I added the "TIME" function failed because of too many functions.

    The one you gave me worked great.

    Much appreciated.

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Converting a Number to Date and Time

    Glad to help,

    You should try Daddy's solution too. It's pretty slick.

  6. #6
    Registered User
    Join Date
    08-07-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Converting a Number to Date and Time

    Quote Originally Posted by Jonmo1 View Post
    Glad to help,

    You should try Daddy's solution too. It's pretty slick.
    I initially tried it and got a large number in return and thought my data cells showing up as 2.012E+11 was the issue. Then I realized, like an amateur, that the large number it returned was the date in excel format...once I reformatted the cell...worked perfect too.

    Thanks DLL.

+ 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. [SOLVED] Converting this date/time string into number
    By coderboy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-28-2013, 03:54 AM
  2. Excel 2007 : Converting a text date and time into a number
    By laurathomas3 in forum Excel General
    Replies: 1
    Last Post: 12-29-2011, 08:56 AM
  3. Converting 4 digit number to time unit and extrapolating time to run from there
    By shaunsul in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2011, 08:01 PM
  4. Converting a number to Date and Time
    By Fabbyfil in forum Excel General
    Replies: 3
    Last Post: 01-21-2011, 11:03 AM
  5. Replies: 0
    Last Post: 08-23-2005, 12:24 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1