Previous Topic Next Topic

VBA code: calculating time difference

Closed
vathsala - Updated on Jan 10, 2022 at 12:04 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 4, 2021 at 12:37 PM
Hello,

this is my code i try update the data automatically but the macro is not working of the updating part
datetime auto_cycle tool time_difference 

Sub MyMacro()
    Dim Total As Double
    Dim Timein As Date
    Dim Timeout As Date
    Dim a As Integer
    Dim b As Integer
    a = 2
    b = 0
    For a = 2 To 21
    b = a + 1
    If a >= 22 Then
        a = 2
    Else
        Timein = CDate(Cells(a, 1).Value)
        Timeout = CDate(Cells(b, 1).Value)
        Total = TimeValue(Timeout) - TimeValue(Timein)
        Debug.Print Total
        Debug.Print Format(Total, "hh:mm:ss")
        Cells(a, 4).NumberFormat = "hh:mm:ss"
        Cells(a, 4).Value = Total
        Debug.Print "'Number of hours = '" & Total * 24
     End If
   Next a
   
      
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then
        Call MyMacro
    End If
     
End Sub


this is my master file suppose i change the value in A COLUMN corresponding data should updated automatically without trigger the macro assigned button how to do this 

01-01-2021 :10:10:10 1 1 00:00:10
03-01-2021 :10:10:20 1 2 00:00:02
13-10-2021 :10:10:22 1 3 00:00:04
13-10-2021 :10:10:26 1 4 00:00:04
13-10-2021 :10:10:30 1 5 00:00:06
13-10-2021 :10:10:36 1 6 00:00:04
13-10-2021 :10:10:40 1 7 00:00:03
13-10-2021 :10:10:43 1 8 00:00:00
13-10-2021 :10:10:43 1 9 00:00:06
13-10-2021 :10:10:49 1 10 00:00:03
13-10-2021 :10:10:52 1 11 00:00:08
13-10-2021 :10:11:00 1 13 00:00:10
13-10-2021 :10:11:10 1 12 00:00:04
13-10-2021 :10:11:14 1 14 00:00:05
13-10-2021 :10:11:19 1 16 00:00:04
13-10-2021 :10:11:23 1 17 00:00:04
13-10-2021 :10:11:27 1 18 00:00:02
13-10-2021 :10:11:29 1 19 00:00:04
13-10-2021 :10:11:33 1 20 00:00:05
13-10-2021 :10:11:38 1 21 00:00:07
13-10-2021 :10:11:45 1 12
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 4, 2021 at 12:37 PM
Hi Vathsala,

Not sure how your sheet is made up, but when you have times like "hh:mm:ss" in column A and you want the time difference in column D, then you can give the code below a try: 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

With Target.Offset(0, 3)
    .NumberFormat = "hh:mm:ss"
    .Value = Target.Offset(1, 0).Value - Target.Value
    If .Row = 2 Then Exit Sub
    If .Value < 0 Then .Value = vbNullString
End With

With Target.Offset(-1, 3)
    .NumberFormat = "hh:mm:ss"
    .Value = Target.Value - Target.Offset(-1, 0).Value
End With
End Sub


Best regards,
Trowa

Similar discussions

The system time is invalid
LeonardPromise -
Anonymous User -

5 responses