I have the following on time sheets
In A1 800
Out A2 1206
In A3 1226
Out A4 500
Total A5
I want the total hours worked to 1 decimal in A5
I have the following on time sheets
In A1 800
Out A2 1206
In A3 1226
Out A4 500
Total A5
I want the total hours worked to 1 decimal in A5
Re: Convert time clock hours and minutes time to hours
Try something like this. Put this code in the Sheet code and run
Sub Time()
Dim arry(0 To 3) As Date
For i = 1 To 4
cellVal = Cells(i, 1).Value
If Len(cellVal) = 3 Then
cellVal = TimeSerial(Left(cellVal, 1), Right(cellVal, 2), 0)
Else
cellVal = TimeSerial(Left(cellVal, 2), Right(cellVal, 2), 0)
End If
arry(i - 1) = cellVal
Next
arry(3) = DateAdd("h", 12, arry(3))
Cells(5, 1).Value = (arry(1) - arry(0) + arry(3) - arry(2)) * 24
Cells(5, 1).NumberFormat = "##.#"
End Sub
Display More
Re: Convert time clock hours and minutes time to hours
B1:
=ROUND(TIMEVALUE(TEXT(A1,"#!:00"))*24,1)
copy down to B4
B5 :
=SUM(B1:B4)
Re: Convert time clock hours and minutes time to hours
l like Jindon's formula, but it does not work. I get a #value error. I tried both text and number. Since I have about 20 employee's, a formula works best versus a macro
Quote from sjeter;622998Try something like this. Put this code in the Sheet code and run
CodeDisplay MoreSub Time() Dim arry(0 To 3) As Date For i = 1 To 4 cellVal = Cells(i, 1).Value If Len(cellVal) = 3 Then cellVal = TimeSerial(Left(cellVal, 1), Right(cellVal, 2), 0) Else cellVal = TimeSerial(Left(cellVal, 2), Right(cellVal, 2), 0) End If arry(i - 1) = cellVal Next arry(3) = DateAdd("h", 12, arry(3)) Cells(5, 1).Value = (arry(1) - arry(0) + arry(3) - arry(2)) * 24 Cells(5, 1).NumberFormat = "##.#" End Sub
Re: Convert time clock hours and minutes time to hours
See....
Re: Convert time clock hours and minutes time to hours
forum.ozgrid.com/index.php?attachment/47792/
Sorry, I did not explain what I needed or what my input was.
The numbers are clock times AM/PM. (see attached) I can use the @right to get the minutes, but get lost in trying to calculate hours.
Re: Convert time clock hours and minutes time to hours
See...
Re: Convert time clock hours and minutes time to hours
forum.ozgrid.com/index.php?attachment/47813/Attached is what I used: I used a vertical lookup to find the hours and @right to calculate the minutes.
Don’t have an account yet? Register yourself now and be a part of our community!