+ Reply to Thread
Results 1 to 4 of 4

adding a time stamp in a cell

Hybrid View

  1. #1
    Registered User
    Join Date
    10-11-2007
    Posts
    7

    adding a time stamp in a cell

    I have column a2 thru a20 where I add data. I want column b2 to b20 to automatically enter the date and time I entered the data in column a2,a3,.... I am using the the following formula in column b: =IF(A2>0,NOW(),IF(A2=0,"")). It work okay when I enter data in a2 The date and time is automatically entered in b2, but when I add new data at a much later time in a3 the time changes in b2 with the date and time that is auto entered in b3 when the new data was entered. How do I overcome this?
    Thanks in advance.
    Alex

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    i found this some where with a search(http://www.mcgimpsey.com/excel/timestamp.html)
    You can use a circular reference to enter the time when a change is made in another cell, then maintain that time. Choose Tools/Options/Calculation and check the Iteration checkbox. Then, if your target cell is A1 and you want the date/time to appear in B1, enter this in B1:

    =IF(A1="","",IF(B1="",NOW(),B1))
    Format B1 as you wish to display date, time, or both. If A1 is initially blank, B1 will return a null string (""). When a value is entered into A1, B1 will evaluate as "", therefore NOW() will be returned. After that (as long as A1 remains populated), B1 will evaluate to a date/time and therefore will return the value in B1 - i.e., the date/time.

  3. #3
    Registered User
    Join Date
    10-11-2007
    Posts
    7

    adding a time stamp in a cell

    Your answer is exactly what I was looking for.
    Thank you very much.

  4. #4
    Registered User
    Join Date
    10-11-2007
    Posts
    7

    Another question RE:adding a time stamp in a cell

    Although this may sound odd, how can I make the time stamp be yesterday's date when I add data in A1 today. I tried adding a "-1" after the now() but it does not work. Thinking ahead how can I make todays date be either past or future date.
    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1