0:00:00
Welcome to another Tech Help 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 the time that a row was edited in a particular column. So, for example, you update the value here in the amount column and the date will automatically update next to it. And this is a developer level video so it's going to involve a little tiny 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.

0:00:53
So let's say you've got your spreadsheet set up like this. You've got the description of whatever the transaction is. You've got the amount and then you have the date. You can do date or time or both, whatever. It's up to you. So let's give it a little splash of color. Always got to have a little splash of color, folks. So let's say you made an Amex payment, $500. Okay, and now if you want to put today's date in there, alright, the keyboard shortcut is control semicolon, alright, and then enter and that will put today's date in there automatically for you. It's a keyboard shortcut. Alright, and then I'm going to take these and write justify. This kind of stuff bothers me. Alright, and yes I am using the ISO date standard.

0:01:41
If you're not familiar with that, go check it out. I got another video that talks about it. And let's say you want to also track the debit for that account, right? The Amex payments, the 500s, the credit, the... if you're doing proper double entry, you want to have, like, let's say it comes out of your 123 checking account, right? That'll be negative 500, right? And again, you want today's date in there, so control semicolon. Now, if you don't always want to have to hit that control semicolon, I know, I know, it's one keystroke, well, technically two if you got to hold down the control and hit the other thing.

0:02:17
All right, if you want to bypass that, you can have this date automatically update itself whenever this column is changed. And yeah, I've seen some functions online, some people have some alternatives where you can put a nested if statement here and then it will put the current date in there when this date is added if you add a new row. But if you're like me, what I like to do is I like to keep a summary sheet that's got all of my accounts, all my credit cards on it, right? So I've got my Discover, okay? Okay, that might be 150 and I might make that you know on let's say the fifth Okay, and then that's also coming out of one two three checking All right, minus 150 every debit should have a credit, right?

0:02:59
And we'll talk a little bit more about double entry accounting in the extended cut But if you want to say, okay this one cleared so I'm gonna leave the sheet the way it is and just zero these out right Okay, you want that date to update. You don't want to have to always keep constantly coming over here and putting the date in. So that's why we're going to have a little bit of code so when I change this column, this guy updates to today's date. Okay, that's the purpose of today's class. So you got your sheet all set up. You know, you rarely add new rows but when you update this stuff, you want this date to update. Okay? See what we're trying to do here? Alright. Now 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. Go watch this. I'll get you started learning Excel VBA. When you're done with that, come on back to this video. You'll find a link to this and any other video I mentioned down below in the description below the video. Go click on it now. Go on, go watch it. All right, so let's go to our developer tab and we'll click on visual basic to bring up the VB code editor. There it is right there. Slide that over here. Okay. We're going to go to this sheet right here, double click on sheet one. We're going to go to the worksheet object. Now I don't want selection change.

0:04:26
Selection change happens whenever you move your selection. That event fires. I don't want that guy. Let me slide this over. All right. I want, drop this one down, I want change. I want when the worksheet changes, when you change a value somewhere in that sheet. All right. I'm going to fire every time I change something in my sheet. So let's just have it message box something right now.

0:04:56
Message box, hi mom. Alright, save that. Now we have to save this again as an XLSM file, right? It's got to be macro enabled, okay? And we'll call this guy, timestamp. Okay, you'll call it your finance sheet or whatever you wanna call it. All right, so now, anytime something in this sheet changes, all right, it's gonna message box, hi mom. So I'm just gonna type in new transaction.

0:05:27
All right, enter, boom, hi mom. See, anytime I do anything anywhere, boom, hi mom. Okay, if I delete this thing, delete, hi mom. All right, now I don't want to say hi mom every time the sheet changes. How about let's take a look at exactly where that change took place. That's what this target is up here. Target as a range comes in and lets you say, okay, where did this change occur? So just come in here and say message box target just like that. All right save it come out here make a change enter look at that. All right ASD ASD that's the value that I put in that cell. All right that's what's at target which is a8. Now how do I get that it's a8? Well you can say message box target dot column, and maybe a colon, and target dot row.

0:06:36
Right column and row. Save it, come back over here, put something else in, press enter. All right, now we're in column one, row eight. See that? Yeah, you get a number for the column. That's okay. We can work with that. All right, so knowing this is column one and this is column two, I can say, well, wait a minute.

