@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:
- It detects when the "Status" column (column I) changes in a table named "YourTableName." Replace "YourTableName" with the actual name of your table.
- It tracks the start time and end time for each row where the "Status" changes to or from "Operational."
- The elapsed time is recorded in the "Duration Operational" column (column S) for the respective row.
- 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:
- 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.
