One way to enhance your spreadsheets is by adding checkboxes. Using checkboxes, you can make data entry easier for your users. And you can create formulas based on whether a checkbox is checked off or not. In the latest version of Excel, using Microsoft 365, it’s easier than ever to create multiple checkboxes in just seconds. And using checkboxes, you can also create timestamps that won’t change.
How to add checkboxes to your Excel spreadsheet in seconds
In Excel’s latest version, a checkbox can be added right from the Insert tab on the ribbon. Simply clicking on the checkbox button will insert a checkbox into your spreadsheet. To insert multiple checkboxes at once, first select all the cells which you want to contain checkboxes. Then, click on the checkbox button. The cells will now be filled with checkboxes.
Using checkbox selections in formulas
If a checkbox is selected, its value becomes TRUE, and FALSE if it is unchecked. Using an IF function, I can create a formula to check whether the value is TRUE or not, and based on that, determine the output. In the following example, I use checkboxes to determine if something has been received. If it is checked off, then the value is “Received” and if it’s not, then it will say “Not Received”
The formula is a straightforward and can be used to track whether something has been processed or not.
Creating a static timestamp in Excel using a checkbox
Let’s use a more complex situation in Excel, such as when we want to lock in the time of when someone received the order, not simply whether or not they received it. This involves a bit more complexity and we’ll need to allow for some circular references in this case, which is usually a no-no in Excel.
Here’s how we can get this scenario to work. Assuming the checkboxes are still in column A, the formula for cell B2 would be as follows:
=IF(A2,IF(B2=””,NOW(),B2),””)
The first argument in the IF statement checks to see if the checkbox is selected. It looks at A2 to see if it returns a value of TRUE or FALSE. Since it is a boolean argument, it is not necessary to state A2=TRUE; that goes without saying in this example.
If that condition is met, we move on to the next IF function. This one checks if B2 (the current cell, and thus, creating a circular reference) is blank. If it is, then the current date and time would be inserted with the NOW function. If it isn’t blank, then the value in B2 would remain as it is. And if the checkbox is A2 isn’t checked off at all, the value in B2 will be blank.
This formula initially won’t work and will give you an error stating that you’ve created a circular reference. To fix this, you need to allow for Iterative Calculations. This will ensure that Excel stops calculating after a certain number of attempts and take the last value. To activate this, go into Excel Options and under Formulas, select to Enable iterative calculation:
You can leave the default number of iterations. Now, your formula will calculate without the circular reference error. And when you check off boxes, the timestamps will all remain static until the checkboxes become unchecked again.
In the above screenshot I only show time, but that’s because I have formatted it to only show time. Since the NOW function contains both date and time, you can choose to show both, or just time or date individually.
The drawbacks of iterative calculations
Before you enable iterative calculations, however, you should consider the risks with doing so:
Performance Issues. Iterative calculations can significantly slow down Excel, especially in large and complex worksheets. Excel has to repeatedly recalculate formulas until either the maximum number of iterations is reached or the difference between the results of two calculations is below a certain threshold. This can be particularly noticeable if the workbook contains many formulas or data points.
Accuracy and Precision. The result of an iterative calculation can depend on the maximum number of iterations and the maximum change settings. If these are not appropriately set, the result may be inaccurate or not precise enough for your needs. This is because the calculation stops once the set limits are reached, not necessarily when the correct or most accurate result is found.
Risk of Other Circular Reference Errors. While iterative calculations allow you to use circular references intentionally, they also increase the risk of unintended circular references. Unintended circular references can lead to errors and incorrect data, making it difficult to debug and correct issues within your workbook.
Compatibility Issues. If you share your Excel workbook with users who have iterative calculations disabled, or if the workbook is opened in a different spreadsheet program that doesn’t support iterative calculations, the intended functionality may not work correctly. This can lead to errors or data inconsistency.
Potential for Infinite Loops. Incorrectly configured iterative calculations can lead to infinite loops, where Excel continuously recalculates without reaching a conclusion. This can cause Excel to freeze or crash, potentially leading to data loss if changes haven’t been saved.
As long as you understand the risks of enabling iterative calculations, they can help you in setting static date and time stamps in Excel.
If you like this post on Adding Static Timestamps in Excel Using Checkboxes please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.