How do I Change text (0130PM) into military time equivalent?

[Guest]

I'm trying to change times that are reported in text format (ie 0830PM
instead of 8:30PM) into military time equivalents.

Is there a straightforward way to do this?

 

[Guest]

Try something like this:

For a text-time in A1 (example: 0830PM)

B1: =TEXT(LEFT(A1,4)+(RIGHT(A1,2)="PM")*1200,"0000")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Yes, that works.
I just then have to change all the 2400's to 1200's since 1200 is noon, and
0000 is midnight

Do you know of a way to do that?
I'm working on it myself.

Sara

 

[Guest]

Try this:

=TEXT(TIMEVALUE(LEFT(A1,2)&":00 "&RIGHT(A1,2))*2400+MID(A1,3,2),"0000")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

EXCELLENT!!

Thanks

Sara

Try this:

=TEXT(TIMEVALUE(LEFT(A1,2)&":00 "&RIGHT(A1,2))*2400+MID(A1,3,2),"0000")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

This also works:

=TEXT(LEFT(A1,2)&":"&MID(A1,3,2)&" "&RIGHT(A1,2),"hhmm")

Does that help?
***********
Regards,
Ron

XL2002, WinXP