Hello, all. I have this crazy time sheet spreadsheet. My district managers asked me to try and make it where they can enter 130 for 1:30 so they can enter start and stop times faster. I was finally able to achieve this by formatting the start and end times as 1:30PM and the drive time as 13.30 I have the following code on the worksheet so the ":" is entered automatically. The range "Time" set just for the time cell on the sheet (I did not define a range at first, so it was applied to the whole page and text entries were automatically entering a ":" before the last two characters of anything I typed). Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, r As Range Set rng = Intersect(Target, Range("Time")) If rng Is Nothing Then Exit Sub 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 So here is the issue, the "DRIVE TIME" is not a formula number. I enter 30 and the code tells it to me :30 Again, the cell is formatted as 13:30. When I add up COLUMN D, THE VALUE COMES BACK AS 0:00 *the cell where I want the totals is formatted as [h]:mm Please help before I lose my mind :) A B C D
|START TIME||END TIME||TOTAL TIME||DRIVE TIME|
|8:00 AM||9:00 AM||1:00||:30|
|9:00 AM||10:00 AM||1:00||:45|
|10:00 AM||11:00 AM||1:00||:15|
This question generated 15 answers. To proceed to the answers, click here.
This thread is current as of September 18, 2014.