Excel Automatically Date and Time Stamp When Data is Entered but Don't Change When Data is Modified

okay so you want to automatically date and timestamp your data entries so i'm going to show you two methods to achieve this one with a simple formula so for example if i add a value here automatically returns the date entered if i was to change that it would keep the date entered the same now the second method uses a little bit of vba so here if i enter some data it returns the date entered as before but also i get a column last updated so for example if i went back to this first data entry here currently last updated is 12 17 i'll change this to a different number it returns the current date and time but keeps the original date entered the same so let's see how this can be achieved first thing i'm going to do is format these cells with the date and time format so with the cell selected right click format cells and i'm going to go to the custom category down the side there and in the type box i'm going to type the format for date and time so that's dd slash mm slash yyyy space hh colon mm click on ok now to use a formula for this solution we're going to use the if function and the first thing i need to check is whether a2 is not empty if it is empty i don't want to return the current data time the second thing i'm going to check is that b2 is empty if it already contains a date and timestamp i don't want to overwrite it now i'm using a nested if structure here if you are used to using the ifs function and if you have the ifs function in your version of excel then please feel free to use that comma so if these two things are true i want to return the current date and time and i can use the now function for that the now function returns the current date and time according to your system clock now i need to deal with the value of false for this nested if so if b2 is not empty then i want to keep the value in b2 so i'm just going to return b2 and then close the bracket for the nested if now i need to deal with the value of false for our first if so if a2 is empty then i want to return an empty text string in b2 in other words keep the cell blank now if i press enter i get a blank cell but if i type something in column a what i'm expecting is a date and time stamp now the reason it's not working is because within my formula i've got a circular reference i'm referring to the cell address b2 when the formula is in b2 and to get around that what we need to do is go to file options formulas and under calculation options you need to tick enable iterative calculations so if i tick this and i can change the number of maximum iterations to one click on ok so what i'm going to do is i'm going to delete my data there type something in and it gives me the date and time that i entered that value now let's copy this down and let's try for other rows you can see it works really well now i've waited a couple of minutes and i just want to see whether the date stamp changes if i change the data here so if i change that value there you can see that it stays the same which is exactly what we wanted we only want the date and time that the data was originally entered okay so that is the method if you want to use a formula the next method uses a little bit of vba code let's go over and see how that can be achieved now with this solution we're going to record both the date and time the data was originally entered but also when it was last updated now because we're using a vba macro you will need to save the workbook as a macro enabled workbook so you can see here that i've chosen excel macroenabledworkbook.xlsm do not save the workbook as a normal excel workbook you will also want to show the developer tab on your ribbon it won't show by default so right click on one of the other tabs go to customize the ribbon and tick developer down here on the right hand side then go to the developer tab and click on the visual basic button that will open up the visual basic editor on your screen now you will need to see the project explorer and if you can't see it just go to view project explorer and then you need to look for the workbook that you're currently working in so i'm in the demo workbook and then you need to double click on the sheet that you want the macro to apply to so i've double clicked on sheet 2 there then at the top above the code window where it currently says general from the drop-down list select worksheet and then in the second drop-down list over here change selection change to change then you can delete these two lines of code at the bottom here so what we're doing is we're running this macro whenever there's a change to sheet 2. i'm going to paste in the code that we need for this solution i'll walk you through it and i'll also leave a link in the description of this video to this code first thing you need to understand is this word target target is the cell that you are currently changing or it may be cells the next thing you need to understand is this variable that i've declared here my data range and i've said that my data range is the range a2 to a10 that may be different for your worksheet the next line of code checks whether the cell that i'm changing is within this data range if it isn't then i don't need to do anything i only need to perform this code if i'm changing a cell within this range so assuming that a cell within this data range has been changed i need to first of all check whether there's already a value in the date entered column so as a reminder i'm checking whether a value has already been entered in column b and i'm doing that by using the offset method so i'm checking whether the cell offset by no rows and one column to the right is empty so if it is empty i want to put the current date and time in that cell so that deals with column b now we need to deal with column c last updated now column c is easier to deal with all i need to do is specify two columns to the right of my target cell and i need to enter the current date and time and i enter that date and time irrespective of whether there is already a date and time in the cell now let's just check whether this macro works so if i enter a value here and you can see it puts the date originally entered in column b and the date last updated in column c now if i go forward in my time machine a few minutes so the time is now 13 13. so i'll change this value to 56 you can see that the date entered stays the same but the date last updated changes to 1313. let's try it down here see it works now what happens if i delete one of these values you can see it leaves the date entered and date last updated as it is now you may want that if there is a deletion you may want to know when it was deleted which had been that time you can see there it's changed the time there to 1314 when i deleted it or you may want to get rid of the dates and time when you delete the values in column a so let me show you how to do that so i've entered some more lines of code here the first thing i've done is declared another variable my data and that's because down here i'm using a for each next loop so what i want to do is loop through each cell in the my data range so range a2 to a10 and i want to check whether those cells are empty so i go through each cell in column a and if the cell is empty then i clear the contents of column b and column c so now with that in place if i delete that value there it deletes the corresponding dates entered and last updated now one thing i didn't explain was this line of code here on error resume next and that's to deal with situations where the end user deletes multiple values in column a basically it prevents any errors occurring in that situation okay that's all i wanted to cover in this particular video hopefully that's useful if it is please give me a thumbs up and subscribe and i'll see you next video [Music]

Channel: Chester Tugwell

Views: 164,588

Keywords: excel date stamp when cell changes, timestamp formula in excel, excel date stamp when cells in row are modified, insert timestamp in excel, date stamp cell entry excel, excel automatic timestamp when data is entered, excel timestamp when cell changes without vba, date and time stamp excel, automatic date stamp excel, timestamp formula in excel that doesnt change, automatically time stamp entry in excel automatically, automatic date and timestamp in excel

Id: 23-RoK1p7tg

Length: 9min 46sec (586 seconds)

Published: Fri Aug 12 2022