Excel Date and Time Stamp

Question: How do you create a date and time stamp in an Excel table without using VBA?

The problem that you will encounter with this problem is that the formula you want to use is =NOW() or =TODAY(). But this formula is dynamic, which means it will automatically update and change the date and time whenever anything else on the spreadsheet is changed. What we need to do is change the formula from dynamic to static.

1. Create a New Worksheet (File > New > Blank Worksheet) and add your titles for the Excel table, where the data will be stored.




2. In cell D3 (column D, row number 3) you need to enter the following formula
   
   
   =IF(B3="","",IF(D3="",NOW(),D3))
   
   
   This means that IF cell B3 is empty then do not enter a date and time. IF cell B3 is not empty and IF cell D3 is empty, then enter the date and time.
   
   




3. Finally, you will need to change the settings within your Excel spreadsheet to stop any Circular References - which is a formula error you get when you try to include your current cell into the formula like we have done here!
   
   
   To remove these errors, select Tools > Options and then select the Calculation tab. Now make sure that the Iteration box is ticked.
   
   




4. And that's all folks!!!
   
   
   Your finished Excel Date Stamp Table should look like this one below, and will record individual dates and times (static dates) for each action.