Do you have a spreadsheet that needs to track dates? Whether it’s a shipping log, an inventory tracker, a sales order template, or just something to track when the last change was made to a cell, there’s an easy way you can create a date stamp in Excel with VBA.
Use checkboxes to make your spreadsheet more user friendly
If you have a spreadsheet where you want to track statuses, using checkboxes can be helpful. This way, someone can check or uncheck the status of an order. This can indicate whether it has been shipped, ordered, or completed. Excel has made it easier to insert checkboxes with a recent update. If you’re using Microsoft 365, then on the Insert tab on the Ribbon, you should see an option to insert a Checkbox:
When you click on this button, it will insert a checkbox right into the active cell that you’re on. Want to insert checkboxes into multiple cells at once? Simply select a range of cell and then click on the button:
If a checkbox is checked, its value is TRUE. If it is unchecked, then the value is FALSE. This is important to know when creating formulas.
Populating the date using the NOW() function isn’t useful for date stamps
If you want to enter the current date into a cell, you can use the CTRL+; shortcut. The problem is that it won’t change if you go to uncheck and re-check a checkbox. It’s a stale value and it isn’t a formula.
What you may be tempted to use is the NOW() function. However, the limitation here is that anytime the cell recalculates, it will refresh with the current date and time. It won’t hold the existing date stamp. You can create a circular reference and adjust iterative calculations. But there’s an easier way you can create a date stamp in Excel with just a few lines of code using VBA.
Creating a custom function using VBA
You can create a custom function with VBA. To do, start by opening up your VBA editor using ALT+F11. On the Insert menu, select the option for Module. There, you’ll have an empty canvas to enter code on. The custom function can simply contain one argument — the cell that contains the checkbox. This is to determine whether it is checked (TRUE) or unchecked (FALSE). If it is checked, then the timestamp will be equal to the current date and time. If it’s unchecked, then the timestamp will be blank, and so will the cell value.
Here’s the full code for the function:
Function timestamp(checkbox As Boolean)
If checkbox = True Then
timestamp = Now()
Else
timestamp = ""
End If
End Function
This function is now created. To use it within your spreadsheet, all you need to do is select a cell where you want the date and time to populate on. Then, assuming your checkbox is in cell A2, enter the following formula:
=timestamp(A2)
This will run through the VBA code to determine whether to populate the current date and time or not. Since there is no NOW() function present in this formula, it won’t recalculate with the current date.
Formatting your date and time
Even if the custom function work, you may notice that the value that it populates doesn’t look right. If you get a number or the time is missing from the date, then you’ll need to modify the cell format. To do that, select the cell and press CTRL+1. Then, select the Date category where you’ll see various date formats:
If you scroll down the list, there will be an option that shows the date and time:
If you use that format, then your date will now look correct, including both the date and time.
If you like this post on How to Create a Date and Time Stamp in Excel Using VBA, 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.