Table 7.

Instructions for calculating on-time or early/late data

Instruction	Description	Explanation	Alterations
1. Label a column for the earliest time data should be considered on time.	(a) Type “Earliest Time” into cell M1.
2. Enter earliest possible times that data can be considered on time.	(a) Select cell M2 and enter “10:00 a.m.” (b) Drag down M2 to cell M7 (this should highlight Cells M2:M7 and fill in the appropriate times).	For partial-interval data collection systems, data should not be entered until the interval elapses. Anything entered before the interval ends would not be representative of the entire interval.	“10:00 a.m.” can be changed for any appropriate criterion.
3. Label a column that will change the timestamp format.	(a) Type “Time Difference” into cell N1.
4. Enter a formula that will change the timestamp format to something that can be compared in other formulas.	(b) Select cell N2 and enter the formula [=TEXT(L2-M2,“h:mm:ss”)], then press ENTER (RETURN) on the keyboard. (“#VALUE!” will appear in the cell). i. Drag the formula down to cell N7.	This formula calculates the difference between the timestamp (L2) and the time set and step one (M2), then formats it to read as hour: minutes: seconds.
5. Label a column for the on-time criteria.	(a) Type “On-Time Criteria” into cell O1.
6. Enter the on-time criteria.	(a) Select cell O2 and type “0:05:00” into it. (b) Drag down to Cell O7. i. While O2:O7 is still highlighted, select the AUTOFILL OPTIONS which is a small box with an orange (+) that appears to the bottom right of the highlighted cells. 1. In the AUTOFILL OPTIONS dropdown menu, select COPY CELLS.	Using the autofill options when dragging the cell is the most efficient way to copy the cell without creating changes to the format or value.	The 5-min criteria is completely arbitrary and can be substituted with any time criteria entered in the appropriate format (h:mm:ss), such as 0:15:00 for 15 min.
7. Label a column that will change the on-time criteria format.	(a) Type “Criteria Format” into cell P1.
8. Convert the on-time criteria to the necessary format for calculations.	(a) Select cell P2 and enter the formula [=TEXT(O2, “h:mm:ss”)] (“0:05:00” should appear in the cell).	This formula is used to format the contents of cell O2 to match the format of cell N2 so that calculations can be made.
9. Copy the format for each interval.	(a) Drag down to cell P7.
10. Label a column that will calculate if data were entered on-time.	(a) Type “On-Time Calculation” into cell Q1.
11. Enter a formula that will calculate if data were entered on-time or not.	(a) Select cell Q2 and enter the formula [=IF((N2 < P2),1,-1)] (”#VALUE!” should appear in the cell).	This is conditional formula that reads if N2 (the difference between the timestamp and the time it was supposed to be entered) is less than P2 (the time criteria set in step 3) “1” will be produced, if N2 > P2, then “− 1” will be produced.	The outputs of “1” for on-time data inputs and “− 1” for data that were not entered on-time are used for ease of graphing described in Table 10 but can be substituted for other values.
12. Copy the formula for each interval.	(a) Drag down to cell Q7.
13. Label a column that will provide a textual indicator for on-time or early/late data.	(a) Type “On-Time?” into cell R1.
14. Enter a formula that will give you a textual indicator if data were entered on-time or early/late.	(a) Select cell R2 and enter the formula [=IF(Q2 > 0,“On-Time”, “Early/Late”)] (”#VALUE!” should appear in the cell).	This formula produces a textual indicator for whether the data were entered on-time or early/late. The formula reads that if cell Q2 (described in Step 11) is larger than 0, then it will produce the text “On-Time”. If Q2 is not bigger than 0 (i.e., − 1), “Early/Late” will be produced.	“On-Time” and “Early/Late” can be replaced with other desired labels. “Early/Late” data entries are grouped together in this example for ease of calculations and graphing but can be separated by adding another time criterion and calculating them separately using the methods described in this table.
15. Copy the formula for each interval.	(a) Drag down to Cell R7.