How do I convert time in multiple cells to military format?

[booleanboolean]

I am afraid I am going to have to do this manually. For all of the times you see below except for those cells which say "OFF", I will have to convert to military time in this format: 0600-1400, so on and so forth.

Is there any way this can be accomplished?


[TABLE="width: 494"]
<colgroup><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD]F[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD]W[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]7a-3p[/TD]
[TD]7a-3p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]7a-3p[/TD]
[TD]7a-3p[/TD]
[TD]7a-3p[/TD]
[/TR]
[TR]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]12a-8a[/TD]
[TD]12a-8a[/TD]
[TD]12a-8a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]12a-8a[/TD]
[TD]12a-8a[/TD]
[/TR]
[TR]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[TD]8a-4p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]8a-4p[/TD]
[/TR]
[TR]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]6a-2p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]12a-8a[/TD]
[TD]12a-8a[/TD]
[TD]12a-8a[/TD]
[TD]12a-8a[/TD]
[TD]12a-8a[/TD]
[TD]OFF[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

Go to Custom Format ... and add 00\:00

HTH

 

[Rick Rothstein]

Are your time spans always exactly 8 hours as your example table shows, or is your example a simplification of what your data really looks like?