Using the HISTORIAN.TIMEINSTATE Function
- Last UpdatedJan 25, 2024
The HISTORIAN.TIMEINSTATE function retrieves a time-in-state summary for a specified list of tags.
Syntax
HISTORIAN.TIMEINSTATE(TagList, StartTime, EndTime, Interval, Details, Down, Ascending, DiscreteFormat, Filter)
-
TagList - Required. The range of cells containing the list of tag names you want to retrieve information about.
-
StartTime - Required. The date and time to start the data summary, expressed as a serial date number.
-
EndTime - Required. The date and time to end the data summary, expressed as a serial date number.
-
Interval - Required. The time interval over which values will be retrieved, expressed as a number of days. For example, 1/24 represents an interval of one hour, while 1/4 represents an interval of 6 hours.
-
Details - Required. A comma-separated list of properties to retrieve for the selected tags. The following properties are supported: Count, Minimum, MinimumContained, Maximum, MaximumContained, Average, AverageContained, Total, TotalContained, Percent, PercentContained, StartDateTime, EndDateTime, OpcQuality, InterpolationType, and Resolution.
-
Down - Optional. If TRUE, the tags are listed in rows. If FALSE, the tags are listed in columns. If no value is specified, the default value of TRUE (rows) is used.
-
Ascending - Optional. If TRUE, data is sorted in ascending order by time. Otherwise, the data is sorted in descending order by time. If no value is specified, the default value of TRUE (ascending) is used.
-
DiscreteFormat - Optional. The format in which to display discrete values. Valid options are label, numeric, or boolean. If no value is specified, the default value of label is used.
-
Filter - Optional. A comma-separated list of state values. If a list is provided, only values matching the provided list of states will be included in the output. If no value is provided, then the output is not filtered.
Example
Recreating the simple report from the previous example, we select the Function option instead of the Static option. This results in a formula being inserted into the spreadsheet, instead of static data. There is a brief pause while communicating with the server, then the retrieved data is displayed.
The formula generated using this example is:
=HISTORIAN.TIMEINSTATE(Sheet1!A2, TODAY() + (0), NOW(), 1/24, "Count,Minimum,Maximum,Total,StartDateTime", FALSE, TRUE, "label", "OFF,ON")
You can modify the formula, and the results refresh automatically to reflect your changes. You can also use the function directly, without using the add-in panel. For example, the formula in cell B6 is replaced with:
=HISTORIAN.TIMEINSTATE(Sheet1!A2, TODAY() + (0), NOW(), 1/6, "Count,Maximum,Total,StartDateTime", FALSE, TRUE, "label", "OFF,ON")
The displayed report is regenerated, and now the time interval is every 4 hours (1/6), and the minimum time property is no longer displayed.
If your version of Excel does not support dynamic arrays, a message similar to the following displays:
In this case, although you can still use the custom function, the size of the output range cannot be refreshed automatically. For example, if you run the function with a time interval of 1/6 (every 4 hours) and then want to change the interval to 1/12 (every 2 hours), the output range cannot be automatically resized to accommodate the additional results. You must manually select the new output range, edit the function parameters, and then press Ctrl-Shift-Enter.