Automatically adding ':' to Military Time

[David Schuler]

I need to create a sheet that finds the difference in military time.
User inputs start (Column B) and stop (Column C) time, and then the
total time is shown (Column D).

I can get it to work easy enough, except I'd like to have the user not
have to SHIFT + ; for every time.

So online, I found a VB script that fixes the problem.
-----------

Private Sub Worksheet_Change(ByVal Target As Range)
ThisColumn = Target.Column
If ThisColumn > 1 And ThisColumn < 4 Then
UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" &
Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End If
End Sub

-------------

However, with this code, Column D no longers totals the time. Typing
in 1234 correctly inputs 12:34, but in the formula bar it shows
12:34:00 PM!

The formula I have in Column D is:

=(C4-B4)*1440

This correctly shows the total time in minutes if I do not use the VB
script. But with the script, I get nothing shown.

Is there a better script or formula I could use to get the best of
both worlds?

~ Dave

 

[Tom Ogilvy]

If it displays in the formula bar as you say, your formula should work.

In military time, 12:34 is 34 minutes after noon - 12:34 PM

00:34 is 34 minutes after midnight. 00:34 AM

 

[Crash]

Hi David

The code works fine.

Do you want the total time to be in minutes or hh:mm, the formula
=(C4-B4)*1440 is for minutes. Simply subtracting C from B will give you
hh:mm
There is a condition that exists when midnight is crossed such as start
21:00 and end at 05:00 you will get negative time which doesn't exist
in the normal date system you'll need to use the 1904 date system or
use the formula =(C4-B4)+(C4<B4).

HTHs

Jon