Automatically insert timestamps in excel sheet using formulas

Posted on January 8th, 2009 in Learn Excel - 107 comments

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.

formula-calc-iterations

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:

timestamps-in-excel-formula

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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

107 Responses to “Automatically insert timestamps in excel sheet using formulas”

  1. JacMars says:

    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?

  2. Oliver Montero says:

    Another great suggestion, but I also would like to change the timestamp in case someones modifies the cell. Is that possible without VBA?

  3. Chandoo says:

    @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 :)

    • Berna says:

      @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.

  4. Zach says:

    @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?

  5. Zach says:

    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.

  6. Sumeet says:

    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?

  7. Jim says:

    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

  8. Chandoo says:

    @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

    =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())),"")

    Here is an e-donut for you Zach… :)

  9. Chandoo says:

    @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())),"")

  10. Chandoo says:

    @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 :)

  11. mike says:

    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.

    • Chandoo says:

      @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.

  12. Juwin says:

    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.

    • Chandoo says:

      @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.

  13. Juwin says:

    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.

    • Chandoo says:

      @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 ?

  14. [...] Learn to work with Circular References – Tutorial on Generating Timestamps [...]

  15. Martin says:

    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

  16. Chandoo says:

    @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…

  17. [...] Another column where we generate a time stamp when the user enters the name. Please read this article to generate time stamps in excel [...]

  18. mark says:

    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?

  19. mark says:

    Got it! Never mind…

  20. kaliman says:

    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
    ————————–

  21. Chandoo says:

    @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.

  22. Jaye says:

    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.

  23. Chandoo says:

    @Jaye… I am sorry, but isnt that exactly what this article is all about?

  24. Jaye says:

    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?

  25. MikeV says:

    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.

  26. Chandoo says:

    @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.

  27. Adrian says:

    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?

  28. Chandoo says:

    @Adrian: Have you enabled “iterative calculation mode” from formula settings?

  29. Mel says:

    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”?

  30. Chandoo says:

    @Mel.. Thanks, I am happy you like this.

    And no, we cannot write formulas in such a way. May be you can use VBA.

  31. Elen says:

    @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)

  32. 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)…

  33. Tony Stroupe says:

    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.

  34. Tomas says:

    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

  35. Chandoo says:

    @Tomas: I am not aware of any way to do this without iterative calculations or vba.

  36. Tomas says:

    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..

  37. keith says:

    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

  38. Thomas says:

    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).

  39. Chandoo says:

    @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?

  40. Thomas says:

    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.

  41. Thomas says:

    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.

  42. Chandoo says:

    @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

  43. Thomas says:

    @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.

  44. Chandoo says:

    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.

  45. Thomas says:

    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.

  46. Matt says:

    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?

  47. Chandoo says:

    @Matt.. This technique doesnt work correctly without iterative mode.

  48. [...] But, keep in mind that, in few cases, circular references may be required. For eg. if you want to add timestamps to your workbook. [...]

  49. Mitch Davidson says:

    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

  50. Caleb King says:

    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?

  51. E.Walker says:

    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),””)

  52. E.Walker says:

    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.

  53. Caleb King says:

    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.

    • Joana says:

      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!

  54. John says:

    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!

  55. ezuk says:

    Heya,

    This is an absolutely fantastic tip. Thank you for sharing it. Works very well for me.

  56. Kara says:

    Thank you!! Very helpful :D

  57. louisa says:

    Thanks!! That is great!!
    Although i saw this formula somewhere else, your explanation make me fully understand how it’s work

  58. Jono says:

    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

  59. Patrick says:

    How do I add a date and time stamp which indicates any/which cells and when they were last updated

  60. Dave says:

    Great tip – very neat idea – thanks!!

  61. Luigi says:

    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 !

  62. Hui... says:

    @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

  63. Luigi says:

    @ 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 !

  64. Hui... says:

    @Luigi
    Can you retype the formula only

  65. Luigi says:

    @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 !

  66. Hui... says:

    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

  67. Luigi says:

    @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 !

  68. Praveen says:

    how to generate timestamp with two or more conditions to be met, i tried but it is giving me #value error.

  69. Praveen says:

    How to generate time stamp with multiple if conditions

  70. [...] zonder VBA maar met de optie iteraties ingeschakeld zie How to insert timestamps in excel sheet using formulas | Chandoo.org – Learn Microsoft Excel Online [...]

  71. Greg says:

    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

  72. Jonah says:

    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!!!

  73. Vlad says:

    Thank you very much for this formula!

  74. Shal says:

    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?

  75. vivek says:

    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 !!

  76. [...] a look at this link __________________ A meaningful thread title will get you more answers -Happy with the answer ? [...]

  77. [...] 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 [...]

  78. manzoor sharif says:

    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..??

  79. Riz says:

    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?

  80. Chandra says:

    GOD BLESS YOU ONE AND ALL!!!!!!!!!!!  HALLELUIAH!!!!!!!!!

  81. fizzie says:

    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! 

  82. Mike J says:

    How do you leave a time stamp in a cell everytime the worksheet is saved?

  83. Krishna says:

    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!

  84. Calvin says:

    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.

  85. Thomas says:

    Excellent and easy to follow.  Explanations are crystal clear.  THANK YOU!!!

  86. Phil says:

    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.

    • Hui says:

      @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))
       

  87. Berna says:

    @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.
     

  88. Soon says:

    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
     

  89. RAVI XAVIER says:

    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!

  90. [...] 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 [...]

  91. [...] 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 [...]

  92. Kevin says:

    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

  93. exerciselink.net says:

    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.

  94. hyipbox says:

    Works, but it only inserts the Date and not the time. Any idea how i can get the Unix timestamp format?

  95. […] 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 […]

  96. […] 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 […]

  97. […] 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 […]

  98. jraju says:

    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

  99. jraju says:

    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

Leave a Reply