+ Reply to Thread
Results 1 to 4 of 4

Microseconds in Excel - weird time format

  1. #1
    Registered User
    Join Date
    02-07-2019
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    9

    Microseconds in Excel - weird time format

    Hi

    I got a data set where values in time column are in a nasty format that excel doesn't recognise as time , example:

    170123488 - thats 5:01pm 23 seconds 488 microseconds

    Does Excel deal with microseconds anyway ?

    How to make it display as time ?

    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,945

    Re: Microseconds in Excel - weird time format

    No, excel does not deal with microseconds, it does, however, deal with decimals (which may be the same thing here?)

    What you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Tue 20 Aug 2019) is actually 43697

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    So to convert your "time"...
    =TIME(LEFT(A2,2),MID(A2,3,2),MID(A2,6,99)/100)
    This will maintain the decimals, but not show them
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,429

    Re: Microseconds in Excel - weird time format

    The 488 is actually milliseconds, which Excel is happy to display:

    A
    B
    C
    2
    170123488
    17:01:23.488
    B2: =--TEXT(A2, "0\:00\:00\.000")
    3
    Format of B2: h:mm:ss.000
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,945

    Re: Microseconds in Excel - weird time format

    oops my last mid was not quite right, apologies...
    =TIME(LEFT(A2,2),MID(A2,3,2),MID(A2,5,99)/1000)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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