Help to derive a formula from the given example

[pol]

Emp sttime Endtime breake Hrs

XX 8:30 17:30 1:00
(endtim-sttime-breake)
YY 8:00 20:50 1.15
ZZ


With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow Step 1
If i > 2 Then
.Cells(i, 5).Value = (.Cells(i, 3).Value - .Cells(i,
2).Value) - (.Cells(i, 4).Value)
End If

Next
End With

sttime Endtime breake Hrs are hh:mm format

But the above formula is not working Please help

 

[Tim879]

You need i to be >= 2 in your If Statement.

use debug.print to track the value of i and you can see what I mean.
Example below.

Sub test()

With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Debug.Print lastrow
For i = 1 To lastrow Step 1
Debug.Print i & "outside if"

If i >= 2 Then
Debug.Print i & "inside if"
Debug.Print "(.Cells(i, 3).Value - .Cells(i, 2).Value) -
(.Cells(i, 4).Value)" & .Cells(i, 3).Value & " - " & .Cells(i,
2).Value & "- " & (.Cells(i, 4).Value)
.Cells(i, 5).Value = (.Cells(i, 3).Value
- .Cells(i, 2).Value) - (.Cells(i, 4).Value)
End If
Next
End With
End Sub

 

[Dave Peterson]

Maybe you just don't have column E formatted to show the correct time:

Option Explicit
Sub testme01()

Dim i As Long
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'if you're not going to process rows 1 and 2, then
'just start at row 3
For i = 3 To LastRow Step 1
.Cells(i, 5).NumberFormat = "[hh]:mm:ss"
.Cells(i, 5).Value = .Cells(i, 3).Value _
- .Cells(i, 2).Value _
- .Cells(i, 4).Value
Next i
End With
End Sub

And you're sure that the times in columns B, C, and D are hh:mm:ss, right? (You
did have a typo in that 1.15 value. I bet you meant 1:15????)

 

[pol]

Thanks for caring me

Please still problem

.Cells(i, 5).NumberFormat = "[hh]:mm:ss"
.Cells(i, 5).Value = .Cells(i, 3).Value _
- .Cells(i, 2).Value _
- .Cells(i, 4).Value

After giving this format "[hh]:mm:ss" the value is not calculated . But I
am removing this format and making the cell format as text we can write
..Cells(i, 5).Value = 'dsasa' this will come there .

But in format "[hh]:mm:ss" , the resulot is not coming there

Thanks pol

Maybe you just don't have column E formatted to show the correct time:

Option Explicit
Sub testme01()

Dim i As Long
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'if you're not going to process rows 1 and 2, then
'just start at row 3
For i = 3 To LastRow Step 1
.Cells(i, 5).NumberFormat = "[hh]:mm:ss"
.Cells(i, 5).Value = .Cells(i, 3).Value _
- .Cells(i, 2).Value _
- .Cells(i, 4).Value
Next i
End With
End Sub

And you're sure that the times in columns B, C, and D are hh:mm:ss, right? (You
did have a typo in that 1.15 value. I bet you meant 1:15????)

Emp sttime Endtime breake Hrs

XX 8:30 17:30 1:00
(endtim-sttime-breake)
YY 8:00 20:50 1.15
ZZ

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow Step 1
If i > 2 Then
.Cells(i, 5).Value = (.Cells(i, 3).Value - .Cells(i,
2).Value) - (.Cells(i, 4).Value)
End If

Next
End With

sttime Endtime breake Hrs are hh:mm format

But the above formula is not working Please help