Entering time without colon so ends up with hh:mm (no seconds) and can subtract two time entries to get difference

jghans wrote:
> I'm wanting to enter military time without the colon "hhmm"
> so it ends up as "hh:mm"

Format the cells as Custom 00\:00 .

To do that, select the cells, right-click and click on Format Cells, Number, Custom, and enter 00\:00 into the Type field.

Note that even though the military time 1234 might appear as 12:34, it is still the number one thousand thirty-four, not the numeric time 12:34.

jghans wrote:
> I also need to be able to subtract one "hhmm" entry from
> another "hhmm" entry so I can end up with the "hh:mm"
> difference with the colon.

If the start time is in A1 and the end time is in A2, use the following formula:

=TEXT(A2,"00\:00")-TEXT(A1,"00\:00")+(A1>A2)

formatted as Custom hh:mm .

This results in true numeric time.

The term (A1>A2) handles the case when the start time appears to exceed the end time because the time interval spans midnight.