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

[@alexvolk6063]

With this video you just saved me lots of time. Thanks a lot for uploading it.

[@michaelsvenson2456]

This is exactly what I've been looking for, pefect. Now you helped me again, very nice videos, easy to follow and understandable... 😁

[@traytgamer9552]

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

[@YeeLiTan]

Thanks for the video! I am now finally able to make it

[@IvanCortinas_ES]

Excellent tutorial and excellent explanation of these concepts. Thank you Chester.

[@thomasjulian6976]

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

[@nickwright9901]

did you manage to find a solution to this?

[@19941994Gaurav]

hi, were you able to find a solution?

[@ahench22]

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.

[@carlchristopher2534]

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.

[@RC-bv2yz]

Thanks for the great information! What if I want the date in B1 to change every time the data in A1 changes?

[@melisisk7871]

Thanks for the video I want to do same thing but excel file will be online or Sharepoint because of that macros and this formula are not working in online. Is there way to do this in online or Sharepoint files?

[@melisyaliniz8244]

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?

[@fongvang8426]

Hi did you ever figure this out? I have the same question!

[@King-eu7ie]

@@fongvang8426 Same! I have two columns in once sheet I would like to apply this to. I having a hard time figuring it out.

[@billparkinson1002]

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?

[@KendalynSarka]

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?

[@donquicovelez]

Gracias profesor, gracias por compartir su tiempo y conocimientos, y lo que es mejor, gratuitamente.

[@ChesterTugwell]

👍

[@Blessedandfavoured24]

Thank you so much for sharing this. Very much appreciated.

[@philtaylor3098]

Massive thank you.

[@leigh982]

Thanks, it works for me, but how do I do it for multiple cells? I've tried copy and pasting the code with changing the cells in the code but I doesn't work?

[@kiNgToMoDo]

Why do I get a "there is a problem with this formula" when I type it exactly as shown in the video?

[@vliegendepyton]

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.

[@1Telcontar]

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...

[@derekfaust6531]

Did this formula just stop working for anyone else?

[@niralakhatun5348]

This is working so well ❤

[@fazil1211]

It's working for one column.when I added multiple , i am not able to get the date for added columns.😢

[@MartinOrkuma]

Thanks! My problem was applying the function to every cell in the column, so your video helped with that.

[@BenZivkovic]

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?

[@MasonHoutteman]

I'm trying a variation of this. But the cells are not returning the NOW value.. any thoughts as to why I am getting the date 01/00/1900 Here is my formula =IF(E177="Submitted", IF(H177="", TODAY(), H177), "")

[@mostafaokasha3137]

Thank you, you are adding value to KZbin

[@Cameron_T]

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

[@ryangrinter2743]

You are amazing! Thank you for helping me figure this out!

[@fazil1211]

Thanks for the tutor. Could you please support me in getting for the following. Already have the date captured for column A,is there any possibility to capture the date of entery in column B ?.please help

[@rickmartinez9999]

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.

[@rahulpanchal5887]

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?

[@sahilverma3484]

Hi sir can I check the date and time when a spreadsheet was updated first.

[@skylerposton_EDP_Photo]

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.

[@paramveerssachdeva]

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.

[@In-Sanity]

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?

[@arthurvanderlays6781]

How do you copy the formula down to the other cells as you did at <a href="#" class="seekto" data-time="213">3:33</a> in the video

[@jesschodor8949]

I'm so close! Why is it returning the year 1900 date?

[@AshokKumar-hd7pz]

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

[@Riverdon-c7r]

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"

[@benjamincohen8449]

thanks from France, very usefull. Have a nice day ;)

[@MG-xs8bi]

Great Explanation! Thank you.

[@zzaimzulkefli7747]

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.

[@kgpearce1]

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?

[@guillaumepaquet1551]

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 )

[@stephenshockley6500]

Is there a way to write that formula to track any change to three columns that are side by side?

[@bilalshaikh3583]

Thanks For This.... Creating It's Work & Very Helpful

[@milindshiralkar6284]

Does this vba code applicable to data derived by filter function?

[@maggieschanck]

this was sooooo helpful! thank you!

[@danielguizienmartin1825]

Thank you for this!

[@ImNadeem.1]

if we are changing iterations to 1 wouldn't it effect all other formulas in case we have any?

[@sorinnews4620]

Excellent ! You earned a new subscriber.

[@osjohnmagpale167]

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

[@BillEagan-oo7lf]

I inputted the code a few days ago and when I reopened today, its not working. Any suggestions?

[@Merastov]

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.

[@ExcelWithChris]

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.

[@LauraPrescott-zs4jt]

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?

[@chellesearle930]

Hi, I have tried to use this but excel doesn't recognise as a formula? and won't let me press enter

[@cybertalos]

ME TOO i have the 2007 one maybe it is old and does not support it?

[@thuytienlives8487]

That was really helpful, thank you!

[@user-qm6ue1tr7p]

Thank you exactly what I needed

[@consciouslifestyle9018]

what if you have two columns of inputs on a same day?

[@narieco]

Thank u so much, it was very detailed

[@karol9330]

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.

[@taccooooo]

You have to edit the iteration option in spreadsheet. You can change it from 'File'-'Option'-'Calculation'-'Iterative calculation'

[@19941994Gaurav]

@@taccooooo is it possible to keep the iteration option selected for always on? everytime i close the workbook, the selection goes away

[@omarkhattab9594]

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

[@dlynch4008]

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.

[@michaelcai4439]

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.

[@ralphmedija769]

Me too 1899

[@zethjumps8951]

Same

[@kevinmcm19]

thanks helped me so much

[@sethbrickner7313]

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!

[@johnhindmarsh9428]

Thanks very much, you're a life saver!

[@ChesterTugwell]

You're welcome!

[@LulShogun]

is there any way even after deleting the cell value the time stamp should remain same?

[@user-ug6cj9em2b]

TRYING TO USE THIS ON MY MAC IN NUMBERS BUT I DON'T SEE THE CALCULATION OPTION TO ENABLE ITERATIVE CALCULATION

[@airecai-poweredrecruitment6206]

I am using google spreadsheet, how do i enable developers tab on google sheet. I dont want to use MS excel

[@puynoonnisachon]

Very appreciated. Thank you so much

[@ian.the.nobody]

can it posibly include the user who changed the data (for shared excel files)?

[@prasathj7436]

Awesome, thanks !!!

[@fabiankeppler315]

How would the VBA code look if it were a table and not a range?

[@d.j.martin]

SO COOL!! THANK YOU!!!

[@user-oc1we3to2q]

Thank you,

[@user-hd6xb4lo7u]

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

[@viktoriakireeva5860]

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 🤨🤷‍♀️

[@joeylaird77]

Great thank you. My date is showing as 00/01/1900 though

[@TheMrWARLORD]

first simple formula works great but it fails when I add new row to existing table... then date column shows 01/01/1000 <a href="#" class="seekto" data-time="0">00:00</a>

[@TelesisREI]

Great examples. Excellent presentation of the process. You earned a subscriber. :)

[@user-df4nh9iq1x]

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.

[@user-df4nh9iq1x]

Nevermind, I figured it out. Thanks anyway.

[@stewtheman]

@@user-df4nh9iq1x What was the solution to this? I'm having the same issue.

[@zaz4667]

thanks

[@robl417]

Thanks!

[@ChesterTugwell]

Many thanks!

[@mohammadafsari4686]

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 🌹🌹

[@samuelumeofia9125]

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