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.
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
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
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?
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?
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?
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?
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 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
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.
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
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?
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.
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?
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?
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.
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.
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?
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.
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?
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
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 )
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.
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.
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
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
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.
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.
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 🤨🤷♀️
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 🌹🌹
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
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.
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 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.
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
Excellent tutorial and excellent explanation of these concepts.
Thank you Chester.
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.
Gracias profesor, gracias por compartir su tiempo y conocimientos, y lo que es mejor, gratuitamente.
👍
Thanks for the great information! What if I want the date in B1 to change every time the data in A1 changes?
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?
Hi sir can I check the date and time when a spreadsheet was updated first.
Thank you so much for sharing this. Very much appreciated.
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?
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?
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?
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.
Did this formula just stop working for anyone else?
This is working so well ❤
It's working for one column.when I added multiple , i am not able to get the date for added columns.😢
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! My problem was applying the function to every cell in the column, so your video helped with that.
You are amazing! Thank you for helping me figure this out!
Is there a way to write that formula to track any change to three columns that are side by side?
I'm so close! Why is it returning the year 1900 date?
How do you copy the formula down to the other cells as you did at 3:33 in the video
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
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.
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
Thank you, you are adding value to TH-cam
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.
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.
Thank you for this!
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?
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?
this was sooooo helpful! thank you!
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?
thanks from France, very usefull. Have a nice day ;)
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.
Thanks For This....
Creating It's Work & Very Helpful
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.
Great Explanation! Thank you.
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?
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 inputted the code a few days ago and when I reopened today, its not working. Any suggestions?
what if you have two columns of inputs on a same day?
Excellent ! You earned a new subscriber.
That was really helpful, thank you!
Thank you exactly what I needed
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.
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?
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"
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 u so much, it was very detailed
thanks helped me so much
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.
How would the VBA code look if it were a table and not a range?
can it posibly include the user who changed the data (for shared excel files)?
Awesome, thanks !!!
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
Thanks very much, you're a life saver!
You're welcome!
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!
Very appreciated. Thank you so much
SO COOL!! THANK YOU!!!
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?
Thank you,
TRYING TO USE THIS ON MY MAC IN NUMBERS BUT I DON'T SEE THE CALCULATION OPTION TO ENABLE ITERATIVE CALCULATION
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.
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
I am using google spreadsheet, how do i enable developers tab on google sheet. I dont want to use MS excel
Thanks!
Many thanks!
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
Great thank you. My date is showing as 00/01/1900 though
Great examples. Excellent presentation of the process. You earned a subscriber. :)
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
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
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
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 🤨🤷♀️
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 🌹🌹
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
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.
is there any way even after deleting the cell value the time stamp should remain same?