fbpx
Search
Close this search box.

Automatically insert timestamps in excel sheet using formulas

Share

Facebook
Twitter
LinkedIn

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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

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

      • Marion says:

        I am also getting 1/0/1900 as a result. What are we missing?

        • nooo1 says:

          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

        • Ken Beaumont says:

          Set the iteration to 1 - that should do it

        • Vanitha says:

          @Berna and @Marion, please try using IFERROR function before you use the formula, example =IFERROR(1/(1/IF(C3"",IF(B3="",NOW(),B3),"")),"")

      • Scott G. says:

        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?

    • Paddy says:

      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

    • Subash says:

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

  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.

    • Charlie says:

      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

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

    • Riz says:

      I can't seem to get the formula for cell F3 right. Can you help please?

    • Squidlo says:

      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.

      • jojo says:

        I have the same problem. No suggestions have worked so far.

        • Tilley says:

          Have you been able to find a solution where you can have multiple rows with an independent static Excel date/time stamp?

          • Andy says:

            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

  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.

      • Alias says:

        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.

        • Alias says:

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

  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 😀

  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.
     

    • Megan says:

      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.

  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
     

    • JesusUrdaneta says:

      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

  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

  100. mismail says:

    Best option is to use
    =Now()

    • Hui... says:

      @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

  101. Sumit Bansal says:

    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/

  102. 4217 says:

    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

  103. Naruto says:

    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?

  104. David says:

    -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

  105. Basu Navindgi says:

    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?

    • Hui... says:

      @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

  106. Cedric says:

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

  107. Stephanie says:

    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?

  108. bazsinho says:

    thank you for this article, very good!

  109. Nandan says:

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

  110. Matt says:

    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

  111. nonlinearly says:

    it does not work. it brings a message about circular reference and it show nothing!!!

  112. Evan says:

    @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

  113. Vadim says:

    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.

  114. MarkAM says:

    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.

  115. […] To automatically insert the timestamp, there is a smart technique using circular references (thanks to Chandoo for this wonderful technique). […]

  116. Yogendra Mehta says:

    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

  117. tony says:

    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

  118. Aardigspook says:

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

  119. Tim says:

    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.

  120. Clgrunen says:

    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!

  121. shirley says:

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

  122. Charlene says:

    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.

  123. Pete says:

    Thanks so much for the very clear directions on setting up an auto time stamp. It worked well.

  124. Dev says:

    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

  125. Idera says:

    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

  126. JennC says:

    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.

  127. Reuben says:

    Very helpful... With the formula and 100's of supporting comments to help troubleshoot

  128. LPL says:

    @Chandoo

    Works great! Thanks!

  129. Silviu says:

    @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

  130. Johnny says:

    Thanks Chandoo for the timestamp

  131. Dzung says:

    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.

  132. Bryan Lee says:

    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

  133. Joe says:

    When I enter the formula I get a number 43581.66393?

  134. Marthen supit says:

    Wow....its a very very useful format,
    very very helpful .....thanks a lot

  135. R says:

    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

  136. Rajeev Saraf says:

    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

  137. Marc says:

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

  138. Marc says:

    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,

  139. Todd says:

    13 years after this post and I was able to solve my problem. Thanks!!

  140. Rash says:

    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

    • Chandoo says:

      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.

Leave a Reply