VBA code military time hassles... please help!

[briantemp]

Before I pull the rest of my hair out...

I have a spreadsheet in which I'm entering start and end times, then applying a simple calculation to get the differance between the two. I'm using this VBA data to eliminate the need to type a colon-

Private Sub Worksheet_Change(ByVal Target As Range)
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 Sub

-and it works great EXCEPT FOR when I have a time such as 0035 in which case it displays :35 only, which prevents the calcuation from being made. I've tried several reformattings of the cells with double-zero times but nothing works. Is there a simple solution I can apply? I'm not as tech-savvy with macro/VBA applications as I wish I was, so a cut and paste option would be greatly appreciated... or at least an "in simple terms" solution.

This messageboard is brilliant with info... hopefully someone can help me out?

Thanx muchly! ray:

~Brian

 

[zilpher]

Hi Brian, welcome to the board.

This will help you around your issue:

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

I made NewInput equal to a formatted number of min 4 digits with leading zeros which gets around your issue.

I also changed the logic of If UserInput > 1 as it was not catching a deletion of data, you might want to change that back.

HTH

 

[briantemp]

Fantastic! Thank you thank you thank you! Working great now... the only thing is that some of the resulting calculations appear as ######## instead of the elapsed hours:minutes between the start and end times. Any idea what I can plug into it to get the proper results? I'm using the tried and true =(C1-B1) as the A column contains Lot Numbers and aren't part of the equation.

Much thanx!

~Brian

 

[briantemp]

Sorry, should've mentioned that I'm dealing with a 24 hour military time thing as well... for instance:

START END
14:00 04:00

which is:

2:00 PM to 4:00 AM

There are a LOT of these numbers to input, so ideally if there's any sort of code I can use that will:

A) allow me to input the times without having to type a colon in, and

B) calculate the difference between the start and end times taking into account the military time 24 hour thing, and

C) not have the other columns on the spreadsheet be affected by the automatic formatting, as there are columns in which we have to intial and date our data entry... when I type the date in I get 4/7/:04 instead of 4/7/04.

Hopefully that's not too much to lay on everyone, and I've explained it right... I've been at it all day and I'm pretty beat.

Thanks again zilpher... any more great advice?

~Brian

 

[Legacy 21301]

Assuming that you want the difference between the two times to be in column D, format cloumns B:D as [h]:mm and try this :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserInput, r&
Application.EnableEvents = False
If Not Intersect(Target, [B:C]) Is Nothing Then
    If Not Selection.Cells.Count = 1 Then
        Application.Undo
        Application.EnableEvents = True
        Exit Sub
    End If
    UserInput = Format(Target.Value, "0000")
    If Len(UserInput) > 1 Then
        Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
    End If
    r = Target.Row
    If Cells(r, 2) = "" Or Cells(r, 3) = "" Then
        Cells(r, 4).ClearContents
    Else
        Cells(r, 4).FormulaR1C1 = "=IF(RC[-1]<RC[-2],RC[-1]+1-RC[-2],RC[-1]-RC[-2])"
    End If
End If
Application.EnableEvents = True
End Sub

Note : you will only be able to change one cell at a time in columns B:C

 

[Ekim]

Brian,

Instead of using “=(C1-B1)” use “=C1-B1+(C1< B1)”, and format the cell as [h]:mm.

See the simulation. In row 6, the start time is 11:56 and the finish time is 9:35. Excel interprets the finish time as the next day i.e. you have crossed midnight. In this case, using “=C6-B6” will result in #########. Cell D13 shows the correct formula for times that cross midnight (formatted as [h]:mm).

The formula could be a bit more elaborate to show no result if columns B or C are empty:
=IF(OR(B15="",C15=""),"",C15-B15+(C15< B15))

Time entered without the colon.xls
	A	B	C	D	E
1		Start	Finish	Difference
2
3		9:35	12:36	3:01
4		9:15	15:23	6:08
5		15:36	17:56	2:20
6		23:56	9:35	#########
7
8
9
10		9:35	12:36	3:01
11		9:15	15:23	6:08
12		15:36	17:56	2:20
13		23:56	9:35	9:39
14
Sheet1

