ExcelBanter - Is this right

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Is this right (https://www.excelbanter.com/excel-programming/279532-right.html)

I am new to VB and been asked to fix up a book so all the sheets are the
same now.

At present time, time is enter on a few sheets are like this 1645.30 and
is used to calculate time from different between cols (i.e..) col dd7
(1245.45) minus col p7(1230.25) for col df that has =runtime(dd7) in it
that gives 15.20. Start point for each row is different and there is
more than 1 =runtime() col in the row.

I need to change how the time is enter from 1245.45 to 12:45:45 that is
been added on other sheet with quick time enter code.

The only part of the code I see that needs to be change is this

STstr = Format(StartTime, "0000.00")
ETstr = Format(EndTime, "0000.00")

stH = Left(STstr, 2)
stMIN = Mid(STstr, 3, 2)
stSEC = Right(STstr, 2)

etH = Left(ETstr, 2)
etMIN = Mid(ETstr, 3, 2)
etSEC = Right(ETstr, 2)

temp = TimeSerial(etH, etMIN, etSEC) - TimeSerial(stH, stMIN, stSEC)

RunTime = CDbl(Format(temp, "hhmm.ss"))

End Function

To this I think, but does not work for some reason.

STstr = Format(StartTime, "00.00.00")
ETstr = Format(EndTime, "00.00.00")

stH = Left(STstr, 2)
stMIN = Mid(STstr, 3, 2)
stSEC = Right(STstr, 2)

etH = Left(ETstr, 2)
etMIN = Mid(ETstr, 3, 2)
etSEC = Right(ETstr, 2)

temp = TimeSerial(etH, etMIN, etSEC) - TimeSerial(stH, stMIN, stSEC)

RunTime = CDbl(Format(temp, "hh.mm.ss"))

End Function

John

StartTime = 1645.30
RunTime =
TimeSerial(Left(Starttime,2)*1,Mid(StartTime,3,2)* 1,Right(StartTime,2)*1)

Testing from the immediate window:

StartTime = 1645.30
RunTime =
TimeSerial(Left(Starttime,2)*1,Mid(StartTime,3,2)* 1,Right(StartTime,2)*1)
? runtime
4:45:00 PM

if startime could have less than 4 numbers on the left

Startime = 45.30
Starttime = Right("0000" & StartTime),7)

--
Regards,
Tom Ogilvy

"FutureTransit2020" wrote in message
...
I am new to VB and been asked to fix up a book so all the sheets are the
same now.

At present time, time is enter on a few sheets are like this 1645.30 and
is used to calculate time from different between cols (i.e..) col dd7
(1245.45) minus col p7(1230.25) for col df that has =runtime(dd7) in it
that gives 15.20. Start point for each row is different and there is
more than 1 =runtime() col in the row.

I need to change how the time is enter from 1245.45 to 12:45:45 that is
been added on other sheet with quick time enter code.

The only part of the code I see that needs to be change is this

STstr = Format(StartTime, "0000.00")
ETstr = Format(EndTime, "0000.00")

stH = Left(STstr, 2)
stMIN = Mid(STstr, 3, 2)
stSEC = Right(STstr, 2)

etH = Left(ETstr, 2)
etMIN = Mid(ETstr, 3, 2)
etSEC = Right(ETstr, 2)

temp = TimeSerial(etH, etMIN, etSEC) - TimeSerial(stH, stMIN, stSEC)

RunTime = CDbl(Format(temp, "hhmm.ss"))

End Function

To this I think, but does not work for some reason.

STstr = Format(StartTime, "00.00.00")
ETstr = Format(EndTime, "00.00.00")

stH = Left(STstr, 2)
stMIN = Mid(STstr, 3, 2)
stSEC = Right(STstr, 2)

etH = Left(ETstr, 2)
etMIN = Mid(ETstr, 3, 2)
etSEC = Right(ETstr, 2)

temp = TimeSerial(etH, etMIN, etSEC) - TimeSerial(stH, stMIN, stSEC)

RunTime = CDbl(Format(temp, "hh.mm.ss"))

End Function

John

Tom Ogilvy wrote:

