Table 5.
Instructions for creating an automatic timestamp for data entry per cell
| Instruction | Description | Explanation | Alterations |
|---|---|---|---|
| 1. Enable iterative calculations. |
(a) Select FILE then open then OPTIONS menu. i. Open the FORMULAS menu and click the check box to ENABLE ITERATIVE CALCULATIONS, then select OKAY. (b) Mac OS instructions are as follows. Select the EXCEL menu and open PREFERENCES. i. Open the CALCULATION menu and select the box next to USE ITERATIVE CALCULATION, then close the menu by selecting the EXIT Icon. |
Enabling Excel® to make iterative calculations is necessary for the timestamp formula to work appropriately. | Differences may be found in different versions of Excel®. PC instructions are based on Excel® 2013, and MAC OS instructions are based on Excel® 2016. Each version will have a way to enable iterative calculations within the calculations menu. |
| 2. Label the timestamp interval times. | (a) On SHEET2, type “Timestamp Intervals” into cell F1. | ||
| 3. Copy and paste the interval times. |
(a) Highlight cells A2:A7. i. Copy the highlighted cells by pressing CONTROL + C (COMMAND + C) on the keyboard. (b) Select Cell F2, then paste the copied cells by pressing CONTROL + V (COMMAND + V) on the keyboard. |
This is not a requirement but is done for organization and ease of viewing the raw data when it is entered. | Any added rows should be included. |
| 4. Copy the target behavior labels. | (a) Highlight cells B1:E1, then press CONTROL + C (COMMAND + C) on the keyboard. | ||
| 5. Paste the target behavior labels onto SHEET2. | (a) Select cell G1 and press CONTROL + V (COMMAND + V) on the keyboard. | ||
| 6. Enter the timestamp formula. | (a) Select cell G2 and then enter the formula [=IF(NOT(B2 = “”),IF(G2 = “”,NOW(),G2),“”)]. | The timestamp formula is a complicated conditional formula that basically reads that if cell B2 is not blank then G2 should display the time that a value was added to B2. | This formula can be edited and used anytime a timestamp is desired in Excel®. Simply replace “B2” with any cell you want to timestamp and replace both “G2” inputs with where you want the data to output to. |
| 7. Copy the formula to each cell. |
(a) Drag down to cell G7. (b) While G2:G7 is still highlighted, drag to cell J7. |
||
| 8. Format the cells. |
(a) While G2:J7 is still highlighted, on the HOME Tab in the NUMBER section, open NUMBER FORMAT dropdown menu. (b) Select TIME on the dropdown menu. |
All the cells must be formatted to register the input for the timestamp as time or it will not calculate correctly. |