Timesheet Question

[Daxton A.]

:bink: My question is about the converting from minutes to a decimal answer. I have what I think would make it work but it won't work. I've been trying to get it to work for a couple of days now and thought I would rely on the experts to show me the answer. I started out making this timesheet as a way to teach VB to a friend of mine b/c he needs the timesheet and he wants to learn VB. Right in the middle of teaching him, lol i need some help. The error comes up
in the minHand function. I'm a little bit rusty on functions so it's probably just missing something.

I have the Timesheet attached in case it needs to be looked at.


Sub addTime()
Dim pm As Integer
Dim i As Integer
Dim theValue1 As Double
Dim theValue2 As Double
Dim theValue As Double
Dim theDecimal1 As Double
Dim theDecimal2 As Double
Dim theDecimal As Double
Dim mins1 As Integer
Dim mins2 As Integer
'range C & F is the Hour Hand
'range D & G is the Minute Hand
'range E & H is AM or PM
For i = 2 To 1000
mins1 = Range("D" & i).Value
mins2 = Range("G" & i).Value
If Range("C" & i) = "" Or Range("D" & i) = "" Or Range("E" & i) = "" Or Range("F" & i) = "" Or Range("G" & i) = "" Or Range("h" & i) = "" Then
i = 1000
Else
If Left(Range("K" & i).Formula, 4) = "=SUM" Then 'Or Range("K" & i) = ""
i = 1000
Else
If Range("E" & (i - 1)) = "PM" Then
theValue1 = Range("C" & i).Value + 12 & "." & minHand(mins1)
Else
theValue1 = Range("C" & i).Value & "." & minHand(mins1)
End If
If Range("H2") = "PM" Then
theValue2 = Range("F" & i).Value + 12 & "." & minHand(mins2)
Else
theValue2 = Range("F" & i).Value & "." & minHand(mins2)
End If
theValue = theValue2 - theValue1
Range("K" & i) = theValue
End If
End If
Next i
End Sub

Function minHand(minutes As Integer)
If minutes = 15 Then
minutes = 25
ElseIf minutes = 30 Then
minutes = 50
ElseIf minutes = 45 Then
minutes = 75
End If
End Function

[Daxton A.]

Here's the spreadsheet b4 I realized that I needed to convert the time over to the time schedule. Meaning, if he clocked in @ 9:30AM and clocked out @ 10:00AM...the time shown would be a .7 instead of a .5...I didn't have this problem at all b4 that. So I added the function to convert the time over and that's where I'm stuck :dunno

I attached my previous spreadsheet. The one b4 i started the conversion if it helps at all.

[Zack Barresse]

For your minute hand function, why don't you just do a simple calculation ...


Function minHand(minutes As Integer)
minutes = minutes * 1.6
End Function

Haven't looked at your sheet yet though. Just a quick observation.

[mdmackillop]

I've tweaked your code a bit to do the sums


If Range("E2") = "PM" Then
theValue1 = Range("C2").Value + 12 + Range("D2").Value / 60
Else
theValue1 = Range("C2").Value + Range("D2").Value / 60
End If


A simpler way would be

theValue1 = Range("C2").Value + Range("D2").Value / 60
If Range("E2") = "PM" Then theValue1 = theValue1 + 12


To make the application more user friendly, I've added a sheetchange event. Basically, it checks if you have selected a cell in column I and if there is data (AM/PM) in column H, it will do the time calculation and enter the result in Column K. The Cell references are changed to "offsets" as the code needs to be more flexible. The code is stored in Sheet1(Timesheet) rather than in a standard module.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim theValue As Double, theValue1 As Double, theValue2 As Double
If Target.Column() = 9 And ActiveCell.Offset(0, -1) <> "" Then
theValue1 = ActiveCell.Offset(0, -6).Value + ActiveCell.Offset(0, -5).Value / 60
If ActiveCell.Offset(0, -4) = "PM" Then
theValue1 = theValue1 + 12
End If
theValue2 = ActiveCell.Offset(0, -3).Value + ActiveCell.Offset(0, -2).Value / 60
If ActiveCell.Offset(0, -1) = "PM" Then
theValue2 = theValue2 + 12
End If
theValue = theValue2 - theValue1
ActiveCell.Offset(0, 2) = theValue
End If
End Sub


Good luck with the learning!
MD

[mdmackillop]

BTW, How did you insert the colons between the hours and minutes?

[Zack Barresse]

Their OLEObjects MD. Insertion of a text box and typed a ":" in it. :)

[mdmackillop]

Missed that! I thought it was some clever border setting.

[Daxton A.]

Im sry it took me so long to respond, but I couldn't get none of your answers to work. I'm not saying that they won't, I'm just saying "THAT I" couldn't get them to work lol. And I'm sure the spreadsheet works that you worked for me Mdmackillop, but I don't know how the thing works.

And one more thing lol, what does your name say. Me and my wife got into an argument over it and I want to know actually if she's right or I'm right to settle this. It's nothing bad, we're not seriously fighting or anything.

Thanks for the help so far.
Oh...and 1 more thing, what does the .Offset property mean?

[Jacob Hilderbrand]

Offset is a range a specified number of rows and columns away from another range.


OffSet(Row, Col)

Positive numbers for columns to the right and rows down. Negative numbers for columns to the left and rows up.

So


ActiveCell.Offset(2, 5)

is the cell two rows down and 5 columns to the right of the active cell.

[mdmackillop]

Hi,
Its M.D. MacKillop in full.
Re the spreadsheet, check your Macro security settings. Tools/Macro/Security set to medium.
If you enter new start and finish times, selecting the Project cell should cause the Time to be calculated.
With regard to the offset, in your code, you use "theValue1 = Range("C2").Value", which is fine, but too specific. My example of code, based on the project cell being selected, would read "theValue1 = Activecell.Offset(0,-6).Value" as I need it to be applicable to any row.
MD

[Daxton A.]

TY for all the good help u 3. It works.
I always get the right answer in here, maybe a few right answers, but they always work. Anyways, its solved.

Thank you all again!

[mdmackillop]

Glads to be of help. If you go to Thread Tools, you can mark this as solved.
MD