# Microseconds in Excel - weird time format

1. ## 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. ## 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

=TIME(LEFT(A2,2),MID(A2,3,2),MID(A2,6,99)/100)
This will maintain the decimals, but not show them

3. ## 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

4. ## 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)

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

#### 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