0:07:02
If the change happens in column two, that's the only one I really care about, then we can update the date in column three. All right, so let's hit okay. Let's come back in here. And we're going to say if target.column equals 2, that's column B, then do some stuff and if. Okay, what's the stuff we're going to do? Well, for now, let's just say message box, hi there again. We'll just make sure that it's happening in column 2, right? Save it, come back out here. Now, if I change this guy, nothing happens, right? I can put stuff in this column all day I can put stuff over in this column all day okay but if I come over here put something in here either see that okay so now Excel knows that I made a change in column 2 column B all right back to our code now 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 you here's how this writes up. It's range and then C ampersand target dot row. Okay, that's how you refer to a specific cell. So it's going to be range column C. Yeah, here you use the letters, the other way you use the numbers.

0:08:26
I know, it's a little confusing, but you get used to it. Okay. C and the target.row. What row we're in, go to column C and set that value equal to today's date. Okay. Or excuse me, date. That's one thing with Excel is that today is a function that you use in the cells, but in VBA you have to use date. I mix those together all the time. And if you want the date and time you can use now. All right but I'm gonna put date in here. Okay all right now come back over here come right there and type in something 95. Oh there's today's date. That come down here four five six checking gets both of them and negative well i think i mean they can change it let me manually change this to just for one and i'll come back over here let's say now i got another annex payment that happens today right three hundred bucks three hundred here that the data updated negative three hundred below it.

0:09:40
There you go. That's how you can change that value based on when this one is updated. Now 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 one always be column 2 for B, right? Because users are going to come in here, they're going to insert columns, they're going to insert rows, they're going to move things around, okay. So what I do is I like to set up named ranges and work with those ranges. For example, our date column, okay, just select column C and we're going to go over here to a named range and call that date column, date, C-O-L, enter.

0:10:22
If you don't know what a named range is, I cover that in my Excel Expert Level 1 class. But now that this is date column, I can come in here and instead of saying range C, I can say range date column. And then the rest of this has to change just slightly. column dot rows and then target row like that. Okay this says go to the range date column and go to its row whatever the target row is and then we get the same results. All right and likewise I'll set up an amount column. Amount column like that because this could move to right if they insert something out here. So now, likewise, instead of saying the target column equals 2, we'll say the target column equals range of amount column dot column.

0:11:32
Range amount column is going to be just that one column. What column is it? It will return that. So now, even if I come in here and insert something in front of that, like maybe a number, I don't know, one, two, three, whatever, this is still going to work, watch. Because now this and this are both named ranges and I'm not referring to actual rows and columns in here. If at all possible, try not to refer to actual row and column numbers or letters in your code because you'll come in here, you'll move something around and then your code stops working because Excel doesn't automatically rewrite your code for you.

0:12:15
Like you know how it rewrites your formulas if you put in here like sum of column B or whatever? If you move that cell or you move that column, the formula gets rewritten, not so much with your VBA code. You got 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 of. There's your credit, there's your debit.

0:12:51
So as soon as you type in like 200 here, it'll update the dates and it'll also make this guy negative 200. Alright, makes sense? Alright, 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's lots of them. And for those of you who are my Access students, we're also going to be doing something with double entry accounting in tomorrow's Tech Help in Microsoft Access. So that's coming up too, so look forward to that. And that's also going to have its own extended cut. So lots coming up. But that is your Excel tech help video for today. I hope you learned something.

0:13:28
Live long and prosper, friends. I'll see you next time. How do you 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. Silver members and up will get access to all of my extended cut tech help videos, live video and chat sessions and other perks. Gold members get access to all 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. Now when you do sign up to become a member, I need you to email me and tell me I want more Excel.

0:14:21
The vast majority of my videos are from 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 tech help lessons for Excel. But don't worry, these free tech help videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free. If you enjoyed this video, please give me a thumbs up and post any comments that you have. I do try to read and answer all of them as soon as I can. Make sure you subscribe to my channel, which is completely free. Click the bell icon to select all and receive notifications when new videos are posted. Want to learn more? If you're watching this video on YouTube, just click the Show More link below the video to find additional resources and links.

0:15:07
You'll see a list of other related videos, additional information on the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time I post a new video click on the link to join my mailing list. If you have not yet tried my free Excel level 1 course check it out now. It's over 90 minutes long and it covers all the basics of using Microsoft Excel. And if you like level 1, Level 2 is just $1 and it's free for all members of my channel at any level, even supporters. Just email me and let me know you signed up as a member. Want to have your question answered in a video just like this one? Visit my tech help page and you can send me your question there. And while you're on my website, be sure to stop by and check out my Excel forum.

0:15:59
Be sure to follow my blog and of course you can find me on Twitter and YouTube. And as always, thanks for learning with ExcelLearningZone.com. I'm Richard Rost, see you next time. you