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.
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
@Fbxbill Sorry, I did not. However, if you are fine with using a very small vba code then you can watch this video >> ruclips.net/video/XOVocNqCyUI/видео.html
I’m afraid this doesn’t work with files stored on one drive and accessed by multiple iOS devices running excel for iOS.. it’s annoying and I’m yet to find a solution. It works fine on a stand alone spreadsheet stored locally and accessed by pc. I believe this is to do with how excel on the iPad and iPhone update the spreadsheet with the latest data when loading from the cloud. Sometimes a blank cell will be populated with data from the cloud upon loading and at that point the date stamp is written again regardless if there was one in there before because from that sheets formulas perspective it’s just been added into the cell for the first time (albeit by excel loading latest version of the file) even though the original input was a few days prior Ona different device.
@MatLampitt Thank you for letting me know about the issue. Unfortunately I can't help you with this issue right now. If you can't find a suitable solution for this problem elsewhere then I suggest you get help from ChatGPT, Gemini or other AI models. You can also check out the VBA method explained on another video linked in the description.
Hello, I need this for an attendance sheet, I need employees to scan a QR code so that I can get the names and timestamps automatically on my sheet, can you help me?
@mh4612 Right now I can do very little to help you. This seems a bit complicated. But you can ask the AI models like ChatGPT or Gemini to help you with the process.
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.
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.
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.
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 👍
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
Watch this if you are having trouble with the formula...ruclips.net/video/XOVocNqCyUI/видео.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.
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 >> ruclips.net/video/XOVocNqCyUI/видео.html
I’m afraid this doesn’t work with files stored on one drive and accessed by multiple iOS devices running excel for iOS.. it’s annoying and I’m yet to find a solution.
It works fine on a stand alone spreadsheet stored locally and accessed by pc.
I believe this is to do with how excel on the iPad and iPhone update the spreadsheet with the latest data when loading from the cloud. Sometimes a blank cell will be populated with data from the cloud upon loading and at that point the date stamp is written again regardless if there was one in there before because from that sheets formulas perspective it’s just been added into the cell for the first time (albeit by excel loading latest version of the file) even though the original input was a few days prior Ona different device.
@MatLampitt Thank you for letting me know about the issue. Unfortunately I can't help you with this issue right now. If you can't find a suitable solution for this problem elsewhere then I suggest you get help from ChatGPT, Gemini or other AI models. You can also check out the VBA method explained on another video linked in the description.
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
Hello, I need this for an attendance sheet, I need employees to scan a QR code so that I can get the names and timestamps automatically on my sheet, can you help me?
@mh4612 Right now I can do very little to help you. This seems a bit complicated. But you can ask the AI models like ChatGPT or Gemini to help you with the process.
Just use google form
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.
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.
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 👍