Shawndapew
New Member
- Joined
- Sep 2, 2014
- Messages
- 7
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
<colgroup><col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;" span="4">
<tbody>
</tbody>
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 |