StartTime = 1645.30
RunTime =
TimeSerial(Left(Starttime,2)*1,Mid(StartTime,3,2)* 1,Right(StartTime,2)*1)

Testing from the immediate window:

StartTime = 1645.30
RunTime =
TimeSerial(Left(Starttime,2)*1,Mid(StartTime,3,2)* 1,Right(StartTime,2)*1)
? runtime
4:45:00 PM

if startime could have less than 4 numbers on the left

Startime = 45.30
Starttime = Right("0000" & StartTime),7)

--
Regards,
Tom Ogilvy

"FutureTransit2020" wrote in message
...
I am new to VB and been asked to fix up a book so all the sheets are the
same now.

At present time, time is enter on a few sheets are like this 1645.30 and
is used to calculate time from different between cols (i.e..) col dd7
(1245.45) minus col p7(1230.25) for col df that has =runtime(dd7) in it
that gives 15.20. Start point for each row is different and there is
more than 1 =runtime() col in the row.

I need to change how the time is enter from 1245.45 to 12:45:45 that is
been added on other sheet with quick time enter code.

The only part of the code I see that needs to be change is this

STstr = Format(StartTime, "0000.00")
ETstr = Format(EndTime, "0000.00")

stH = Left(STstr, 2)
stMIN = Mid(STstr, 3, 2)
stSEC = Right(STstr, 2)

etH = Left(ETstr, 2)
etMIN = Mid(ETstr, 3, 2)
etSEC = Right(ETstr, 2)

temp = TimeSerial(etH, etMIN, etSEC) - TimeSerial(stH, stMIN, stSEC)

RunTime = CDbl(Format(temp, "hhmm.ss"))

End Function

To this I think, but does not work for some reason.

STstr = Format(StartTime, "00.00.00")
ETstr = Format(EndTime, "00.00.00")

stH = Left(STstr, 2)
stMIN = Mid(STstr, 3, 2)
stSEC = Right(STstr, 2)

etH = Left(ETstr, 2)
etMIN = Mid(ETstr, 3, 2)
etSEC = Right(ETstr, 2)

temp = TimeSerial(etH, etMIN, etSEC) - TimeSerial(stH, stMIN, stSEC)

RunTime = CDbl(Format(temp, "hh.mm.ss"))

End Function

John

Sorry, I may lead you, so here is all the code for this. I snip the part that
has to be change.

Function RunTime(EndTime As Range) As Double
Dim StartTime As Double
Dim org As Range
Dim STstr As String, ETstr As String
Dim stH As Long, stMIN As Long, stSEC As Long
Dim etH As Long, etMIN As Long, etSEC As Long
Dim col As Long, EndCol As Long, rw As Long
Dim i As Long
Const startCol = 13 'Column M
Const LabelRow = 4
Dim temp As Double

Dim ar
Const c1 = "worker at", c2 = "Station #" 'this defines the Start Time

ar = Array(c1, c2)

EndCol = EndTime.Column - 1
rw = EndTime.Row

If EndTime.Value = 0 Then
RunTime = 0
Exit Function
End If

If Not IsNumeric(EndTime.Value) Then Exit Function

If EndTime = 0 Then
RunTime = 0
Exit Function
End If

StartTime = 0
For col = startCol To EndCol
If InStr(1, Cells(LabelRow, col), c1) + _
InStr(1, Cells(LabelRow, col), c2) 0 Then
StartTime = Cells(rw, col).Value
End If
If StartTime 0 Then Exit For
Next col

If StartTime = 0 Then
RunTime = 0
Exit Function
End If

STstr = Format(StartTime, "0000.00")
ETstr = Format(EndTime, "0000.00")

stH = Left(STstr, 2)
stMIN = Mid(STstr, 3, 2)
stSEC = Right(STstr, 2)

etH = Left(ETstr, 2)
etMIN = Mid(ETstr, 3, 2)
etSEC = Right(ETstr, 2)

temp = TimeSerial(etH, etMIN, etSEC) - TimeSerial(stH, stMIN, stSEC)

RunTime = CDbl(Format(temp, "hhmm.ss"))

End Function