What is a time stamp?
Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it.
So how do I generate timestamps in Excel?
We all know about the now() formula in excel and how it tells you the current date and time. The only problem with it is that, when you press F9 or recalculate formulas, the now() will be changed to reflect latest date and time. Thus we cannot use NOW() as a time stamp.
A good alternative (although manual) is to use keyboard shortcuts CTRL + ; or CTRL + : to insert current date and time in the active cell. Since this places the value, we don’t need to worry even if the date or time changes. But the only issue is, every time you need a time stamp you have to press the 2 keys.
A better way to get timestamps in excel is to use a special type of formulas, called as circular formulas.
What the heck is a circular formula?
A circular formula refers to itself (or to another cell which in turn refers to this cell). Confused ? Well, let me give you an example.
=if(A2<1000, A2+1,A2)
Now the above formula is normal, but the moment you write it in the cell A2, you have made it a circular formula. So, each time you run the formula (by pressing F9) the value in A2 will go up by 1, just like an incrementing counter.
But here is a thing you should be aware of: There is a setting called “iterations”, it tells excel how many times it should run a formula before finalizing the result.
This setting wouldn’t make any sense in case of regular formulas. But it is very important for circular formulas. If not for this, excel would go in to an infinite loop and stall.
Now, by default iterations in formula computation is turned off. That means when you write a circular formula or create a circular reference, excel shows you a message warning you and it doesn’t evaluate the formula.To turn the iterative calculation, and thus enable usage of circular formulas, go to menu > tools > options > calculation tab and check the iteration box. In excel 2007, you can go to office button > excel options > formulas > iteration area.
As you can see the default max. iterations per formula execution is 100, which means, every time you run the circular formula, excel calculates it 100 times before presenting the value. So our formula =if(A2<1000, A2+1,A2) written in cell A2 would go up by 100 every time you run.
Back to Generating Time Stamps
Now that you are knowledgeable about circular formulas, here is how we can refine the “time stamp generation” problem:
if the cell doesn’t already have time stamp, insert current date time using now(), otherwise leave the value as it is
and we will trigger the formula once we make certain changes, for eg. whenever you enter data, you can tell excel enter timestamps. See this:
The formula itself is very simple:
=IF(C3<>"",IF(B3="",NOW(),B3),"") in cell B3.
What it does?
It checks cell C3 and whenever it is not empty it runs the circular formula IF(B3="",NOW(),B3) which fetches NOW() value only if the cell B3 doesn’t already have a value, thus serving timestamps.
The above formula works only if you have enabled iterative calculation mode as described above.
Your thoughts ?
PS: Having questions on formulas, try our excel formula helper tool and learn 50+ forumals in a fun way
175 Responses to “Automatically insert timestamps in excel sheet using formulas”
Just last night, I was browsing the web looking for how to create a command button that when pressed will update the timestamp on a cell. I have a checklist of names and each name that I find I will mark them off. Cell A1 has the timestamp. Is this posible?
Another great suggestion, but I also would like to change the timestamp in case someones modifies the cell. Is that possible without VBA?
@JacMars: Welcome to PHD and thanks for the comments 🙂
You can use a variation above formula to do just that. You can maintain a counter variable in a cell that goes up or changes whenever you check a cell (try using a one of the count() formulas). Once this cell reflects the changes you can link the timestamp formula to it and generate timestamp automatically.
I am not able to give you the exact formula since I couldnt really understand what you meant in the question. Can you elaborate ?
@Oliver: I am not aware of any formulas that can do this. I would love learn something on these lines though. I can figure out a circular formula that can store the first value you have ever entered in a cell but that is not useful in this case.
Let me know if you come across a good solution for this. Our donut policy is alive and kicking 🙂
@Chandoo
I activated the iteration setting and enable automatic calculation setting. However, i am getting this results: 1/0/1900 12:00:00 AM which is equivalent to zero in number.
Am I missing something? Kindly assist on this matter.
Appreciate your help.
I am also getting 1/0/1900 as a result. What are we missing?
change formatting of the cell (call it A10) from date. Shortcut only produce time value, not date & time.
Or produce another cell (call it A11) with =Today()
get the result in A12 by =A10+A11
Set the iteration to 1 - that should do it
@Berna and @Marion, please try using IFERROR function before you use the formula, example =IFERROR(1/(1/IF(C3"",IF(B3="",NOW(),B3),"")),"")
I was able to do all of this successfully however when I send the file to my coworker it doesn't work at all for her. I confirmed that the iteration settings hadn't changed and that she was entering things correctly. Could there be any global computer settings interfering with it?
it is a bit late now, but the below formula will do exactly what JacMars was looking for, just enter this in visual basic, anytime you have an entry in column B, Column A will populate a permanent time-stamp.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Set rInt = Intersect(Target, Range("B:B"))
If Not rInt Is Nothing Then
For Each rCell In rInt
Set tCell = rCell.Offset(0, -1)
If IsEmpty(tCell) Then
tCell = Now - Time
tCell.NumberFormat = "dd/mm/yyyy"
End If
Next
End If
End Sub
Hello Paddy
When I trying to run your script, error: Argument not optional. Can you help me?
How to use this code two times in two different column.?
I am receiving "Ambiguous name detected: Worksheet_Change" error.
Hi,
Could you please solve the below given issue.
Suppose i have put formula in C coloumn, And when ever i put some thing in B coloumn C coloumn will update, But if i put B1 Cell C1 cell should be updated and and should be freeze, later if i put some value in B2 The C1 cell should not be changed.
Help....
@Oliver, @Chandoo
Here is how to change the timestamp when the data cell is modified. There is probably a cleaner way to do this but I just came up with it now and haven't gone over it too thoroughly. Note that:
1) this uses two "helper" columns in addition to the Data and Timestamp columns - you can feel free to hide these columns in your final workbook once you have it working, but don't delete them
2) something is screwy with how Excel is propagating the calculations (at least on my machine), so it's important that the columns are in the order I describe them here (if anyone can figure out why this should matter, please explain - I would assume that since I have iterations set to 100 and automatic calculation then the order wouldn't matter, but I guess it does)
Anyway, the idea is to have one cell (DataHelper) which will keep track of the previous data and will therefore be able to tell you when the data has changed. When that happens, another cell (TimeHelper) will update itself with the current time and will store that, and finally the Timestamp cell will pull the time from that TimeHelper cell.
1). Data: (still in cell C3), this is where you enter your data
2). DataHelper: (cell D3), formula: =IF(C3"",IF(D3="",0&" - "&C3,IF(RIGHT(D3,LEN(D3)-4)=""&C3,D3,MOD(LEFT(D3,1)+1,10)&" - "&C3)),"")
The output of DataHelper will be in the form "# - data", where # is an integer between 0 and 9, and data is the data in C3. Walking through the logic, if Data is not empty, then there are three possible cases. (a) this is the first time data is being entered (which we know because DataHelper is empty), (b) no one modified the data, we're in a static state (which we know if Data matched the end of DataHelper), or (c) the data was just modified (all other cases). If (a), then just output "0 - data". If (b), keep DataHelper the same. If (c), then increment #, and update with the current data. [Note: In the comparison IF(RIGHT(D3,LEN(D3)-4)=""&C3 - I wrote ""&C3 to force C3 to be evaluated as text.]
3). TimeHelper: (cell E3), formula: =IF(C3"",IF(E3="",0&" - "&NOW(),IF(LEFT(E3,1)=LEFT(D3,1),E3,LEFT(D3,1)&" - "&NOW())),"")
The output of TimeHelper will be in the form "# - time", where # is an integer between 0 and 9, and time is the time of last modification (don't be alarmed - the time will appear as a number - we'll fix this in the Timestamp cell). The logic is almost identical with DataHelper. The main difference is that here we are able to tell the difference between scenarios (b) and (c) based on whether the # prefix here matches the # prefix of DataHelper.
4). Timestamp - (cell F3), formula: =IF(E3"",VALUE(RIGHT(E3,LEN(E3)-4)),"")
This will extract the time from TimeHelper. Make sure to format this as Time in the standard Excel number formatting dialog.
OK, that should do it. Let me know if that works for you. Any ideas on how to clean this up?
absolute beauty that one. thanks
Some of my formulas above are a bit mangled. Wherever you see quotes in the formulas, it's either an empty string, or a string with 3 characters - space, minus, space.
Hi Zach,
this formula is not working "=IF(C3"",IF(D3="",0&" - "&C3,IF(RIGHT(D3,LEN(D3)-4)=""&C3,D3,MOD(LEFT(D3,1)+1,10)&" - "&C3)),"")"
I'm guessing that's because it's not fulfilling "logical function" condition. Can you check and let me know
Hi Chandoo,
I want to a cell to display running time ie. the seconds should be changing. What is the easiest way to do this?
THANKS SO MUCH!! Yes, I'm yelling because I'm so happy to have found this timestamp formula and your explanation of how it works so that I can modify it to fit my needs. I've been struggling with a way to do this that wasn't a complex solution. This is it.
BTW - I love the Calendar, too
Thanks, again. - Jim
@Zach: that is awesome.. Thank you so much for the formula.. It worked just fine 🙂
I have actually started out to solve the updation problem like this but gave up after trying for a while.
For those of you, the formula for dataHelper column is
and for timeHelper column is
Here is an e-donut for you Zach... 🙂
Hi Chandoo,
Please help me out with a formula as below mentioned requirement.
1) If I type anything in C coloumn, for example @ C2.
2) Then that date should be captured in A coloumn, for example @ A2.
3) Then that time should be captured in B coloumn, for example @ B2.
@Kham
You want to use Date/Time stamps
Have a read of:
http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/
http://forum.chandoo.org/threads/time-stamp.23169/
@All, the formulas are getting cropped, another version here... Just in case
For those of you, the formula for dataHelper column is
=IF(C3<>"",IF(E3="",0&" - "&C3,IF(RIGHT(E3,LEN(E3)-4)=""&C3,E3,MOD(LEFT(E3,1)+1,10)&" - "&C3)),"")and for timeHelper column is
=IF(C3<>"",IF(F3="",0&" - "&NOW(),IF(LEFT(F3,1)=LEFT(E3,1),F3,LEFT(E3,1)&" - "&NOW())),"")I can't seem to get the formula for cell F3 right. Can you help please?
I am trying to update multiple rows with a timestamp for each one. I have the formula working for one cell. I then copied it to multiple rows and every time one updates, they all update. Any help would be appreciated. Thank you in advance.
I have the same problem. No suggestions have worked so far.
Have you been able to find a solution where you can have multiple rows with an independent static Excel date/time stamp?
in addtion to the two stated helper columns add one for which you concatenate all the other columns in the row, i.e. =A3&A4&a5 etc, this string becomes your data cell to monitor for changes
@Sumeet: A cell that displays time.. Well, you may want to resolve to a VBA based solution for this. If you are looking a clock that updates automatically, do visit our earlier post on doing clocks in excel: http://chandoo.org/wp/2008/08/19/show-time-excel-fun/
@Jim: You are welcome 🙂
Is it possible to create a formula that only runs once? In other words...if i have a formula that gets triggered by a number in cell A3 can I make it so that it only runs once even if the number in cell A3 changes later. I would like to keep a running total in A3 and have several different calcuations off of that same cell so that I can refer to each quater and see what the value was in each.
@Mike: Welcome to PHD and thanks for your comments.
Yes, you can do the same using circular formulas as shown above.
assuming your formula is cell A4 and it depends on A3, you can write something like this in A4
=if(a4="",sum(a3),a4)this formula runs once and then doesnt change no matter what. But you have to enable iterations from calculation options as described above.
I had to modify this static sum formula for it to work. My a4 and a3 are in mm/dd/yyyy hh:mm format.
The static field (a4) initially returns the sum value of the dynamic field (a3). a3 is set to "" until the trigger field (a5) was "".
SUM("")=0 (formatted as 1/0/0 19:00 12:00AM) and this value returns true if 0"". I was able to mend this issue by changing the formula to
=if(a4=0,sum(a3),a4)
since the default value of a4 was 0, this formula will continue to add 0 to 0 until the value added is not zero, which will occur when a5 "" become false.
I do have my iteration set to 1, but I'm not sure if that matters.
I'm not sure how to fix this, but the angle brackets disappeared from my comment, guessing it recognize html code and removed them from my text. ~
That is a very nice tip... In addtion to this, is there a way to populate the user name/ user ID, of who made the entry? That would complete the time-stamp information.
Thanks,
Juwin.
@Juwin: Welcome to PHD.
You can add the user information easily to this. Just create another column with username as the title and use it to enter the userids. If you want to automate this step based on the windows userid, you may have to use VBA.
I wanted to automate the step based on Windows userID. But when I use VBA, everytime I open excel, I get a popup regarding macro security. Evenif I try to lower my security, when others open the excel sheet on their machine, they get the popup, and they have the option to disable macro. Once they disable the macro, then the automatic population logic doesn't work anymore. That is the reason why I wanted to use a formula, instead of VBA.
Thanks,
Juwin.
@Juwin: hmm VBA in excel means, presenting your audience with the dialog asking them whether they are ok to relinquish the control. One way to get around this is to write a UDF that can get the userid, then publish it as an add-in and ask your colleagues to install it. One time process, but reduces pain there after.
Even simpler one? here is a way. Ask your readers to store the file on Desktop. Then use CELL() function - cell ("filename") to extract the file location. This shows, a typical windows file path like this: D:\DOCUME~1\{username}\Desktop\[filename.xls]sheetname. Now extract the username from the path by splitting text using some simple text formulas. I know this kinda forces your user to save the file etc, but it can work well.
Any other suggestions you can think of ?
[...] Learn to work with Circular References - Tutorial on Generating Timestamps [...]
Hi guys,
I have used the formulas supplied by Chandoo on the 15th Jan - Is there any way to make the data counter incremental, i am only getting 1's - ie:
modification 2 would equal: 2 - New Data
modification 3 would equal: 3 - New Data
Thanks
@Martin: Have you enabled iterations from calculations options of excel.. I know it is stupid question...
Also, when you start out, first you need to clean up the data cells, then start entering values one after another, that way counter increments properly...
[...] Another column where we generate a time stamp when the user enters the name. Please read this article to generate time stamps in excel [...]
I am having trouble with formulas in timeHelper column as displayed by chandoo. I have entered the formulas exactly as written but the problem seems to be that I cannot get the second IF statement to recognize the comma after NOW() so it NEVER gets to the False condition which is the rest of the formula, so consequently it gives an error and never gets past the true condition. Has anyone else had this problem?
Got it! Never mind...
If you can use VBA (and don´t want to activate manual calculation, which has brought serious trouble in my experience when checking big tables due to forgetting to hit F9), this simple procedure will do the trick:
--------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C9:C1000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else
With .Offset(0, -1)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
--------------------------
@Mark.. cool
@Kaliman... That is a cool script, thanks for sharing it with us. Btw, you dont need to turn on the "manual mode". You just need "iterative mode" to enable circular references.
I have a question about the timestamp as well. I am wondering if I were at the time to key something simple like "C", "X" or "P" in a cell, I would like the cell next to it have the date I actually typed that into the cell and stay at that timestamp date.
I appreciate all the info on this matter already, im just still having difficulties, very long day.
@Jaye... I am sorry, but isnt that exactly what this article is all about?
Ok, I got the time stamp to work. Is there any way of it being able to read what date the cell was originally keyed into?
Thank you for the time stamp formula. I have been looking for something like this for a long time. I have a spreadsheet that is used in a power plant and need the time the readings were entered.
@Jaye.. missed your comment in holiday mood.. Are you still looking for help?
@Mike V: You are welcome 🙂 If you dont mind, share with us how this formula is helping you in your work. Others might benefit it from it.
I'm not sure what I'm doing wrong but the Time Stamp formula provided above isn't working for me: =IF(E3?”,VALUE(RIGHT(E3,LEN(E3)-4)),”")
What am I missing?
@Adrian: Have you enabled "iterative calculation mode" from formula settings?
What a great tutorial! I'm a beginner but this was easy to understand and the instructions for excel 2007 were just what I needed. I have one question - is it possible for the timestamp to display in the same cell as the data? For example, I type the sentence "hello, my name is John" inside of cell A1, and the timestamp shows up right after "John"?
@Mel.. Thanks, I am happy you like this.
And no, we cannot write formulas in such a way. May be you can use VBA.
@Mel:
you can combine cells like:
hello John | 28/01/2010 12:05 | hello John 28/01/2010
cell values:
hello John | now() | =CONCATENATE(A1," ",TEXT(B1,"dd/mm/yyyy"))
and it's variations... you can cut name off using wiledcards (more advanced)
Social comments and analytics for this post...
This post was mentioned on Twitter by bsamson: Coolest formula trick in #Excel: Update a timestamp when the data in another cell is changed. http://trunc.it/wx06 (in the comments)...
I need to do the following in excel. Once the time stamp in a cell A1 = 9:30 am it will record true or false based on certain conditions. Now once A1 cell records true or false at 9:30 I need another cell say B1 to record the A1 result, true or false and remain constant until 9:30 the next day when A1 is recalculated and the results sent to B1 again.
Hi,
Is there a way to get the timestamp function working without turning on the iteration function? I'am working on a document that would have many different users and I assume it would cause problems as it is not by standard turned on. I would not prefer to use a VBA solution for the same reasons
@Tomas: I am not aware of any way to do this without iterative calculations or vba.
Chandoo: Is there a way to ensure that all users of the workbook have the iteration box checked ? As far as I understand this is a local setting on the computer using the workbook..
I am a little lost, I want to add a automatic time and date stamp into b colum so each time the user clicks in c colum it adds a date next to each entry is this possible?
thanks
keith
The time stamp formula using the helper columns seems overly cumbersome. This formula will do the same thing.
=IF(ISBLANK(A1),"",NOW())
Both suffer from the same flaw though, the time stamp changes on a recalculate, save, open or a change anywhere on the sheet. it should only change when theres a value change in the referenced cell (A1).
@Thomas: I am not sure if you understood the formula. I solved the "Both suffer from the same flaw though, the time stamp changes on a recalculate, save, open or a change anywhere on the sheet." problem by using circular references and iterative calculation mode. That is the only non-macro way to solve this problem.
Am I missing something?
First let me see if I have the formulas right, are they this:
Text in cell C3
dataHelper column cell D3
=IF(C3"",IF(E3="",0&" - "&C3,IF(RIGHT(E3,LEN(E3)-4)=""&C3,E3,MOD(LEFT(E3,1)+1,10)&" - "&C3)),"")
timeHelper column cell E3
=IF(C3"",IF(F3="",0&" - "&NOW(),IF(LEFT(F3,1)=LEFT(E3,1),F3,LEFT(E3,1)&" - "&NOW())),"")
Timestamp cell F3
=IF(E3="",VALUE(RIGHT(E3,LEN(E3)-4)),"")
In regards to the timestamp formula I added the = between E3 and the "". It wasn't shown above in the formula.
Next I have Iteration checked. Max. It 100 MaxChange .001
Starting from scratch using those formulas. I have reentered the formulas, now it shows a value error for the timestamp column.
So looking at the formula for the timestamp, that doesn't make sense.
when I read that its saying If Cell E3 is blank (the""), then do VALUE(RIGHT(E3,LEN(E3)-4)) , otherwise if Cell E3 has a value, then do nothing. That returns a value error.
It should read as:
=IF(E3="","",VALUE(RIGHT(E3,LEN(E3)-4)))
which is saying
IF cell E3 is blank,then do nothing,otherwise if cell E3 has a value then do VALUE(RIGHT(E3,LEN(E3)-4))
Now I changed the formula to =IF(E3="","",VALUE(RIGHT(E3,LEN(E3)-4)))
Now the formula shows the timestamp ok, but when you enter anything in any other cell the timestamp changes updates to current time, if you save the sheet or open the saved file it changes to the current time.
Now you might not notice this right off depending on how you have the time format set. But if set to include seconds (like 1:35:26) you'll see it right off.
Now this formula:
=IF(C3"",IF(B3="",NOW(),B3),"") in cell B3.
Is good in that it holds the timestamp all through those conditions. But if you want to update cell B3 and have the stamp update also, you have to clear the contents of the cell first then enter your new data.
Now this isn't a problem for me, but I know of some else who this would be a problem for. Because he has a lot of data in the cell and doesn't want to retype all of it just change it.
@Thomas.. glad to see you found the solution already. I was about to say the same thing.
Now, updating timestamps is an issue as there is no straight forward way. But Zach presents an interesting way to do it at http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/#comment-33506
try that and see follow up comments too
@Chandoo..You must have missed something somewhere.
The Zach post formulas here:
http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/#comment-33506
are what I was talking about and what I've been replying to, read my reply here.
http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/#comment-117514
I point out in that post that (Zachs formulas) don't work, in that his timestamp changes when you change anything on the sheet not just cell C3, also on save or open it changes the time stamp.
That's why I said you could do the same thing that Zach's 3 column formulas timestamp doe's with this simple formula. In cell D3 put =IF(ISBLANK(C3),”",NOW())
It doe's the same thing, but also has the same problems that zach's doe's, you just don't have to write 3 formula's to to do it.
Ok, I am sorry for the messup Thomas.
But I tested Zach's formulas just now and they work alright for me.
They do not recalculate on save or updates on other cells. I have demonstrated the technique in a file and uploaded it here: http://img.chandoo.org/playground/timestamp%20experiments.xls
Go ahead and download it and see if you can use it.
Ok I tried the file on two different computer's. It works on one but not the other! There must be a setting or a conflict or corruption on the one that's throwing it off. But yes it doe's seem to work. Thanks alot.
What if I set this up on a sheet and then a friend opens the file? If they haven't set their excel to "Enable iterative calculations" and then set the correct maximum, what will happen?
@Matt.. This technique doesnt work correctly without iterative mode.
[...] But, keep in mind that, in few cases, circular references may be required. For eg. if you want to add timestamps to your workbook. [...]
Chandoo, this thread is very helpful. I am using the first code =IF(C3"",IF(B3="",NOW(),B3),"") exactly as you have it, recreating your example. I named column A "other info," as your screen shot didn't have a column A from what I understand. Column B is "timestamp," and column C is "data." I put the formula in B3, and the wrote something in C3, and the date/time appeared in B3. However, when I modify C3 nothing updates in B3, the date/time stays the same. F9 doesn't do anything either, even when I highlight the specific cell itself. I have enabled iterative calculations. It seems that I have a settings issue. Can you help me?
Mitch
This formula works great:
=IF(C3"",IF(B3="",NOW(),B3),"") in cell B3.
But how do I get it to timestamp B3 if data is entered in EITHER C3,D3, OR E3?
If the cell range C3:E3 is used to trigger the circular reference whenever all the value(s) in this range are Blank then, in cell B3 you could use...
=IF(COUNTBLANK(C3:E3)=3,IF(B3="",NOW(),B3),”")
You might want to lookup the "COUNTBLANK" funtion to see under what conditions it "counts" a cell as blank. You may have to use other variations of the above formula depending on your worksheet, formulas, data, data formats, and requirements etc. For EXAMPLE you could also test cells w/ different triggering criteria like this...
=IF(AND(COUNTBLANK(C3:D3)=2,ISERROR(E3)=FALSE),IF(B3="",NOW(),B3),”")
If the cell range C3:E3 is used to trigger the circular reference whenever all the value(s) in this range are Blank then, in cell B3 you could use...
=IF(COUNTBLANK(C3:E3)=3,IF(B3="",NOW(),B3),”")
You might want to lookup the "COUNTBLANK" funtion to see under what conditions it "counts" a cell as blank. You may have to use other variations of the above formula depending on your worksheet, formulas, data, data formats, and requirements etc. For EXAMPLE you could also test cells w/ different triggering criteria like this...
=IF(AND(COUNTBLANK(C3:D3)=2,ISERROR(E3)=FALSE),IF(B3="",NOW(),B3),”")
Note: Re-submitted this comment since part of previous comment appeared to have been truncated.
I haven't had luck getting the effect I want using this formula but I was eventually successful by using this macro:
Private Sub Worksheet_Change(ByVal Target As Range)
'If you enter anything, in column B-E,
'cells in columns A, same row, get the timestamp of entry
Application.EnableEvents = False
Set isect = Application.Intersect(Range("B:E"), Range(Target.Address))
If Not isect Is Nothing Then
Me.Unprotect Password:="sjhn"
Cells(Target.Row(), 1).Value = Time
Columns(1).NumberFormat = "hh:mm:ss AM/PM"
Me.Protect Password:="sjhn"
End If
If Application.CountA(Cells(Target.Row, 2).Resize(1, 4)) = 0 Then Cells(Target.Row, 1).Resize(1, 2).ClearContents
Application.EnableEvents = True
End Sub
Thank you.
Hi Caleb King,
I tried to use this on my excel but i can't make it work. I am having an error in line for Set isect and columns (1). Can you help me? Thanks!
Look at the quality of discussion your site gets Chandoo, you are to be congratulated as your site has the right mix of practical, technical, creative stuff and fun. Well done man!
Heya,
This is an absolutely fantastic tip. Thank you for sharing it. Works very well for me.
Thank you!! Very helpful 😀
Thanks!! That is great!!
Although i saw this formula somewhere else, your explanation make me fully understand how it's work
I have several cells and several sheets interlinked by making them equal each other. My first worksheet is the main work sheet where all the info from other sheets add up and place in one value.
I would like to have several cells in the first worksheet display the date and time when any info was changed in any work sheet...Am I making sense.
Please can you provide me with a step by step way of how this is done..I am new to these types of things.
Thanks,
J
How do I add a date and time stamp which indicates any/which cells and when they were last updated
Great tip - very neat idea - thanks!!
Hi,
When I use both Zach and your formula I get a number 40660.47026 in B3. I have the Timestamp column, a data column and I also checked the iteration. Is the cell not formated properly ? Can you please help ?!
Thanks !
@Luigi
This is the Date/Time number for 27/04/2011 11:17 AM
.
To display this Seelcty the cell, Right Click, Format Cells
on the Number Tab, goto Custom and enter
d/mm/yyyy h:mm AM/PM
in the Type box
@ Hui
Thanks.. I hate how that was a simple fix haha but I have another question.
I have multiple sheets in my workbook which are linked to one main dump sheet. I want to timestamp each sheet when the main dump sheet is update. I tried using the basic formula to create the time stamp on my first sheet. It appears but as this "01/00/1900 00:00. This is the formula that I am using "=IF('IMS Dump'!D5"",IF(W5="",NOW(),W5),"")" The IMS DUMP is where all the data is changed. Any suggestions ?
Thanks !
@Luigi
Can you retype the formula only
@Hui
=IF('IMS Dump'!D5"",IF(W5="",NOW(),W5),"")
For the dump sheet, I have already data in it. I just copy and paste a new data over the old data. Maybe that could help.
Thank you Hui !
Your formula is getting scrambled and so is hard to make out what your trying to say
.
If copying over old data make sure you clear the whole sheet first
@Hui
hmm the clearing of the whole sheet worked haha I'm such a noob! Live and learn I guess. Thank you for your help !
how to generate timestamp with two or more conditions to be met, i tried but it is giving me #value error.
How to generate time stamp with multiple if conditions
[...] zonder VBA maar met de optie iteraties ingeschakeld zie How to insert timestamps in excel sheet using formulas | Chandoo.org - Learn Microsoft Excel Online [...]
I have a spreadsheet that used drop downs in cell L1 with the options of: cleared, pending, and open. The cell has dates in cell E1....what I want to do is once the option of "cleared" is made in L1 I want cell E1 to insert the date for that day and stop. Is that possible and does this make sense?
Thanks
Greg
This was actually incredibly useful. I was a little worried at first with vocabulary like "...much more easier..." but I hung in there and read what you had to say. I've been looking for a tool like this and I knew it couldn't be too complicated and your formula and tips did just the trick. Thanks!!!
Thank you very much for this formula!
Thanks a lot for the great tutorial. I still have a small problem. for the timestamp to work, every time excel is closed and opened again I have to re-allow circular reference. Is there a way to permanently allow it?
Hi Chandoo,
am working on Time stamp formula with now() or with circular formula, the date and time would be displayed but the time would not be freezed, am looking at a formula where in the time has to be freezed whenever the sheet is updated on a particlular cel the time stamp has to show the date and time and which has to be freezed.
eagerly waiting for your quick response !!
[...] a look at this link __________________ A meaningful thread title will get you more answers -Happy with the answer ? [...]
[...] que o tratamento de referências circulares foi ativado, podemos retornar ao nosso problema com uma fórmula de timestamp que encontrei. A exibição de uma marca temporal pode ser reduzida à seguinte questão:Se a [...]
Hi all,
I have data in pivot till march now in a pivot, next month i will have data for april and so on every month.I want a calculated column which gave dynamic calculated value every time new data is refreshed in pivot.
I have 3 columes A B and C
I want C/B And also we had average of A B and C in pivot..
I used a formula =IF(A5""(IF(A5"Ave",C5/B5,Average(D5:D6))),"") and just drag it..
But it fails when it checks the "Ave" in the last row of pivot..
Any suggestions..??
This is awesome!!! Thank you guys! It works like a charm but....
Here is my new dilemma!
potential values in my data cell C3 can be 1,2 or 3. If I change value 1 to value 2, my cell F3 populates/updates the time stamp. However, since we are using 1, 2, 3 as status updates, we have scenarios where status remained the same .i.e the value in C3 remains as 1 ( unchanged), I still want to populate the column F3 with the latest time stamp to reflect that somebody updated the status this week and the status update is not from last week.
Can anyone help me with that please?
GOD BLESS YOU ONE AND ALL!!!!!!!!!!! HALLELUIAH!!!!!!!!!
Hi all, I badly need your advice pertaining to the time stamp formula while detecting duplicate entries.
My point here is to extract a duplicate by comparing two columns; ie by comparing column C (new entry) with column B (present entry).
I used the Conditional Formatting with the formula
=COUNTIF($C$1:$C$100,$B1)>0 and highlight it accordingly.
The problem I encountered was that it highlighted the cell below the actual duplicated entry in column B. I believe this has got something to do with the circular formulation that was entered in column B.
Is it possible to select the actual duplicated entry instead of the one below it?
Any advice is greatly appreciated!
How do you leave a time stamp in a cell everytime the worksheet is saved?
Great formula! Just one question (I'm not sure whether people have addressed this but anyway...) Does it work on Excel compatible with Android tablets in particular, i.e. "Polaris Excel" and/or "Documents to Go" Excel ? Thanks!
The BEST and fastest is to use the IF and AND statements together.
=IF(AND(O1<>"",Q2<>""),Q2,IF(O1<>"",TODAY(),""))
Break Down:
If O1 and Q2 (my timestamp cell) are not equal to a empty space
,Then display Q2 (In other words if both conditions are met then a circular comes back to the time stamp cell; in this case Q2)
,ELSE if O1<>"", TODAY(), So here is my actual calculation to enter something into the Q2 cell, to make the first statement true.
Since we have the first IF THEN statement is true then you never have to worry about the TODAY() date changing going forward.
Excellent and easy to follow. Explanations are crystal clear. THANK YOU!!!
I am wondering if there is a way to count all the cells that are durring a current hour. Like between 5 and 6 PM. I have tried the COUNTIF() function, but it only seems to work if i ask it to count a time greater than a prvious time (i.e. x>TIME(5:00PM) if it is after 5PM), but if I try a time less than a time in the future (i.e. X<TIME(6:00PM) if it is after 5PM but before 6PM) I get 0. If I do only the greater than (after 5PM) it just counts all the cells. My guess is this has to do with the circular nature of the equation, but I am not sure.
So anyway. is there a way to count the number of cells in a given hour. I use this for records of number of students who come into a library and it is nice to easily see if there is a drop off of students after a certain time.
@Phil
How about something like:
=Sumproduct((Hour(A1:A100)>=14)*(Hour(A1:A100)<=15))
Where 14 = 2:00pm & 15 = 3:00pm
Adjust the range to suit
You can link it to cells by
B1: Start Time
B2: End Time
=Sumproduct((Hour(A1:A100)>=24*B1)*(Hour(A1:A100)<=24*B2))
@ChandooI activated the iteration setting and enable automatic calculation setting. However, i am getting this results: 1/0/1900 12:00:00 AM which is equivalent to zero in number.Am I missing something? Kindly assist on this matter. Appreciate your help.
Berna, I know it's been awhile since you posted this, but I have ran into this issue as well. As I was trying to research the issue, I was playing around with the different cells and think I have figured out the issue. You may have using it the same way I was. I formatted my table under "format as table" option, so that when I enter into the next line/row, it automatically becomes a part of the formatted table. I have found that if I enter in the cell that I referred to in my formula (i.e. entering in P6 of the formula =IF(P6 "", IF(Q6 ="", NOW(), Q6),"")) then I get the "1/0/1900" response, BUT if I enter in any other cell of that row to create the new row in the formatted table, I then can go back to cell in question (P6) and get the correct response/date stamp. I don't know why this happens, but I hope this helps anyone else that may come across this issue to understand a way around it.
Hi! Chandoo, Thank you for the idea you gave. I would never know about using circulation formula this if not this blog. Thank you so much!
This is my try, can you check if there is any bug?
Objective: Update Time stamp if data updated.
1) On Iteration
2) Formula:
Data (A2): User fill in
Time Stamp(B2): =IF(A2<>C2,NOW(),B2)
Data Helper1 (C2): =IF(A2=D2,C2,A2)
Data Helper2 (D2): =C2
Soon, your idea is perfect but i think you only need 1 data helper just use this
Data Helper1 (C2): =IF(A2=C2,C2,A2) (replacing the D2 for C2)
and then you dont need another cell.
Thanks
Hi! Chandoo, Thank you for the idea you gave. I would never know about using circulation formula this if not this blog. Thank you so much!
[...] could use a timestamp to do this Create A Timestamp In Excel With Formulas How to insert timestamps in excel sheet using formulas | Chandoo.org - Learn Microsoft Excel Online but not sure how your spreadsheet is [...]
[...] The app LogIt is good for simple time-stamped notes on a Mac, but for my PC note-takers, I created an Excel file that put a time stamp in column B as soon as they entered any text in column [...]
I am trying to timestamp when a work truck is damaged and entered into the spreadsheet, i am using excel 2010 and the formula i previously had was automaticallly changing the date everyday instead of timestamping the day it was logged in excel. I tried the formula above but nothing happens. Please help
I blog quite often and I truly appreciate your information.
Your article has really peaked my interest.
I'm going to take a note of your website and keep checking for new information about once a week.
I subscribed to your RSS feed as well.
Works, but it only inserts the Date and not the time. Any idea how i can get the Unix timestamp format?
[…] have referred to the link that resolved this : How to insert timestamps in excel sheet using formulas | Chandoo.org - Learn Microsoft Excel Online and for an explanation of circular functions : Excel Circular References - What are they, How to […]
[…] you can use a datestamp formula - which uses circular reference - but could give you other issues ,if you do have unintentional circular reference have a read of these two sites - for setting a time/date stamp Create A Timestamp In Excel With Formulas How to insert timestamps in excel sheet using formulas | Chandoo.org - Learn Microsoft Excel Online […]
[…] how to use a timestamp these links should help Create A Timestamp In Excel With Formulas How to insert timestamps in excel sheet using formulas | Chandoo.org - Learn Microsoft Excel Online […]
Hi, i found this very useful. It perfectly works for me for 2007 version. But , i think copying the formula to the adjacent cell, it repeats the same time stamp, and blinking copy cursor around the copied cell. But otherwise, it is ok. If you format the cell before hand with time and select custom dd/yy/mm h:mm:ss then it is easy to find which cells had been updated lately. Thank you. But is there a short cut to autoshift the row to the top of the excel rows
Hi, i found this very useful. It perfectly works for me for 2007 version. But , i think copying the formula to the adjacent cell, it repeats the same time stamp, and blinking copy cursor around the copied cell. But otherwise, it is ok. If you format the cell before hand with time and select custom dd/yy/mm h:mm:ss then it is easy to find which cells had been updated lately. Thank you. But is there a short cut to autoshift the row to the top of the excel rows
Reply
Best option is to use
=Now()
@Mismail
=Now() will return the Date/Time but it is volatile and will be recalculated every time the worksheet changes and is saved.
So you will loose your original date/time
This is good stuff! Here is another way to automatically enter date and time in a cell using formulas only (does not use circular reference)
http://trumpexcel.com/2014/08/date-time-stamp-in-excel-formula/
Hi,
Can anyone help with how to make the timestamp unique and not change each time the spreadsheet is updated.
I am looking for a way to track the time stamp on tasks, for the start and finish time. So there will be multiple timestamps in the same workbook.
Thanks
My entire timestamp column changes to the most recent timestamp, rendering it useless as a timestamp column
what am i missing? or have done wrong?
-Insert a header or a footer
-Select Custom Header\Footer
-Select left\center\right
-Select the calendar icon and\or the clock icon
-Header\footer will be present once you print the document
I have a report generated 3 times a day using macro I need to show the static date and time in the header to show what time the report was generated.
how can i do this?
@Basu
Why not just add a header to the Report
Goto File, print, Page Setup, Header/Footer tab
Custom Header
Click in the appropriate section (Left, Middle, Right)
Then click on the Date/Time icons
or insert codes &[Date]&[Time]
Accept and print
@chandoo
Thank you for this great site.
I got the timestamp working when a value is entered manually.
However when the cell is filled trough a function it doesn't work and gives #VALUE
I am using:
=IF(E4"";IF(B4="";NOW();B4);"")
E4 can be entered manually or can be filled in automatic trough: =IF(D4="lid";70;IF(D4="lid+vlag";80;any))
If it is filled in automatic the timestamp doesn't work.
This is a balance book for a non profit organisation. And I am using this function so that the price of reoccurring items such as membership cards doesn't have to be entered manually.
I finally got this formula to work but I need to extend it
I need to have a time stamp in col A. which is where the formula is in:
=IF(B2"",IF(A2="",NOW(),A2),"")
But I have 6 columns. B2-G2. I don't intend to use all 6 columns at the same time but I don't know which col. data will go into until I start the work. I've tried this formula but it is incorrect.
=IF(B2:G2"",IF(A2="",NOW(),A2),"")
I'm not very familiar with VBA and am having difficulty with it. Is there a way I can use a formula for this in the spreadsheet?
i have the same query
thank you for this article, very good!
=IF(TODAY()=A20,'Execution Status Today'!$C$20+'Execution Status Today'!$D$20,"")
A20 cell have particular date but date is getting update daily so this formula is not working can you please help me on this?
Thanks in advance,
Nandan.
Hi - I can't get this formula to work - I pasted in exactly as you have it above into B3:
=IF(C3"",IF(B3="",NOW(),B3),"")
and then I enter data into C3 and I just get 0 in B3. I don't think it's a format issue...
cheers,
Matt
it does not work. it brings a message about circular reference and it show nothing!!!
I take it back... I forget to change the button > excel options > formulas > iteration area
@Chandoo, thanks for the help!
Regarding enabling the iterative calculation...
Is it possible to change the setting of the workbook itself? I am creating a spreadsheet that is saved in a shared location where others will be using the sheet and the timestamp as well so it would be best if this setting would work for everyone so I do not need to ask each user to change their excel settings.
Thanks,
Evan
Hi guys,
I am using the following formula to time stamp: =UDF_Date(B69:AC69). The data is in a table and this formula checks if any cells along a row has changed.
The problem is that the data is in a table. So if, for example, someone uses a filter on a column, all the time stamps update for each row.
Any suggestions?
Specifically want the time stamp to only work actual contents along a given row has been updated.
I'm creating a sheet where my employees are using a bar code scanner to scan things into and out of a secure area. Using this formula, and keying the "now" to the time they use the scanner, (and locking down the area that time stamps so that they would not be able to change it) would this work to create that? I have that it in my head that it would look at the cell next to it to see if it were empty or not, and if there was data, then it would "time stamp" it. Am I missing anything? Not looking for something complicated, just a simple method of tracking when things go into the secure area.
[…] To automatically insert the timestamp, there is a smart technique using circular references (thanks to Chandoo for this wonderful technique). […]
Using Circular References to insert static time stamp is surly an advance technique as Chandoo has explained.
I surly learned that Cir Ref also has some intelligent use thanks Chandoo
Surprisingly it has drawn 125+ responses over 7 years since 2009?
Here is a dumb recorded macro which will do the same thing but with no need for any advance concepts :
- no need to enable iterative calculations behind the scene
- no need to have formula pre pasted in the B column
- will print time stamp in any cell in the sheet
- no need to format entire formula column as well ( unless you want different format then what I defined in my Macro)
Method :
1. Simply create a new recorded Macro with any preferred hot key combination ( I used ctrl + t )
2. Then copy paste the following code below the comments line in the macro you created :
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select
Selection.NumberFormat = "d-mmm-yyyy h:mm:ss AM/PM"
ActiveCell.Offset(0, 1).Range("A1").Select ' moves focus to next
' the cell on right
3. Yes you must save As the Excel file as .xslm
4. now just click any cell in say sheet 1
and press "YOUR DEFINED HOT KEY COMBINATION"
and time stamp will be inserted in the selected cell
5. try any other cell
Finally, Like Circular references there is a sensible use for SQL cross join queries ( what we call Cartesian Join )
which in fact has no join at all between tables
most books ( yes really good books not blogs ) will tell us SQL cross join has no use at all
but now use of cross join to create large data set is becoming well known
say you want to create a million unique FULL names
then just have three single column tables with 1000 First Names in 1 table, 1000 middle names in 2nd table and 1000 Last names in 3rd table
drop tables in MS Access SQL Designer and run select query
and you get a million rows of unique FULL names
watch out for memory overload as your machine might hang
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Set rInt = Intersect(Target, Range("B:F"))
If Not rInt Is Nothing Then
'For Each rCell In rInt
'Set tCell = rCell.Offset(0, -1)
'If IsEmpty(tCell) Then
'tCell = Now - Time
'tCell.NumberFormat = "dd/mm/yyyy"
'End If
'Next
Range("A" & (rInt.Row)) = Now
Range("A" & (rInt.Row)).NumberFormat = "hh:mm:ss"
End If
End Sub
@Chandoo, @Zach
Thank you so much for the 'timestamp when a cell is edited' formulae (January 2009). I amended it a bit to help someone out on another forum (http://www.excelforum.com/showthread.php?t=1168801&p=4554997#post4554997). I used Named Ranges to let him put the cells wherever he wanted, but of course that's not really an option if you want to drag the formulae down to monitor a whole range of cells - but it's useful if you're only going to monitor one (or a few).
The formula:
IF(C3"",IF(B3="",NOW(),B3),"")
works as a timestamp when you want to enter a value in one cell to trigger a time stamp (for me it is must my initials). Howver, it will give a circlular reference error on opening the sheet. That is eliminated by setting the Formula options to Iteration = True and number of iterations to "1."
However, those options are not stored with the sheet (they are stored somewhere in the registry and change with each sheet or workbook you open or set), so if you open another workbook without those settings and then open a workbook with the timestamp and setting, those settings will have disappeared.
Solution: You need to put in a macro to set the options when the sheet opens:
Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic
Application.Iteration = True
Application.MaxIterations = 1
End Sub
That solves the problem and the formula works fine every time.
I've used this formula and it's exactly what I need. Though there is also an easier way to write it: =IF(A1="","",IF(B1="",NOW(),B1))
My problem (either way it's written):
- we have this static time stamp focus on the action of a cell when the entry is made.
- our process later involves starting a new day and copying (or cutting) and pasting the data. it copies over fine and seems to stay with the entry when sorted and moved around, but I noticed to say that some timestamps have a date&time entry indicating it was typed in 53 hrs ago, yet I actually just typed it in 30 min ago.
Not sure why this is happening. The filters stay on so that the info moved with the entry.
Any help would be appreciated!
didnt realize my previous post had gone through.
Chandoo must not keep up with this page anymore....
just like to share i encountered the "1/0/1900 12:00:00 AM" issue with the original formula and below formula works for me. Hope it helps somebody out there.
=IF(ISBLANK(B2),"",IF(OR(C2=0,C2=""),NOW(),C2))
Chandoo,
I recommend simplifying the formula. After a few days of trying to figure out what means, I learned that it means "is unequal to".
If you swap the conditions of the formula, you can swap the sign:
=IF(A1="","",IF(B1="",NOW(),B1))
I swapped my signs and the positions of my conditions, and Excel returned the same desired success as your written formula, just simpler and easier to understand because not everyone I teach to understands advanced Excel.
Thanks so much for the very clear directions on setting up an auto time stamp. It worked well.
THANK YOU!!!!!! I have been searching how to do this EVERYWHERE on the web, I finally lost my temper after months and just googled "Time stamps" and ended up here. It's the easiest solution possible but nowhere near the first page of Google. Thank you again <3
Please I need a formula for timestamp
If I enter a name in cell B2, I want it to trigger a time stamp in cell C2
Someone asked the question many years ago:
"I am trying to update multiple rows with a timestamp for each one. I have the formula working for one cell. I then copied it to multiple rows and every time one updates, they all update. Any help would be appreciated. Thank you in advance."
I have the same problem but don't see the answer. Please help! I am working on a spreadsheet for my daughter's school. There will be about 1000 rows. All of column BD has formula's in it like:
=COUNTIF(A1:BA27,"Opening/Closing a Door I")
Then in BD3 every time "Opening/Closing a Door" shows up on the progress report it changes from 0 to 1. All the way down it is different items in the quotes.
What I need is a timestamp for column BE. I found one that seems to be working well for me:
=IF(AND(BD3"",BF3BD3),NOW(),IF(BD3="","n/a",BE3))
The problem is, it updates the entire column. And every time I make a change in the sheet, let's say I put in Opening/Closing a Door I twice, the date changes for all rows.
Is there any way to get this to stop? I am volunteering my time to make this spreadsheet and I have to say on this time stamp alone I have spent a good 10 hours trying to make it work. Unfortunately the time stamp is the most critical part because it tells the head director of the Montessori when something was introduced to a child.
Another problem I have is the date changes every day. Any way to make it fixed unless the data in BD changes?
I would seriously hug anyone with the answer to this. The school wants to become tech enabled and move away from paper but it's taking me so long to do this.
Very helpful... With the formula and 100's of supporting comments to help troubleshoot
@Chandoo
Works great! Thanks!
@Chandoo - it works for empty reference but if I need to track a progress and reference is changing daily, the trick is not working anymore.
What I need is to store the reference value and timestamp on another sheet for each time when the reference is changing it's value. Is this possible with VBA, please?
Example:
- we have a shared tracking sheet on a G-Drive,
- on column J we track a progress in %; column header is "Complete"
- we have a number of 20 tracked rows (activities), each with value in column J,
- on the first day we have a 0%, on the first row,
- second day we have a 10%, on the same row,
- forth day we have a 11%, etc
- I need to track the progress of the changes on column J with a timestamp in a list in a second sheet,
- based on that list we can generate a progress chart or a burn-down chart.
Thanks
Thanks Chandoo for the timestamp
Hi,
Is there a way to make the timestamp work when I copy paste data into the cell? It works beautifully when I enter or just copy paste 1 cell. When I copy paste multiple cells the timestamp only works for the first paste and not the ones after.
Thank you.
Formula worked like a champ! Totally needed this timestamp workaround. Thank you for sharing your Chandoo Voodoo for the rest of us.
A Thankful Excel Grasshopper,
Bryan
When I enter the formula I get a number 43581.66393?
@Joe... Set the cell format to date & time. 43581.66393 is the numeric representation of a date & time in Excel.
Wow....its a very very useful format,
very very helpful .....thanks a lot
Hey,
How would one go about setting up an automatic formula that would automatically insert the date, and it was not dynamic so once you had saved it then came back to it later the date did not refresh each time?
Thanks
Hi Chandoo
First of all, thank you for BEING AWESOME IN EXCEL.
I have learnt a lot in Excel during last 1 year of lockdown, thanks to you.
Regarding timestamp, I have a query -
I have made a file for my sales team who are supposed to fill their daily sales visit reports and send to me on excel. I have created a timestamp and told them about enabling iterative calculation also. However, when they send the file to me and I save the file, the timestamp changes to current date for all. How do I correct this?
PS - I do not use VBA / Macro because my team is not tuned to these and I also need to learn it yet. So I am dependent on formulas only in the cell.
Regards
Rajeev
Question regarding those function.
When I try to enter those function in the cell.
Excel indicate I cannot use it since it detect a formula because of the =
like it want a 1+1 =2.
I don't know why I cannot write in the cell that formula
=IF(C2" " ,NOW() ," ")
Update: I used a function instead for the timestamp
Function EcritureDeDate(Reference As Range)
If Reference.Value "" Then
EcritureDeDate = Format(Now, "yyyy-mm-dd")
Else
EcritureDeDate = ""
End If
End Function
The problem I have is each time I open the excel file, it will not keep the original timestamp; it will update.
Any way to fix the situation?
Best regard,
13 years after this post and I was able to solve my problem. Thanks!!
How make this formula "=IF(H4"",IF(I4="",NOW(),I4),"")" to get the UTC time , as the cell would be updated by teams in different time zone
The time returned by Excel's NOW() is limited to the environment. So if you run it on a PC, it will be same as your computer clock. Excel currently has no simple way to figure out the timezone of the computer or user.