Nov 03 2023 07:29 AM
Nov 03 2023 07:29 AM
Is it possible to record elapsed time IF a condition is met? For example, I'd like to record the amount of time that cell I4 (Status) contains the string "Operational". This Status is likely to change regularly, but would need the elapsed time recording from the previous value. Almost like a stopwatch starting and stopping, but not re-setting.
Nov 04 2023 07:28 AM
Here is how you can create a VBA macro to track the time when cell I4 contains the string "Operational":
Dim StartTime As Double Dim ElapsedTime As Double Dim IsTiming As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$I$4" Then If Target.Value = "Operational" Then If Not IsTiming Then StartTime = Timer IsTiming = True End If Else If IsTiming Then ElapsedTime = Timer - StartTime IsTiming = False ' Store the elapsed time in a cell or variable as needed ' For example, you can store it in cell J4: ' Range("J4").Value = ElapsedTime End If End If End If End Sub
This VBA code will do the following:
You can customize the code to store the elapsed time as needed, such as in a specific cell or a variable, by uncommenting and modifying the appropriate lines.
Now, when cell I4 contains "Operational," the timer will start, and when it changes to a different value, the elapsed time will be recorded. The text were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Nov 06 2023 02:02 AM
Thanks very much for your response. I am having a few issues getting it to work. Can you check I have input correct? Also, I'd like to fill this down the column of a table (where column S has the name "Duration Operational", and column I = "Status"). Can this be written in so that the formula works down all rows?
Also, column I will change its string based on an IF formula. I'm assuming this timer will be able to record the change in the cell, even if data isn't manually input?
Thanks again for your help, it's much appreciated!
Nov 06 2023 03:14 AM
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:
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:
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:
=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.
Nov 06 2023 04:06 AM
Thanks again... I'm still struggling to get any output. I have changed the VBA to reflect the name of the table (Foss_List), however nothing is showing in the 'Duration Operational' column, despite there being a 'Status' as "Operational".
Excel version - Office 16
file type .xlsm
storage medium - one drive
OS - Windows 10 Enterprise