THANK YOU, you're a life saver! If anyone wants their date to be inserted into column E for example, so that is column number 5, then change the code to If Not Intersect(Target, Range("B1:B1000")) Is Nothing Then With Target(1, 4)
Hello, Thank you very much for this video. It really helped me. One thing I have is "How can I lock the cells once the date and time are entered? " Really appreciate your help with this.
This Worked Great! Thanks, I did run into a problem with wanted to enter data in colun I and have the date show in column J. I first changed the 1, 2 to 1, 10 and the date showed up in the right spot. But I hadn't changed the A1:A1000 yet. When I changed that to I1:I1000 I didnt see the date. I then realized that i had to go back to change the 1, 10 back to 1, 2 and it worked. Thanks Again!
Is the anyway to make this work when a specific text is into the cell by formula? I have; Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Range("X1:X1000")).Value = "Sent" Then With Target(1, 10) .Value = Date .EntireColumn.AutoFit End With End If End Sub But I get Run error 91. Could you let me know how to correct please my friend?
Great Video :) Thanks for uploading!! It was indeed helpful and created an assignment within couple of hours after watching this video... One Question: If cells (Start, End and Total time) are locked, the function does not work. Do we have a solution to it.
This is what I needed... Thank you!!! only one question... can I chose in which collum the end result will be? For example... if I enter data in collum B, the result will be in collum C, but can I change that result to end up in collum A?
@@salahalfiky by replacing the "With Target()" with "With Range()" like this ↓ Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("C7:G7")) Is Nothing Then With Range("H7") .Value = Now .EntireColumn.AutoFit End With End If End Sub where my H7 is the cell where i want the update date.
Thank You. How would the code look like if I wanted to date stamp more that two separate columns. For instances if column A is updated, show the date in column B. If column D is updated, show the date in column E etc?
Hi.. I want to print the Time as explained in your video, but I am getting the Column 'A' value from live data which keeps updated automatically. How can I do it? With the change event, it's not being updated. Can you please let me know to do the same for the LIVE DATA?
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1:A10000")) Is Nothing Then With Target(1, 2) .Value = Date .EntireColumn.AutoFit End With End If End Sub
Hello DPtutorials - i need help we have a excel which is used as daily ticket tracker team enter the ticket based on the time the ticket arrived and changed the color for e.g. if the ticket arrived between 8 am to 9 am it is marked in green and the ticket which come between 9 am to 10 am it is in red like me every one hour the color changes and this file worked 24/7 so any formula to change the cell color based on the data entered in a cell on specific time
This is awesome!!!... Can you provide one solution? I have invoice date and due date, when enter due date, a value of amount, ie 225$ automatically enter to particular month column, ie in January. Could you help me out?
Thank you for the VBA code, it works perfectly. Just another question though, when i delete the cell which was manually updated for e.g. cells in column A, the date is still shown in the cells. Is it possible to clear the date as well when we clear the value of cells in column A? or does the deletion is considered an action and so the date will always be there unless removed manually? TIA
Sounds great. What if i want the current time and date also for 2nd column such as (first column start date and time in the next column end task date or time)?
@@dptutorials I also had to change the target from 1, 2 to 1, A. I tried all kinds of stuff to only end up crashing the program before coming up with this.
How to keep record of all date and time? History? I mean when I enter Today in any cell, it gets record of the current date, and later if i change tomorrow, it show show the yesterday's date and the tomorrow date both
Hi! Thanks for the video. I want to secure the date/time cel now for changing it. So when they need to fill in cell 1, cell 2 is giving the time from filling it in. Do you know how to do that? When I secure cell 2 is the macro not working anymore..
Hello. Is there a way to modify this code, to only enter the date if the Cell next to it has the text Yes in it? For example Column 1 - Resolved Y/N, Column 2 - Date Resolved
Thank you so much for sharing with us... but I have a question... that if i have a data in column B to Z. And I want to show the result in column A, and have more condition. Example: My data is from B1 to Z100, and if I change one of data in B1, the time in A1 have changed too. But if B1 have new update, and need me to accept, that A1 just update new time kha. Can you help me this one kha. Thank you so much.
i have a simuler code but doesn't seem to work if i wanted to do it in multiple collums. example: Collum "A": auto-Date/time, Cullum "B": information1, Collum "C": Information 2, Collum "D" blank ,Collum "E" an other Auto-Date/time. I've tried by creating a seperate coding page and i've tried by just creating a 2nd line for the second set of ranges. but neither seems to work. any chance you could help me fix that? Data must be next to each other. Only the last set of ranges work unless i comment either one of the sets out
can you apply the formula/code if changes are made to a row? I have a spreadsheet where we track events on each row, but would like a date to autopopulate if someone adds another row or makes changes to any cell within that row
What if I want the same function on multiple columns like I have a table where I record purchase orders in B1 (this can be down with the VBA code you have shared) but I want to record the "out of stock" date as well which would be in F1. Is there any workaround for that?
Nice tutorial, Hello sir your video are easily understand able and use full. I have one question, can we display the date and time only one cell only when the sheet updated?
Yes, you can, you can modify the range in the VBA code accordingly and apply a formula in a cell to fetch the max of all these dates and you can hide this column B.
If my cell value chaging/updating as per source data then will it update timestamp at what time value got modified?? I mean will it work in live Excel data ??
Thank You. How would the code look like if I wanted to date stamp two separate columns. For instances if column A is updated, show the date in column B. If column D is updated, show the date in column E?
What would be the code if I'm updating data in column D and I want date in column B. I changed "A1:A1000" with "B1:B1000" but this is giving me date in column E when updated data in column D
Hi there, I'm using a table in Excel for dashboarding reasons, and this doesn't seem to work when adding data into a table, only into a cell. Do you know if there's an edit to the VBA code to apply this to table data too? Many thanks!
Hi Fazal and Kwame, Please read the video title carefully, this trick is to capture the timestamp value. i.e., the time or date on which the adjacent cell has been modified last.
Not a scam, Just to maintain the description length, I have provided my access to google drive. will check the access rights, it should be easily downloadable.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then With Target(1, 2) .Value = Date .EntireColumn.AutoFit End With End If End Sub
Thank you my Friend. Your amazing
Thanks a lot.I welcome you to subscribe to this channel to enjoy more interesting videos.
@@dptutorials Thank you
if i put =today() or =now() formula in a cell today, if i open the spreadsheet tomorrow i want yesterday's date to be displayed in that cell
THANK YOU, you're a life saver!
If anyone wants their date to be inserted into column E for example, so that is column number 5, then change the code to
If Not Intersect(Target, Range("B1:B1000")) Is Nothing Then
With Target(1, 4)
Hello, Thank you very much for this video. It really helped me. One thing I have is "How can I lock the cells once the date and time are entered? " Really appreciate your help with this.
This Worked Great! Thanks, I did run into a problem with wanted to enter data in colun I and have the date show in column J. I first changed the 1, 2 to 1, 10 and the date showed up in the right spot. But I hadn't changed the A1:A1000 yet. When I changed that to I1:I1000 I didnt see the date. I then realized that i had to go back to change the 1, 10 back to 1, 2 and it worked. Thanks Again!
Great to hear!
Very good job!
I was looking for this solution for hours!
God bless you!
Glad it helped
Excellent explanation very clear and works! Thank you.
Glad it helped!
If you want to change the column that the date or time shows up in. In the VBA code change the With Target number. Example 1=A 2=B 3=C and so on
Is the anyway to make this work when a specific text is into the cell by formula?
I have;
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("X1:X1000")).Value = "Sent" Then
With Target(1, 10)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
But I get Run error 91. Could you let me know how to correct please my friend?
That was a tremendous time saver!!! Thank you
Great Video :) Thanks for uploading!! It was indeed helpful and created an assignment within couple of hours after watching this video...
One Question: If cells (Start, End and Total time) are locked, the function does not work. Do we have a solution to it.
Glad it helped!
Sir, how to wrap text the column by giving time and date in =now() formal because it shows # symbol when decrease the column - please clarify this.
This is what I needed... Thank you!!! only one question... can I chose in which collum the end result will be? For example... if I enter data in collum B, the result will be in collum C, but can I change that result to end up in collum A?
Yes you can make
@@dptutorials how
@@salahalfiky by replacing the "With Target()" with "With Range()" like this ↓
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C7:G7")) Is Nothing Then
With Range("H7")
.Value = Now
.EntireColumn.AutoFit
End With
End If
End Sub
where my H7 is the cell where i want the update date.
@@PikaJapan2012 thanks
@@salahalfiky DID IT WORK FOR YOU ? MINE STILL COMES OUT IN THE COLUM IN FRONT
Thank You. How would the code look like if I wanted to date stamp more that two separate columns. For instances if column A is updated, show the date in column B. If column D is updated, show the date in column E etc?
Did you find an answer for that? I came here looking for this answer :)
Hi.. I want to print the Time as explained in your video, but I am getting the Column 'A' value from live data which keeps updated automatically. How can I do it? With the change event, it's not being updated. Can you please let me know to do the same for the LIVE DATA?
You saved me some time. So thank you very much for your work.
Great to hear!, So welcome
@@dptutorials I need to click down my English lessons script time line by line, so I can generate a combined video with both scripts and my talk video
it works. Very helpful.
Thanks a lot
Thanks my friend, its Fantastic working fine, can we do same thing in google sheets ?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10000")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
Hello DPtutorials - i need help we have a excel which is used as daily ticket tracker team enter the ticket based on the time the ticket arrived and changed the color for e.g. if the ticket arrived between 8 am to 9 am it is marked in green and the ticket which come between 9 am to 10 am it is in red like me every one hour the color changes and this file worked 24/7 so any formula to change the cell color based on the data entered in a cell on specific time
So nicely conveyed
This is awesome!!!... Can you provide one solution? I have invoice date and due date, when enter due date, a value of amount, ie 225$ automatically enter to particular month column, ie in January. Could you help me out?
What changes do I need to make to the Macro if I want to record the date in column I for changes made to column H?
Thank you for the VBA code, it works perfectly. Just another question though, when i delete the cell which was manually updated for e.g. cells in column A, the date is still shown in the cells. Is it possible to clear the date as well when we clear the value of cells in column A? or does the deletion is considered an action and so the date will always be there unless removed manually?
TIA
Sounds great. What if i want the current time and date also for 2nd column such as (first column start date and time in the next column end task date or time)?
just in the vba code, mention the column by replacing in this text Range("b1:b1000")
@@dptutorials I also had to change the target from 1, 2 to 1, A. I tried all kinds of stuff to only end up crashing the program before coming up with this.
How to keep record of all date and time? History? I mean when I enter Today in any cell, it gets record of the current date, and later if i change tomorrow, it show show the yesterday's date and the tomorrow date both
Hi! Thanks for the video. I want to secure the date/time cel now for changing it. So when they need to fill in cell 1, cell 2 is giving the time from filling it in. Do you know how to do that? When I secure cell 2 is the macro not working anymore..
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
Hello. Is there a way to modify this code, to only enter the date if the Cell next to it has the text Yes in it? For example Column 1 - Resolved Y/N, Column 2 - Date Resolved
Thank you so much for sharing with us... but I have a question... that if i have a data in column B to Z. And I want to show the result in column A, and have more condition.
Example: My data is from B1 to Z100, and if I change one of data in B1, the time in A1 have changed too. But if B1 have new update, and need me to accept, that A1 just update new time kha.
Can you help me this one kha. Thank you so much.
Awesome tool...thank you so much
You're welcome!
Hi. Can you make a formula/ when you change the cell to specific color, cell next to it will show todays date. Thank you
How can I add a condition like if there is data in column A, then enter Now() in column B, else print "No Data Found" in column B
Dear Sir, Thank yooooou very much. Very nice ever have across. It working fine.
Thanks a lot, Glad to hear that
Hi why everytime i entered the any data in a1 it is saying sub or function error
i have a simuler code but doesn't seem to work if i wanted to do it in multiple collums. example: Collum "A": auto-Date/time, Cullum "B": information1, Collum "C": Information 2, Collum "D" blank ,Collum "E" an other Auto-Date/time. I've tried by creating a seperate coding page and i've tried by just creating a 2nd line for the second set of ranges. but neither seems to work. any chance you could help me fix that? Data must be next to each other. Only the last set of ranges work unless i comment either one of the sets out
hello. thank you. your You Tube is awesome. I tested and lost the code for the static date. Would you please resend it.? Thank yolu
Done!
can you apply the formula/code if changes are made to a row? I have a spreadsheet where we track events on each row, but would like a date to autopopulate if someone adds another row or makes changes to any cell within that row
If you use a table all the formula copies over when you add a row.
I have a range of data B4:D35 but want the =Now() to just stay in cell D1. How do I do that?
What if I want the same function on multiple columns like I have a table where I record purchase orders in B1 (this can be down with the VBA code you have shared) but I want to record the "out of stock" date as well which would be in F1. Is there any workaround for that?
So cool, thank you
Thank you too!
Nice tutorial, Hello sir your video are easily understand able and use full. I have one question, can we display the date and time only one cell only when the sheet updated?
Yes, you can, you can modify the range in the VBA code accordingly and apply a formula in a cell to fetch the max of all these dates and you can hide this column B.
This is excellent - Is there a way though that if data is deleted from column a that column b removes the date stamp?
Thank you. Need to check this one,
If my cell value chaging/updating as per source data then will it update timestamp at what time value got modified?? I mean will it work in live Excel data ??
I want to know how to add formula for another cell in same sheet. Two function for two dates. Please solve the problem.
Will it update the date if we amend the existing cell with amended date ?
Great video. How to change the column where the time stamp appears please?
Thank you. you can change the cell reference in the code.
@@dptutorials How ? which line is it ?
@@dptutorials Which line would this be?
Thank You. How would the code look like if I wanted to date stamp two separate columns. For instances if column A is updated, show the date in column B. If column D is updated, show the date in column E?
Will check it please.
thank you so much buddy for that I wish te best
Thanks a lot for the feedback.
I welcome you to subscribe this channel to enjoy more interesting videos.
Many many thanks Bro.
Most welcome
Great video, thanks! Does this work with Form Controls, example as check box?
No, It wont work I guess.
Excellent sir
Many many thanks
How to set particular cell ? Sir
Thanks for the video and code
You're welcome!
Thank you very much
You are welcome
How about if we have more than one cell (columns) in each row?
If time and date show on column A, data input from column B:G, how ?
Hello im wondering can i have the date always appear in a specific column?
I would like to know if i can get 2 columns filled in. say for eg, Date in one column and time in another.
you can use INT or MOD to separate date and time.
Nice. Thanks man
Any time!
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
how to clear the time if we clear content is cell A1
What would be the code if I'm updating data in column D and I want date in column B. I changed "A1:A1000" with "B1:B1000" but this is giving me date in column E when updated data in column D
Just please change the number of column in the VBA code.
Would you be so kind to resend the link. Thank you
wc
i want to add date in column D whenever i make any change in either column A or B or C. Can you please help me with this?
Sure, please write to info@dptutorials.com
CAN THIS BE USED IN A SHARED SHEET ONLINE?
Sir, how to put previous date and hour in one cell? It always give the current date. Pls help
Just add one more column with a formula or modify the vba code.
Hi there, I'm using a table in Excel for dashboarding reasons, and this doesn't seem to work when adding data into a table, only into a cell. Do you know if there's an edit to the VBA code to apply this to table data too? Many thanks!
Let me check
@@dptutorials Any luck?
How can this be done in Online browser Excel?
Can we add Two cell references for this ? Need to have entries in 2 seperate pair of columns
Yes please
what about if date on column A and time on coulum B what is the process?
You can combine both using separate formula.
Excuse me can you show me VBA code running time ?
Thank you so much
Yes I can, its hardly seconds time only.
How to add the date on an unspecfied target cell
Bummer. I copied and pasted exactly as your code shows and nothing happens when I put something in column A. #WhatAmIDoingWrong
Right to left entry what I do
Does VBA work on online share excel file as well?
Yes it does, with some minor modifications
if i put =today() or =now() formula in a cell today, if i open the spreadsheet tomorrow i want yesterday's date to be displayed in that cell
For yesterday's date use the formulas =today()-1
sir what is the code to update time if the text in the cell changes if its not blank i:e N/A changed to yes etc?
Need to write a different code for that.
Thanks bro, but how to apply this code in google spreadsheet?, here there is no view code option
It Google spreadsheet, it would be a query.
Wrote it just as you did, don't work for me....
how to same in google sheets
i need to record data of every 15 or 5 minut interval.how to do that....?
Seriously no idea as of now.
where is the code?
GREAT
Thanks a lot.
Is it possible to do this with multiple cells in one sheet?
No sorry.
Actually, you can. I figured it out. Thank you. 😊
@@angsaysroar how please tell us
@@angsaysroar i would love to know how as well please
@@angsaysroar how?
i'm sorry. i don't see teh code?
Sir... Aftar save file... When re open
Then not working
Same with me. Any help please?
Hi Fazal and Kwame, Please read the video title carefully, this trick is to capture the timestamp value. i.e., the time or date on which the adjacent cell has been modified last.
How doi get this to work in Google sheets?
Sorry, I need to explore this.
this is not working for me
Code is not in description. You have to go to another link and request access. Scam.
Not a scam, Just to maintain the description length, I have provided my access to google drive. will check the access rights, it should be easily downloadable.
This is not working 😢
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub