ฝัง
- เผยแพร่เมื่อ 18 ต.ค. 2022
- Learn how to insert timestamps in excel automatically as soon as you enter or update data in a cell.
Please subscribe to our channel @xlpedia
Watch part 2 in here... • How to Insert Timestam...
Suppose you want to enter data in column B and want the timestamps in column G. Then apply the following formulas and formats accordingly.
• A1: =IF(B1="","",IF(OR(G1="",AND(ISNUMBER(A1),B1=A1)),A1,B1))
• G1: =IF(AND(B1LessThanGreaterThan"",B1LessThanGreaterThanA1),NOW(),IF(B1="","",G1))
• Column G Format: mmmm d, yyyy hh:mm:ss am/pm
#AutomaticTimestamp #Timestamp #Excel #TimestampExcel #ExcelTimetamp #TimestapGoogleSheets #AutoDateInExcel #ExcelTutorial
Watch this if you are having trouble with the formula...th-cam.com/video/XOVocNqCyUI/w-d-xo.html
Hi, I am using this timestamp formula for my shared file with other colleagues. I have enabled iterative calculation and changed max iterations to 1, everything looks fine to me. However, I noticed that there are challenges like:
1. My colleagues are still seeing dates with 00-Jan-1900 when they open the file
2. When I opened up after they edited the file, I am also viewing the date 00-Jan-1900 instead of the normal date.
May I know is there any way to fix it?
@yeowcharmaine5981 Hi, thanks for reaching out. I think if you first input the data in B1 and then insert the formula in G1, then this issue may occur. So, first insert the timestamp formulas and then input your data.
Will the current date change the next day if the cells are updated yesterday? and we check it tomorrow again
No. The dates will only change when you update the cells.
So once u enter a data on the cell, the date n time is captured. It will only capture a new date n time if the same cell data is edited
I want the timestamp (G1) to be updated whenever I update the value of A1, is that possible? Currently it only works if I first remove the value from A1, and re-enter a value. But it doesnt work if I direclty change the value of A1
You may need to use VBA to do that.
Did you ever find a solution that does not require VBA?
@Fbxbill Sorry, I did not. However, if you are fine with using a very small vba code then you can watch this video >> th-cam.com/video/XOVocNqCyUI/w-d-xo.html
Trying to figure out what A1 is in the formula?
Well, A1 stores the data of B1. So whenever you update data in B1, the formula in G1 detects that and inserts the timestamp.
Why my iterative calculation keep unticked whenever I close my excel? How to kwep it permanent?
You need to click OK after ticking the checkbox. Did you do that? Also, don't forget to save the workbook after that. Then antime you reopen that particular workbook, iterative calculation will remain checked. But for other workbooks, it won't be by default. Perhaps it's better that way to protect your formulas from being affected by iterative calculation when not necessary.
How to Use This Formula in an Excel Online File
@DanielCuartas-ec7gv Mr. Cuartas, creating a desktop version and then opening in online may work for you. You can check out the following community thread in this regard. Please check out the follow up of this video linked in the description where the formula is a bit simplified. Thank you.
techcommunity.microsoft.com/t5/excel/creating-a-static-time-stamp-in-excel-online-version/m-p/1787516
tHANKS FOR SHARING THE VIDEO. My date and time is shown same. I do not know why.
You are welcome. The dates will be same if you are updating the cells in the same day. The times will be same if you are updating the cells within the same minute and the timestamp column is formatted as dd/mm/yyyy hh:mm. You can format the column as dd/mm/yyyy hh:mm:ss to notice the difference in seconds or update a new cell a minute later.
Furthermore, the formula only works for updating empty cells only. For example, if you change Mango to Orange in a cell, the timestamp won't change. So, first clear the cell and then enter data.
If this is not the case then please explain a bit more about the issue. Thanks.
@@xlpedia many thanks. Now is done it.
@@xlpedia 1 more think I wanna ask if you dont mind. I want use C1, D1,E1 cells as well. not only A1 and B1. Could you please let me know?
Assuming cells A1 to E1 are empty. Now you need to insert timestamp in cell G1 as soon as you enter data into any of those cells. Then the following formula should work just fine.
=IF(OR(A1"",B1"",C1"",D1"",E1""),IF(G1"",G1,NOW()),"")
**Limitations are the same as stated earlier.
@@xlpedia many thanks
it doesnt work for me, it gives 'January 0, 1900...'
Please create a new workbook and follow the steps carefully. It should work just fine.
JESUS CHIRST USE THE MICROPHONE
Sorry for the inconvenience😥 In the latest videos, I do use microphone though. For sound, you can watch the follow up video linked in the description. Thanks 👍