Timestamp
By Richard Rost
3 years ago
Automatically Add Date & Time to a Column in Excel
In this Microsoft Excel tutorial I'm going to teach you how to add an automatic timestamp by putting the date and time that a row was edited in a particular column. We're going to accomplish this using just a little bit of VBA.
Emily from Portland, Oregon (a Platinum Member) asks: I have a spreadsheet where I track my transactions which are usually payments to different vendors and accounts. Is there a way that I can automatically have the current date and time entered into the date column instead of having to manually type that in all the time?
Members
Members will learn some double-entry accounting and how to have a duplicate and opposite transaction created in the row below the credit account for the specified debit account.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisite
Links
Recommended Course
Want More Excel?
Most of what I do is Microsoft Access, so if if you want to see me post more Excel videos, make sure to comment below: "I want more Excel!"
Keywords
excel 2021, excel 2016, excel 2019, excel 365, microsoft excel, ms excel, ms excel tutorial, #msexcel, #microsoftexcel, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #techhelp, update date of transaction, update date in row, timestamp, date and time stamp, worksheet change event, target.row, target.column, named range, double-entry accounting, excel update transaction date column automatically, Excel Automatically Date and Time Stamp When Data is Entered, How to Automatically Enter Date & Time After Data Entry In Excel, Automatically Timestamp Data Entries in Excel
Subscribe to Timestamp
Get notifications when this page is updated
Intro In this video, I will show you how to set up an automatic timestamp in Microsoft Excel, so that a date or time is entered in a specific column whenever a row is edited. Using a bit of VBA, I'll walk you through tracking changes, updating timestamps when values change, and using named ranges to keep your code flexible even as your sheet evolves. This tutorial is aimed at users who want their Excel spreadsheets to automatically record when transactions are updated, and covers everything you need to get started, including how to save your workbook and basic VBA editor setup.Transcript Welcome to another TechHelp video brought to you by ExcelLearningZone.com. I am your instructor Richard Rost.
In today's video, I'm going to show you how to automatically add a timestamp, putting the date and optionally only the time that a row was edited in a particular column. For example, you update the value in the amount column and the date will automatically update next to it. This is a developer-level video, so it's going to involve a little bit of VBA. You can see some of it right there. It's not that hard.
Emily from Portland, Oregon, one of my platinum members, says, "I have a spreadsheet where I track my transactions, which are usually payments to different vendors and accounts. Is there a way that I can automatically have the current date and time entered into the date column instead of having to manually type that in all the time?"
So let's say you've got your spreadsheet set up like this: you've got the description and whatever the transaction is. You've got the amount and then you have the date. You can do date or time or both - it's up to you. Let's give it a little splash of color. I always like to have a little splash of color.
Let's say you made an MX payment, $500. If you want to put today's date in there, the keyboard shortcut is Control+Semicolon, and then Enter. That will put today's date in there automatically for you. It's a keyboard shortcut. I'm going to take these and just to define this, because this kind of stuff bothers me.
I am using the ISO date standard. If you're not familiar with that, go check it out. I have another video that talks about it.
Let's say you want to also track the debit for that account. The MX payment, so 500 is the credit. If you're doing proper double entry, you want to have, let's say, it comes out of your 123 checking account. That'll be negative 500. Again, you want today's date in there, so Control+Semicolon.
If you don't always want to have to hit that Control+Semicolon, I know it's one keystroke - well, technically two, since you have to hold Control and the other key.
We can have this date automatically update itself whenever this column is changed. I've seen some functions online. Some people have alternatives, where you can put a nested IF statement here and it will put the current date in there when this date is added if you add a new row.
If you're like me, what I like to do is keep a summary sheet that's got all of my accounts, all my credit cards on it. So I've got my Discover. There might be 150 and I might make that, let's say, on the fifth. That's also coming out of 123 checking, right? Minus 150. Every debit should have a credit.
We'll talk a little bit more about double entry accounting in the extended cut. If you want to say, "Okay, this one cleared," I'm going to leave the sheet the way it is and just zero these out. You want that date to update. You don't want to have to always come over and put the date in.
That's why we're going to have a little bit of code. When I change this column, this cell updates to today's date. That's the purpose of today's class.
You've got your sheet all set up. You rarely add new rows, but when you update this stuff, you want this date to update.
If you've never done any VBA programming before, go watch this video. It's about 15 minutes long. It teaches you all the basics: how to get into the VBA editor, how to turn the developer tab on, that kind of stuff. I'll get you started learning Excel VBA. When you're done with that, come back to this video. You'll find a link to this and any other video I mention below in the description. Click on it now. Go watch it.
Let's go to our Developer tab and click on Visual Basic to bring up the VBA code editor. There it is right there. I'll add that over here.
We're going to go to this sheet. Double-click on Sheet1. We're going to go to the worksheet object. I don't want SelectionChange. SelectionChange happens whenever you move your selection. That event fires. I don't want that.
I want, drop this one down, I want Change. I want when the worksheet changes - when you change a value somewhere in that sheet. I'm going to delete the SelectionChange. I don't care about that.
Right here. This is going to fire every time I change something in my sheet. Let's just have a messagebox do something right now. Messagebox "Hi mom." Save that.
Now we have to save this again as an .xlsm file. It has to be macro enabled. We'll call this "timestamp." Call it "your finance sheet" or whatever you want.
Now, anytime something in this sheet changes, it's going to messagebox "Hi mom." I'll type in a new transaction and I'm home. See? Anytime I do anything anywhere, boom. I'm home. If I delete this thing, delete, I'm home.
I don't want to say "hi mom" every time the sheet changes. Let's take a look at exactly where that change took place. That's what this "target" is up here (target as Range). It lets you say, okay, where did this change occur? Just come in here and say messagebox target, just like that. Save it. Make a change. Enter. Look at that.
ASD. That's the value that I put in that cell. That's what's at "target," which is a - now, how do I get that it's A8? Well, you could say messagebox target.column and maybe a colon and target.row. Column and row. Save it. Put something else in. Enter.
Now we're in column 1, row 8. You get a number for the column. That's okay. You can work with that. Knowing this is column 1 and this is column 2, I can say, well, wait a minute. If the change happens in column 2 (that's the only one I really care about), then we can update the date in column 3.
Let's hit OK. Let's come back in here. We're going to get rid of this. We're going to say:
if target.column = 2 then
' do some stuff
end if
Okay, what's the stuff we're going to do? For now, let's just say messagebox "Hi there again." We'll just make sure that it's happening in column 2. Save it. Come back out here.
Now if I change this guy, nothing happens. I can put stuff in this column all day. I can put stuff over in this column all day. But if I come over here, I put something in here, "Hi there." See that? Now Excel knows that I made a change in column 2 (column B).
Back to our code. I don't want to just say "hi there." I want to set the date in column 3, which is column C, if the user changes the value in column 2. So here's how that's done:
Range("C" & target.row).Value = Date
That's how you refer to a specific cell. Range column C, and the target row. Go to column C and set that value equal to today's date. In Excel, TODAY() is a function you use in the cells, but in VBA you use Date. I mix those together all the time. If you want the date and time, you can use Now. For now, I'm going to put Date in here.
Come back over here, type in something, 95. There's today's date. Down here, 456 checking, negative 95. Boom. There you go. Look at that.
Any time you change up here, let me manually change this to just 4.1. Come back over here. Now I have another MX payment that happens today. It's 300 bucks. 300 here, and the date updated. Negative 300 below it. There you go. That's how you can change that value based on when this one is updated.
Personally, I don't like hard coding rows and columns into my VB code. I don't like having to have this always be C or have this always be column 2 for B. Users are going to come in here, they're going to insert columns, they're going to move things around.
What I do is set up named ranges and work with those ranges. For example, our date column. Select column C and go over here to a named range and call that "dateCol" (enter). If you don't know what a named range is, I cover that in my Excel Expert Level 1 class.
Now that this is "dateCol", I can come in here and instead of saying Range("C" & target.row), I can say Range("dateCol").Rows(target.row).Value
This says: Go to the range "dateCol" and go to its row, whatever the target row is. Then we get the same results.
Likewise, I'll set up an amount column, "amountCol." This could move too, if they insert something out here. Now instead of saying target.column = 2, we'll say target.column = Range("amountCol").Column
Range("amountCol") is going to be just that one column. What column is it? It'll return that.
Now, even if I come in here and insert something in front of that, like maybe a number, 123, whatever, this is still going to work. Bam. Now this and this are both named ranges and I'm not referring to actual rows and columns in my code.
If at all possible, try not to refer to actual row and column numbers or letters in your code, because if you move something around, then your code stops working. Excel doesn't automatically rewrite your code for you, like how it rewrites your formulas. If you put in here, for example, SUM of column B, if you move that cell or you move that column, the formula gets rewritten. Not so much with your VBA code, so you have to be careful with that.
That happens to me all the time.
If you like this stuff and want to learn more, in the extended cut for the members, I'm going to show you how to automatically update the reciprocal item for double entry accounting. For example, you've got your MX payment, which is $300. You want to put your negative payment automatically in the opposite account where the money came out. There's your credit, there's your debit. As soon as you type in, like, 200 here, it'll update the dates and it'll also make this guy negative 200.
We're going to do that in the extended cut for the members. Silver members and up get access to all of my extended cut videos. There are lots of them.
For those of you who are my Access students, we're also going to be doing something with double entry accounting in tomorrow's TechHelp in Microsoft Access. That's coming up too, and that's also going to have its own extended cut. Lots coming up.
That is your Excel TechHelp video for today. I hope you learned something. Live long and prosper, friends. I'll see you next time.
To become a member, click the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. All of the members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks.
Gold members get access to download all of the sample spreadsheets that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use and more.
Platinum members get access to all of the previous perks, plus all of my beginner full courses and one new expert course every week. These are the full length courses found on my website and not just for Excel. I also teach Word, Access, Visual Basic, ASP, and lots more.
When you sign up to become a member, I need you to email me and tell me you want more Excel. The vast majority of my videos are for Microsoft Access because that's been my focus for the past few years. However, I'm happy to add more Excel videos if I get more Excel members, so make your voice heard and I'll make lots more TechHelp lessons for Excel.
Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. What is the primary goal of the video tutorial?
A. To teach double entry accounting in Excel
B. To show how to automatically add a timestamp when a row is edited in Excel
C. To demonstrate advanced charting techniques
D. To explain how to use PivotTables
Q2. Which Excel feature is used in the video to automate tasks based on cell changes?
A. Data Validation
B. VBA (Visual Basic for Applications) and worksheet event handlers
C. Conditional Formatting
D. Power Query
Q3. What is the keyboard shortcut to quickly enter today's date into a cell in Excel?
A. Alt+T
B. Ctrl+D
C. Ctrl+;
D. Shift+Enter
Q4. Why is hardcoding column letters or numbers into VBA code discouraged?
A. It makes the code run slower
B. Because rows and columns never change in Excel
C. The code can break if columns are added, removed, or moved
D. It is required for advanced Excel users
Q5. How does the code identify which row was changed by the user?
A. By checking the value in a fixed cell
B. Using the 'target' parameter in the Change event
C. By looping through all rows in the worksheet
D. By using the SelectionChange event
Q6. When updating the date automatically via VBA, what built-in function is used to get today's date?
A. NOW()
B. TODAY()
C. Date
D. GETDATE()
Q7. What is the significance of using named ranges in your VBA code?
A. It colors the cells automatically
B. It avoids hardcoding columns, making the code more flexible
C. It makes the worksheet faster
D. It protects the worksheet from changes
Q8. What event in the worksheet's code module should be used to detect changes to cell values?
A. Worksheet_Deactivate
B. Worksheet_SelectionChange
C. Worksheet_Change
D. Worksheet_Activate
Q9. If you want to include the current time along with the date in your timestamp, which VBA function should you use?
A. Time
B. DateSerial
C. Now
D. CLOCK()
Q10. What must you do when saving the workbook so that your VBA macro will work?
A. Save it as a .csv file
B. Save it as a .pdf file
C. Save it as a macro-enabled workbook (.xlsm)
D. Save it as a web page
Q11. What happens if you move columns around in your worksheet but have relied on named ranges in your code?
A. The code will break immediately
B. The code will automatically adapt to the new layout
C. The code will delete all data in the moved columns
D. The code will ask you to update the columns manually
Q12. What is the main difference between the TODAY() function in Excel formulas and the Date function in VBA?
A. TODAY() is a VBA function, Date is an Excel formula
B. Both can only be used in formulas, not in code
C. TODAY() is used in worksheets, Date is used in VBA code
D. There is no difference
Q13. Why did the instructor suggest watching an introductory VBA video first if you have never done VBA programming?
A. Because VBA is only available in certain Excel versions
B. To learn the basics of the VBA environment before working on this tutorial
C. Because you need to pay for VBA
D. The instructor does not suggest this
Q14. In the VBA code example, what does the following code do?
Range("C" & target.row).Value = Date
A. Sets every cell in column C to today's date
B. Sets the value in column C of the changed row to today's date
C. Sets the value in column C of every row to 0
D. Sets the value in column B of the changed row to today's date
Q15. What benefit do membership levels such as Gold and Platinum offer to viewers according to the video?
A. Early video access only
B. Exclusive extended-cut videos, downloadable resources, and additional perks
C. Free computers
D. No benefits at all
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-C; 7-B; 8-C; 9-C; 10-C; 11-B; 12-C; 13-B; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Excel Learning Zone focuses on how to automatically add a timestamp in Excel, so that the date, or optionally just the time, is updated in a certain column whenever you edit a row. This is especially useful for anyone who is tracking transactions, payments, or any other data where you need to know exactly when a change was made. This lesson is geared toward developers and requires a bit of VBA, but it is not overly complex.
One of my Platinum members, Emily from Portland, asked if it was possible to automatically fill in the current date and time in a date column instead of entering it manually every time she adds a new transaction. This is a common request, and it's definitely doable.
If you have a spreadsheet set up with columns for description, amount, and date, you might want the date to fill in automatically when you enter or edit a value in the amount column. Normally, you can use the keyboard shortcut Control plus Semicolon to enter the current date, but this still requires you to press keys for each entry. If you want this to happen automatically, VBA can handle it.
To make things clear, I always use the ISO date format in my examples. If you are not familiar with this, I have another tutorial that covers why this standard is helpful.
You may also be managing basic double-entry accounting. For instance, when you enter a $500 payment to a vendor, you may want to show a corresponding -$500 entry in your checking account. Again, you'd like the date for both entries to update automatically when the transactions are recorded.
Many online examples rely on formulas or nested IF statements for simple timestamp tasks, but those only help when adding new data and not when editing existing rows. For true automation every time you edit a row, VBA is necessary.
For those who like to keep a summary sheet of all their accounts, with credits and debits tracked, you might want the date to update whenever you make changes without having to remember to enter it each time.
To accomplish this, you need to add a small amount of VBA to your sheet. If you've never worked with VBA before, I recommend starting with my introductory video, which covers how to open the VBA editor and turn on the Developer tab. Once you're comfortable with that, you can proceed with this tutorial.
Within the VBA editor, you'll want to access the worksheet object for your data. The key event to use is Change, which fires every time you alter a value in the sheet. This means the macro can respond whenever you make an edit, not just when you move around the cells. The event provides a parameter called Target, which tells you exactly which cell was changed, including its row and column.
By examining the Target object, you can determine if the change happened in the column you care about (for example, the Amount column, usually column 2 or column B). If that's the case, you then set the adjacent cell in the Date column (column C) to today's date. In VBA, you use the Date function for just the date, or Now if you prefer date plus time.
The benefit is clear. After this code is set up, any time you make a change in the Amount column, the Date column updates automatically with today's date, making your transaction log much more accurate and reliable.
However, hard coding column letters or numbers in your VBA code isn't ideal, because if users rearrange columns or add new ones, your code breaks. To solve this, Excel allows you to use named ranges. For example, name your Date column range as "dateCol" and your Amount column as "amountCol." In the VBA code, refer to these names instead of fixed columns, so your code remains functional even if the worksheet layout changes.
This approach is much more robust, and I always recommend using named ranges when possible to avoid maintenance headaches if your spreadsheet grows or changes over time. Unlike formulas, VBA code does not automatically adjust when columns are moved, so using named ranges is a safer bet.
For those interested in advanced features, in today's Extended Cut for members, I will show how to automatically update the reciprocal entry for double-entry accounting. This means when you enter a positive value for one account, the negative value and timestamp are set automatically in the corresponding account. This makes double-entry accounting much easier and more accurate. Extended Cut videos are available for Silver members and above.
For Access students, I will be covering double-entry accounting in Microsoft Access in an upcoming TechHelp video, which will also have its own extended cut.
That wraps up today's Excel TechHelp lesson. If you want to learn more and get additional detailed tutorials, you can become a member with various levels of perks, including extended cut lessons, downloadable sample spreadsheets, my code vault, and access to complete courses for Excel and other Microsoft Office products.
If you'd like to see me create more Excel content, let me know when you sign up for membership, since most of my resources currently focus on Microsoft Access. I am happy to add more Excel tutorials if there is enough interest.
Remember, the main TechHelp videos will always remain free as long as you keep watching. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Automatically adding a timestamp to a column when a row is edited
Using VBA to detect changes in a worksheet
Understanding the Worksheet_Change event in Excel VBA
Displaying message boxes to debug and view change details
Identifying the changed cell's column and row using VBA
Updating a specific column with the current date when another column is edited
Using Date and Now functions in VBA for date and time stamps
Limiting code to run only for changes in specific columns
Referencing cells dynamically using column and row numbers in VBA
Creating and using named ranges in Excel
Updating VBA code to work with named ranges instead of hard-coded columns
Dynamically detecting column positions using named ranges in VBA
Ensuring VBA code works even when columns are moved or inserted |