The first macro has been slightly changed. The changes should be self explanatory.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 And Target.Row > 2 _
  Or Target.Column = 3 And Target.Row > 2 Then
   UserInput = Format(Target.Value, "0000")
    NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
     Application.EnableEvents = False
     Target = NewInput
     Application.EnableEvents = True
End If
End Sub

HTH

Mike

 

[Ekim]

That “crossing midnight” formula can be simplified to
=MOD(C10-B10,1)
Starting in C10 and copy down.

 

[Legacy 21301]

Ekim's formula =MOD(C1-B1,1) is very nice.

To incorporate into the macro I posted, change the line that reads :

Cells(r, 4).FormulaR1C1 = "=IF(RC[-1]<RC[-2],RC[-1]+1-RC[-2],RC[-1]-RC[-2])"

to :

Cells(r, 4).FormulaR1C1 = "=MOD(RC[-1]-RC[-2],1)"


Note re the macros posted by Ekim and Zilpher, either run-time errors will occur or incorrect values will be returned if :

- more than one cell in B:C is changed at one time by direct input
- changes are made to B:C by dragging
- any cell (or cells) is copied and pasted to one or more cells in C:D

The macro I posted overcomes most of this by not allowing selections of more than one cell if the selection includes any cell in B:C.
But this still leaves the problem of a wrong value being returned if the value of one cell is copied and pasted to one cell only in B:C.

I think this overcomes it (not fully tested - and there's probably a better way) :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserInput, r&
Application.EnableEvents = False
If Not Intersect(Target, [B:C]) Is Nothing Then
    If Not Selection.Cells.Count = 1 Then
        Application.Undo
        Application.EnableEvents = True
        Exit Sub
    End If
    If Target.Value < 1 Then
        UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24) - Int(Target.Value * 24)) * 60, "0000")
    Else
        UserInput = Format(Target.Value, "0000")
    End If
    If Len(UserInput) > 1 Then
        Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
    End If
    r = Target.Row
    If Cells(r, 2) = "" Or Cells(r, 3) = "" Then
        Cells(r, 4).ClearContents
    Else
        Cells(r, 4).FormulaR1C1 = "=MOD(RC[-1]-RC[-2],1)"
    End If
End If
Application.EnableEvents = True
End Sub

Still needs validation (either via worksheet Data Validation of via macro) to ensure only valid numbers are entered in B:C

 

[briantemp]

Thanks everyone! I totally appreciate your time and attention to my little problem... I'll be trying these out today and I'll be sure to follow up to let you know what worked out the best.

Have a good'un!

~Brian

 

[briantemp]

UPDATE: Everything is working fine, but now I have to add more columns to the spreadsheet, thusly what I now find I need is an alteration to the macro that will repeat the time conversions/calculations for F1-E1 with the result cell being G1, and I1-H1 result cell J. I'm still not familiar enough with macro code to go in and tweak it to get the desired results... the one I plugged in is this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserInput, r&
Application.EnableEvents = False
If Not Intersect(Target, [B:C]) Is Nothing Then
If Not Selection.Cells.Count = 1 Then
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
UserInput = Format(Target.Value, "0000")
If Len(UserInput) > 1 Then
Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
End If
r = Target.Row
If Cells(r, 2) = "" Or Cells(r, 3) = "" Then
Cells(r, 4).ClearContents
Else
Cells(r, 4).FormulaR1C1 = "=IF(RC[-1]<RC[-2],RC[-1]+1-RC[-2],RC[-1]-RC[-2])"
End If
End If
Application.EnableEvents = True
End Sub

...which works perfectly for columns B,C and D. Additionally, columns K and L need to remain "blank" so I can enter initials and dates. This is a three page document, exact same column format on each page, so if there's any way I can have the macro target just the columns I need, still with not having to type a colon and having the time adjust for the midnight crossover, that'd be great:

C-B=D
F-E=G
I-H=J
O-N=P
R-Q=S
U-T=V
AA-Z=AB
AD-AC=AE
AG-AF=AH

Thanks again!

~Brian