Numbers to Time Conversion

[Skoal]

I posted a similar question recently, but I have come across a new issue.

I would like to convert numbers (with "A" and "P") to standard military times.

This is what I currently have:

543P
1125A

I would like to get:

17:43 PM
11: 25 AM

Thanks for your help!

[swatsp0p]

First, note that 'military time' does not use the AM/PM designators. With that said, format your cell as Time with the Style: 13:30

Use this formula, adjusted to meet your data's range:

=IF(RIGHT(A1,1)="P",TIMEVALUE(LEFT(A1,LEN(A1)-3)&":"&MID(A1,LEN(A1)-2,2))+0.5,TIMEVALUE(LEFT(A1,LEN(A1)-3)&":"&MID(A1,LEN(A1)-2,2)))

5:43P becomes 17:43
11:25A becomes 11:25

HTH

Bruce

[Skoal]

Thanks Bruce! I forgot that the AM/PM designation is obviously unnecessary when using military time.

I appreciate your help.

[swatsp0p]

There is an error in my first formula for converting this to a time format. It has to do with the way LEFT was seeing 121A as "12". This formula has been tested against various permutations of the time entry possibilities and differentiates between 1210A and 121A by use of LEN()-3:

=IF(AND(RIGHT(B13,1)="A",LEFT(B13,LEN(B13)-3)="12"),TIMEVALUE("00"&":"&MID(B13,LEN(B13)-2,2)),IF(RIGHT(B13,1)="A",TIMEVALUE(LEFT(B13,LEN(B13)-3)&":"&MID(B13,LEN(B13)-2,2)),IF(AND(RIGHT(B13,1)="P",LEFT(B13,LEN(B13)-3)="12"),TIMEVALUE(LEFT(B13,LEN(B13)-3)&":"&MID(B13,LEN(B13)-2,2))+0.5,TIMEVALUE(LEFT(B13,LEN(B13)-3)&":"&MID(B13,LEN(B13)-2,2)))+0.5))

Entry Becomes

1201A \ 0:01
121A \ 1:21
935A \ 9:35
1201P \ 12:01
131P \ 13:31
1159P \ 23:59

Sorry for the confusion. This was ugly. Maybe someone can see an easier solution.

Bruce