Excel VBA: Track Elapsed Time for an Operational Status Cell
Question details
The user needs to measure how long cell I4 remains set to Operational and save the elapsed time when the status changes.
- Product
- Microsoft Excel VBA or WPS Spreadsheets with VBA support
- Device & OS
- not provided
- Scenario
- A worksheet uses cell I4 as a status field. Timing should begin when I4 changes to Operational and end when it changes to another value.
- Observed behavior
- The status changes are not automatically recording the duration that I4 remains Operational.
Save the workbook as a macro-enabled .xlsm file and confirm that macros are enabled. Decide where to store the result; this solution uses J4 for elapsed seconds and K4 for the saved start timestamp.
Use Worksheet_Change to time the Operational status in cell I4
Use this solution when the timer should start and stop automatically whenever the value in I4 changes.
The start time is stored in K4 instead of only in a VBA variable, so it remains available if the workbook is saved or the VBA project is reset. The elapsed duration is written to J4 in seconds when I4 changes from Operational to another value.
In Excel, right-click the worksheet tab that contains I4 and select View Code. In WPS Spreadsheets, open the VBA editor and select the corresponding worksheet object.
Paste this code into the worksheet code window, not into a standard module: Private Sub Worksheet_Change(ByVal Target As Range) Dim statusCell As Range Set statusCell = Me.Range("I4") If Intersect(Target, statusCell) Is Nothing Then Exit Sub Application.EnableEvents = False On Error GoTo CleanUp If CStr(statusCell.Value) = "Operational" Then If Len(Me.Range("K4").Value) = 0 Then Me.Range("K4").Value = Now Me.Range("K4").NumberFormat = "yyyy-mm-dd hh:mm:ss" End If ElseIf Len(Me.Range("K4").Value) > 0 Then Me.Range("J4").Value = (Now - CDate(Me.Range("K4").Value)) * 86400 Me.Range("J4").NumberFormat = "0.00 \"seconds\"" Me.Range("K4").ClearContents End If CleanUp: Application.EnableEvents = True End Sub
Return to the worksheet, change I4 to Operational, and confirm that K4 receives a start timestamp. Change I4 to another value and confirm that J4 records the elapsed time in seconds.
If you prefer minutes, change the calculation in the VBA code to `(Now - CDate(Me.Range("K4").Value)) * 1440`. For hours, use `* 24` instead of `* 86400`.
Track worksheet status duration with WPS Spreadsheets VBA
WPS Spreadsheets can use a worksheet-change macro in VBA-supported editions to monitor I4, save the Operational start time, and calculate the elapsed duration in J4 when the status changes.
- 1. Open the VBA editor: Open the workbook in WPS Spreadsheets, select the worksheet tab, and open its code window from the VBA editor.
- 2. Add the Worksheet_Change event: Paste the event code that monitors I4, writes the start timestamp to K4, and records the elapsed duration in J4.
- 3. Enable macros: Use the workbook's security or macro settings to allow VBA code to run, then save the file in a macro-enabled format when required.
- 4. Verify the result: Set I4 to Operational and then change it to another status. Check that K4 stores the start time and J4 displays the elapsed duration.
Frequently Asked Questions
Where should the Worksheet_Change code be pasted?
Right-click the worksheet tab, choose View Code, and paste the code into that worksheet's code window. Do not paste it into a standard module because the worksheet event must belong to the specific sheet containing I4.
Why store the start time in K4 instead of only using a VBA variable?
A worksheet cell preserves the timestamp when the workbook is saved and is less likely to lose the timing state if the VBA project is reset. K4 can be hidden or moved if it is not part of the visible worksheet layout.
How can I record the elapsed time in minutes or hours?
The code calculates seconds with `(Now - startTime) * 86400`. Use `* 1440` for minutes or `* 24` for hours, and update the NumberFormat in the same step.
What happens if I4 is already Operational when the workbook opens?
Worksheet_Change runs only when the cell changes. If I4 may already contain Operational when the file opens, add a Workbook_Open routine or manually retrigger the status after opening so the start timestamp can be created.
Why does the timer stop when I4 changes to another value?
The event checks whether I4 still equals Operational. When it does not, the code subtracts the timestamp in K4 from the current time, writes the result to J4, and clears K4 for the next timing cycle.
