Excel: Error when adding minutes without hours... Please help :(


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.


For more resources for Microsoft Excel