ExcelBanter - Change text to military time

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Change text to military time (https://www.excelbanter.com/excel-worksheet-functions/78798-change-text-military-time.html)

gobonniego

Change text to military time

I'm trying to change a column of 6 digit numbers into military time separated
by colons. Example 180333 to 18:03:33 (hours, minutes, seconds). I've used
=(A1/100)+(((A1/100)-(A1/100))/0.6) and reformat for time and also
=VALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))

Neither of them transfer the 6 digit number into the correct time. Any help?

Duke Carey

Change text to military time

=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

formatted as Time will do the trick

"gobonniego" wrote:

I'm trying to change a column of 6 digit numbers into military time separated
by colons. Example 180333 to 18:03:33 (hours, minutes, seconds). I've used
=(A1/100)+(((A1/100)-(A1/100))/0.6) and reformat for time and also
=VALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))

Neither of them transfer the 6 digit number into the correct time. Any help?

gobonniego

Change text to military time

BINGO! You're the winner. Thank you soooo much for this. It worked great!

Bonnie

"Duke Carey" wrote:

=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

formatted as Time will do the trick

"gobonniego" wrote:

I'm trying to change a column of 6 digit numbers into military time separated
by colons. Example 180333 to 18:03:33 (hours, minutes, seconds). I've used
=(A1/100)+(((A1/100)-(A1/100))/0.6) and reformat for time and also
=VALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))

Neither of them transfer the 6 digit number into the correct time. Any help?

All times are GMT +1. The time now is 12:38 PM.