Convert time

  • Thread starter Thread starter jr
  • Start date Start date
J

jr

if value is in B9:
=LEFT(TEXT(B9,"00####"),2)&":"&MID(TEXT(B9,"00####"),3,2)
 
This may work better as it returns the time as a value.

=IF(LEN(B9)=6,TIMEVALUE(LEFT(B9,2)&":"&MID(B9,3,2)&":"&RIGHT(B9,2)),TIMEVALU
E(LEFT(B9,1)&":"&MID(B9,2,2)&":"&RIGHT(B9,2)))

However, it assumes that this is military time and 25400 is 2:54 AM.

Format the cell as Time.

PC
 
You must log in or register to reply here.

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

How do I format a cell to calculate overnight Hours? 7
command button code 2
Nested If 2
FIND TEXT IN 1 CELL, COMPARE TO ANOTHER CELL 6
Time schedule 3
Eliminating "#VALUE!" for a formula’s results 1
How do I keep the divisor constant in calculating percentage remai 3
Re : Excel Data>Sort... Tracking of Cell Addresses Used in Formulae 1

Back
Top