Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically adding ':' to Military Time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically adding ':' to Military Time
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 -- Regards, Tom Ogilvy "David Schuler" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically adding ':' to Military Time
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 David Schuler wrote: 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 -- 42°57N 81°16W |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding a colon to military time | Excel Worksheet Functions | |||
Adding military time from multiple cells | Excel Discussion (Misc queries) | |||
Automatically Adding Time based on a name ranged | Excel Discussion (Misc queries) | |||
Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions | |||
adding military time format | Excel Discussion (Misc queries) |