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

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

START TIME
END TIMETOTAL TIMEDRIVE TIME
8:00 AM9:00 AM1:00:30
9:00 AM10:00 AM1:00:45
10:00 AM11:00 AM1:00:15
<colgroup><col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;" span="4"> <tbody> </tbody>
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
You are summing text. Try this:

Code:
NewInput = (Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2))+0
 
Upvote 0
To prevent error of less than one hour:

Code:
[TABLE="width: 446"]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT]<colgroup><col width="594" style="width: 446pt; mso-width-source: userset; mso-width-alt: 21723;">[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]<tbody>[TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 594, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]If   Len(Userinput)<3 Then[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]NewInput = (Left(UserInput,   Len(UserInput) - 2) & ":" & Right(UserInput, 2))+0[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]NewInput = (0 & ":" &   Right(UserInput, 2))+0[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]</tbody>[/TABLE]
 
Upvote 0
This did not work. Now I cannot enter a time at all in the start or stop cells. The Runtime Error "13", Type mismatch comes up...

I put my code back in... any other suggestion?
 
Last edited:
Upvote 0
Steve, I am new to coding (google, forums and this nifty site have been quite helpful). Can you tell me exactly where to put the code you posted within the one I have?
 
Upvote 0
I may be misunderstanding this but don't you times the value by 24 as I've seen that sometimes excel misinterprets the value to be hours instead of minutes?

Does that help - sorry it not!

Rodp
 
Upvote 0
No, I don't have it set up as times 24. I have done some reading on that as well. Doesn't seem to help unless I am doint it wrong. Here's what I THINK is the issue
1. the number is formatted as a time (13:30)
2. The number is entered as 30
3. The code puts the ":" in for me

Summary: I entered a 2 digit number (but sometimes there wil be an entry like 115 that shows as 1:15 (1 hr 15 min - NOT 1:15 PM). Excel sees it as a time formatted box, or should.. So do I need to enter within the code that if it is a 2 digit entry, to place a "0"before the colon? Before the code to not have to type the ";" , I had the same problem adding up this column until I entered it as 0:30 After that, the column totaled fine. Now when I just enter the 30 it is not adding it again. See below of what it is doing. It adds up everything just fine that has an hour...but is not calculating the entries with just minutes.

I think I need to just tell excel to enter "0" before a ":" if there is a two digit entry...

How can I do this and where do I place it in the code?



START TIMEEND TIMETOTAL TIMEDRIVE TIME
8:00 AM11:00 AM3:001:30
11:00 AM12:00 PM1:00:30
1:00 PM2:00 PM1:00:45
2:00 PM3:30 PM1:301:15

<tbody>
</tbody>

6:30 2:45
 
Upvote 0
Code:
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

If Left(UserInput, Len(UserInput) - 2) = "" Then
NewInput = "0:" & Right(UserInput, 2)
Else
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
End If

Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub

Maybe this?
 
Upvote 0
The way to check is that I know that a value of '1' in terms of time is 1 days. So if you type 0:30 to mean 0:30 to mean 30 seconds,. excel won't interpret that as 30 seconds but as 30 minutes. so you need to do 00:00:30

So for 30 minutes, you need to do 00:30:00, ie make sure you enter some digits in for seconds.

Now... does that help?

Rodp
 
Upvote 0
Steve, THIS WORKED!!!!!!! Thank you so much. Here's the only thing.... When I right click, clear contents... I get an error. If I click DEBUG, this is what it being highlighted




If UserInput > 1 Then
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top