Elapsed time IF condition is met

alexnattrass 

If you want to apply the elapsed time recording to multiple rows in a table where column S is named "Duration Operational," and column I is named "Status." The timer should also work when the value in column I changes based on an IF formula. To achieve this, you can modify the VBA code as follows:

vba code

Dim StartTimes() As Double
Dim IsTiming() As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject
    Dim cell As Range
    Dim rowIndex As Long
    
    On Error Resume Next
    Set tbl = Me.ListObjects("YourTableName") ' Replace "YourTableName" with your table name
    On Error GoTo 0
    
    If Not tbl Is Nothing Then
        If Target.Column = tbl.ListColumns("Status").Index Then
            rowIndex = Target.Row - tbl.HeaderRowRange.Row
            If Target.Value = "Operational" Then
                If Not IsTiming(rowIndex) Then
                    StartTimes(rowIndex) = Timer
                    IsTiming(rowIndex) = True
                End If
            Else
                If IsTiming(rowIndex) Then
                    Dim ElapsedTime As Double
                    ElapsedTime = Timer - StartTimes(rowIndex)
                    IsTiming(rowIndex) = False
                    tbl.ListColumns("Duration Operational").DataBodyRange.Cells(rowIndex, 1).Value = ElapsedTime
                End If
            End If
        End If
    End If
End Sub

Here is how this modified code works:

1. It detects when the "Status" column (column I) changes in a table named "YourTableName." Replace "YourTableName" with the actual name of your table.
2. It tracks the start time and end time for each row where the "Status" changes to or from "Operational."
3. The elapsed time is recorded in the "Duration Operational" column (column S) for the respective row.
4. This code will work even when the value in column I changes based on an IF formula or any other method.

To use this code, press ALT + F11 to open the VBA editor, insert a module, paste the code into the module, and save your workbook. Make sure you have a table named "YourTableName" with columns "Status" and "Duration Operational" as specified.

Once you've applied this code, it should track the elapsed time for each row in the table based on the "Status" changes.

Alternative to VBA, if you prefer not to use VBA, you can achieve a similar result using Excel functions to calculate the elapsed time when a condition is met. However, please note that this method won't provide real-time tracking like a stopwatch; it will calculate the elapsed time between the first occurrence of "Operational" and the latest change in the status.

Here is a step-by-step guide to implementing this without VBA:

1. In an empty cell, such as J4, enter the following formula to capture the timestamp when "Operational" first appears in cell I4:

=IF(I4="Operational", IF(J4="", NOW(), J4), "")

This formula checks if I4 contains "Operational." If it does and J4 is empty, it records the current time using NOW(). If I4 doesn't contain "Operational" or J4 is not empty, it displays an empty string.

2. Drag the fill handle (a small square at the bottom-right corner of the cell) in J4 down to copy the formula for as many rows as you need.

3. In another cell, for example, K4, calculate the elapsed time when the status changes from "Operational" to something else. Use the following formula:

=IF(J5<>"", J5-J4, "")

This formula calculates the difference between the current timestamp in J5 and the previous timestamp in J4 when I4 changes from "Operational" to something else. If J5 is empty, it displays an empty string.

4. Copy the formula in K4 down for as many rows as you need.

This approach will record the timestamps when "Operational" first appears and when it changes to something else. It will calculate the elapsed time for each occurrence of "Operational" to the subsequent change in status. This method does not provide real-time tracking but allows you to capture the time intervals between status changes.

If this is not the desired result for you, please add more information in the next message. Information such as Excel version, operating system, storage medium, Excel file extension, etc.