Format Question

J

Jeremy

What is the best way to convert numbers to time format.

Example
173100 = 5:00:00 PM
70652 =7:06:52 AM
 
C

cm

try this: assuming the number to be converted is in cell c13:

=TIME(TRUNC(C13/10000),TRUNC((C13-TRUNC(C13/10000)*10000)/100),((C13/100)-TRUNC(C13/100))*100)
 
D

David Biddulph

You'll have to explain the logic of your system.

If 173100 had been 5:31:00 PM instead of 5:00:00 PM we would have found it
easier to understand. In that case I would have suggested
=--TEXT(A1,"00\:00\:00") and formatting the cell accordingly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert HH:MM AM to HH:00 AM 1
Determine if night shift by start & end time 2
Grouping Time in Pivots. 2
Time formatting in Excel 2003 2
Excel Convert Datetime Stamp to HH and MM Worked 7
Data translation 4
display time in IST as well as GMT? 5
Time Cells 5

Top