+ 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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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)

Similar Threads

  1. PDF--->Excel weird format result. Trying to fix with VBA
    By dlorg001 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2017, 09:25 AM
  2. Excel 2013 - weird paste format?
    By Nighteg in forum Excel General
    Replies: 2
    Last Post: 06-23-2014, 08:51 AM
  3. [SOLVED] VBA Convert Time from weird format
    By bfs3 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-09-2014, 10:11 PM
  4. Getting rid of weird time format 01m 16s
    By rooonik in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-29-2013, 11:08 AM
  5. Help with Time (microseconds problem)
    By naga in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2013, 02:07 AM
  6. [SOLVED] Time measures in weird format
    By andrewmo in forum Excel General
    Replies: 10
    Last Post: 09-21-2012, 03:41 PM
  7. [SOLVED] i need to format a column as microseconds ? how do i do this
    By SMT in forum Excel General
    Replies: 1
    Last Post: 05-26-2005, 05:15 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