G Guest Nov 25, 2004 #1 how excel 2000 convert the number to time format ( Example 130511 become 13:05:11 ) ?
D Domenic Nov 25, 2004 #2 wong said: how excel 2000 convert the number to time format ( Example 13051 become 13:05:11 ) ? Click to expand... Assuming A1 contains your number... =TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)) OR =SUM(MID(A1,{1,3,5},2)/{24,1440,86400}) ...confirmed with CONTROL+SHIFT+ENTER, and format cell as "Time". Hope this helps
wong said: how excel 2000 convert the number to time format ( Example 13051 become 13:05:11 ) ? Click to expand... Assuming A1 contains your number... =TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)) OR =SUM(MID(A1,{1,3,5},2)/{24,1440,86400}) ...confirmed with CONTROL+SHIFT+ENTER, and format cell as "Time". Hope this helps
D Dave Peterson Nov 25, 2004 #3 Another option a formula like: =--TEXT(A1,"00\:00\:00") (format as time: hh:mm:ss) Chip Pearson has time (and date) quick entry procedures at: http://www.cpearson.com/excel/DateTimeEntry.htm They actually make the change to time as soon as you fininsh entering the value in the cell.
Another option a formula like: =--TEXT(A1,"00\:00\:00") (format as time: hh:mm:ss) Chip Pearson has time (and date) quick entry procedures at: http://www.cpearson.com/excel/DateTimeEntry.htm They actually make the change to time as soon as you fininsh entering the value in the cell.
