Excel Automatically Date and Time Stamp When Data is Entered but Don't Change When Data is Modified
HTML-код
- Опубликовано: 22 июл 2024
- Download the featured file here: www.bluepecantraining.com/aut...
In this video I demonstrate how to date and time stamp your data entry. The NOW() function can be used to return the current date and time but this will change whenever the worksheet is edited. We want the date and time stamp to be fixed. There are two methods I demonstrate for achieving this: the first uses a formula and the second uses a VBA macro. The VBA macro solution also displays when the data was last modified.
Table of Contents:
00:00 - Introduction
00:52 - Formula method for creating a date and time stamp that doesn't change
04:04 - Use a VBA macro to create a date and time stamp
------------------------ 
Хобби
Thank you so much for clearing that up and giving the very detailed explanation that I finally understand how to set the date and time without it moving or recalculating every time I move a cell.
Excellent tutorial and excellent explanation of these concepts.
Thank you Chester.
With this video you just saved me lots of time. Thanks a lot for uploading it.
This is exactly what I've been looking for, pefect. Now you helped me again, very nice videos, easy to follow and understandable... 😁
Thank you so much for sharing this. Very much appreciated.
Thanks! My problem was applying the function to every cell in the column, so your video helped with that.
That was really helpful, thank you!
thanks from France, very usefull. Have a nice day ;)
Thanks For This....
Creating It's Work & Very Helpful
You are amazing! Thank you for helping me figure this out!
Great Explanation! Thank you.
Thank u so much, it was very detailed
Excellent ! You earned a new subscriber.
Very appreciated. Thank you so much
Hello, thank you for the great tip! I I have only one issue when my column A is dynamically given by a formula and in my case column C is then not updated as expected ( and is kept blank )
Thank you exactly what I needed
Great examples. Excellent presentation of the process. You earned a subscriber. :)
this was sooooo helpful! thank you!
Thanks for the great information! What if I want the date in B1 to change every time the data in A1 changes?
Gracias profesor, gracias por compartir su tiempo y conocimientos, y lo que es mejor, gratuitamente.
👍
thank you for the video, this is the function i was looking for. sadly i cant enable iterative calculation in online excel (i am using the file across multiple devices, so i need it online ) any other options?
SO COOL!! THANK YOU!!!
Thanks very much, you're a life saver!
You're welcome!
Great video Great explanations but one small thing i was wondering, since this changes the value of NOW when you finish typing in the cell, how do i make it so the NOW is only updated ONLY if the value is actually changed and not just by doubleklicking and then enter. I may look more into it myself but completely new on almost anything related to coding
Hi, any idea why this formula is causing an error in another formula? for example, I am trying to put if formula in C2 in order to substract the time shown in B2 from the time shown in D3, but the value if C2 give error although D3 is bigger than B2
thanks helped me so much
Thank you for this!
Awesome, thanks !!!
Chester, how does the format of the vba change if there are multiple data points in a row, and multiple rows, such as an inventory sheet. I can send you a sample.
Thanks for this man! May I ask how to nest this code in one worksheet, like i want to have multiple timestamps in one Sheet
I appreciate this info. I want to have a current date and time in 2nd column, when something is entered or updated in 1st column. I want cell in 2nd column to be empty if cell in first column is empty. Only thing is, i want the date and time to update, everything there is an update in 1st column, not the original date and time. Please help on this.
Hello, thanks for the helpful video!
To take this a step further, am I able to use the VBA macros to update the Last Modified Date/Time when multiple columns are edited rather than just A in this example?
Thank you, you are adding value to RUclips
I used your formula and it works great if the Field that you are entering the data into is simply a data field. In my spreadsheet, I want to use a dropdown list to populate the data field and that kills the formula. Any suggestions?
Thank you,
My range has dropdown list of values to choose (data check rule). How can I make it working in my case? Because changing any cell in my column with dropdown lists doesn't put date anywhere...
thanks
Hi, many thanks for this video. It is really helpful!
How does the vba code change if there are multiple data points/columns in a row that I want to check? i.e. I want to have the modified date automatically if someone changes ONE OF the column's data in that row?
Could you help about this issue?
Hi did you ever figure this out? I have the same question!
@@fongvang8426 Same! I have two columns in once sheet I would like to apply this to. I having a hard time figuring it out.
Looking for something slightly different. Is there a video for....
- row timestamp updates whenever ANY data in the row is updated
- timestamp isn't just for the first entry, but for whenever there's an update to anything in the row
did you manage to find a solution to this?
hi, were you able to find a solution?
I haven't tried it, but I imagine you could use the IFERROR formula to next each if inside the next until it finds a match.
Did this formula just stop working for anyone else?
This is working so well ❤
Hey I hit and its turned into 1k likes congrats and thanks for explaining just could you also tell us about name of user how made last entry in data too?
can it posibly include the user who changed the data (for shared excel files)?
After typing in the macro, how do I run it? I can’t figure it out and I’m a noob when it comes to vba! Thanks in advance!
How would the VBA code look if it were a table and not a range?
Could you help me figure out a modification to this? I would like it so any Data input in columns E - N creates the fixed intial date in column B only of that same row. Then I would like for any data input into columns P - T to create a changing last edited date in column C only of that same row. I've come close but I can't quite get it.
Thanks!
Many thanks!
Awesome video mate. Quick question. I tried using the formula option for a excel table and I've gone into Options>Formula and checked the iterative option. The formula seems to work on the first row but any subsequent rows in a table defaults back to the year 1900.
Me too 1899
Same
Is there a way to write that formula to track any change to three columns that are side by side?
Why do I get a "there is a problem with this formula" when I type it exactly as shown in the video?
I had to put the formula in the language my excel is in. For example i had to change IF to ALS. this fixed it for me.
I inputted the code a few days ago and when I reopened today, its not working. Any suggestions?
Good Day Sir, what if there is more than 1 Data Column? For example, our target data is from Column A to Column E, but we only want 1 column for Date Entered and 1 for Last Updated.
How do you copy the formula down to the other cells as you did at 3:33 in the video
What would macro code look like if I wanted Now in Cell A20 When a change is made to any cell on the worksheet but not when it is opened and viewed?
Hi Thank you , question i have pms file i want to know is there any way to excel calculate by own and find 3rd date between start and finish date with formula and gives best match date between 2 start and finish date , it's example that can expand for 1000 tasks
Imagine i have 3 tasks with 5 days duration start is 1/1/22 and finish is 1/12/22 so first task start 1 day and 3rd task start 1/7/22 now I want excel calculate and find best date between in 2 tasks that must be 1/4/22
Now is there anyway excel calculate and find this date, this is can use for 1000 tasks more or less that I need to find it
1000000 tnx if you help me 🌹🌹🌹🌹
Thank you 🌹🌹
It is not working if in column A I have formulas, or the data is writen by a SCADA program like InTouch visualisation. It is working only If I write in column A from keyboard. Any sugestion, please.
Thanks a Lot for sharing this. But, Can the name of the person be also displayed who's changing the file or who initiated the data entry in the cell, as everyone has to sign in to office 365, so each person is already signed in with their credentials.
Does this vba code applicable to data derived by filter function?
if we are changing iterations to 1 wouldn't it effect all other formulas in case we have any?
I'm so close! Why is it returning the year 1900 date?
Hello Chester. Can you let me know how can I make the formula work in Google Spreadsheet? I have tried many combinations, changing comma to semicolon and it didn't work.
You have to edit the iteration option in spreadsheet. You can change it from 'File'-'Option'-'Calculation'-'Iterative calculation'
@@taccooooo is it possible to keep the iteration option selected for always on? everytime i close the workbook, the selection goes away
Brilliant. However, 🚩 I'm encountering an issue with the code. When I edit the data later, Excel freezes for a while and may need to restart! What should I do to resolve this problem?
I'm working on a spreadsheet for work and Im not experienced at excel at all. My question is what if I WANT it to change when data is modified? I have my date in column A, and would the date to change if anything in the row along the rest of the column is modified. How would one achieve this I can't seem to find anything online. Cheers
Hello Chester I am great fan of yours and learned lot of excel formulas by seeing your vedios now i need a help hope you would help me in our office we have a daily tracker in that we have 12 agents and works in 24/7 shifts we work on incident tickets as soon as the ticket arrives we have enter the ticket number in that sheet and change the color of the cell manually according to the time the ticket arrived for eg. if ticket came in between 8am to 9 am it will be green if it is between 9 am to 10 am then red if it is between 10 am to 11 am then purple so on so instead of changing the color manually i need a formula or a steps so based on current time when the data entered into a cell the color should change please suggest
You could use the Conditional Formatting to set up colors depending on the time you insert :) You find it located on the first page under "Home"
Am I missing something? If I use the first method it works, but if i then close Excel and open a new empty file, that setting is switched off again by default and if I then open the first file, it is off and gives circular ref again? If I then use the VBA method and save the file in Sharepoint for users to access, it will not run a macro enabled file. So what do i do now? I want to put the file on Sharepoint so more than one manager can complete transactions, but also want to capture the transaction date as a fixed value.
Hi, I have tried to use this but excel doesn't recognise as a formula? and won't let me press enter
ME TOO i have the 2007 one maybe it is old and does not support it?
I am using google spreadsheet, how do i enable developers tab on google sheet. I dont want to use MS excel
I used the formula in a sheet, and it worked perfectly until a couple of days ago. Now whenever I enter data, it returns 1/0/1900. I don't know what's wrong. Can you help, please?
what if you have two columns of inputs on a same day?
Hi there, I was able to understand and input the formula above, but when I enter data on the first row the time enter perfectly, but when I insert data on the second row, it will change the time on the first. Can you tell me why? Here is my formula =IF(C10"",IF(B10="",NOW(),C10),"") Please help. Thank you.
Nevermind, I figured it out. Thanks anyway.
@@user-df4nh9iq1x What was the solution to this? I'm having the same issue.
I want to create a daily expense tracker within one sheet, but i want the date to change automatically every day and clear the previous data when i say create new daily expenses
TRYING TO USE THIS ON MY MAC IN NUMBERS BUT I DON'T SEE THE CALCULATION OPTION TO ENABLE ITERATIVE CALCULATION
Please somebody help! I copied everything shown in the first step BUT when I change data in the first cell, it changes time on the right - without any VBA etc. Just copied the formula exactly as per instruction 🤨🤷♀️
Great thank you. My date is showing as 00/01/1900 though
Thanks a lot for sharing. This was much helpful but I have a question. How can I lock the “date entered” row and the “last updated” row? This will enable employee not to manually adjust the date and time. I have tried using the below command but the rows turned blind afterwards Please help.
Sub protectmydata()
Dim strPassword As String
Range("N12:N298,C2,H5:H7,H9,D5:D7,D9,L5:L7,L9,O12:O298,P12:P298,Q12:Q298").Select
Selection.Locked = True
Selection.FormulaHidden = True
strPassword = InputBox("Pls enter the password")
ActiveSheet.Protect Password:=strPassword
End Sub
first simple formula works great but it fails when I add new row to existing table... then date column shows 01/01/1000 00:00
is there any way even after deleting the cell value the time stamp should remain same?