+ Reply to Thread
Results 1 to 9 of 9

Date stamp in Table

  1. #1
    Registered User
    Join Date
    10-17-2012
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    39

    Date stamp in Table

    I am trying to include a date stamp in column A which does not change after each calculation. The following formula seems to do the job until I turn the data into a table.

    =IF(B2<>"",IF(A2="",NOW(),A2),"")

    When the data is turned into a table, the date defaults to 01 Jan 1900 00:00

    Does anyone have any suggestions how to get this to work correctly in a tbale where the formula will automatically be inserted when new data is added.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Date stamp in Table

    It's working fine for me and not sure what is happening there


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Date stamp in Table

    Don't use a formula - it will always re-calculate itself.

    For a date stamp you can just do CTRL-; (Ctrl-semicolon) in the cell, and for a time stamp CTRL-: (Ctrl-Shift-semicolon)

    Hope this helps.

    Pete

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Date stamp in Table

    See this and this one
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    10-17-2012
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    39

    Re: Date stamp in Table

    Thanks Pete,

    The formula is a circular reference that has maximum iterations set as one, so should not keep re-calculating

    Trax.

  6. #6
    Registered User
    Join Date
    10-17-2012
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    39

    Re: Date stamp in Table

    Fotis,

    Thanks, I have seen some of this already. The formual based ones do not seem to give me the current date (remain at 01 Jan 1900).

    I have usewd a vba solution to a similar requirement before. Not sure if the vba bit will work within an excel table - will have to give it a try.

    Trax

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Date stamp in Table

    Trax.

    I have used this way for more than 50 times in the past(more than 20 times in this forum) and always works fine.

    But never in a table. So i don't really know what happens there.

    For VBA solutions forum and net is full of these.

  8. #8
    Registered User
    Join Date
    10-17-2012
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    39

    Re: Date stamp in Table

    Thanks Fotis,

    Guess I will have to use VBA!

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Date stamp in Table

    Still I am unable to understand what is the need for VBA in this case

    Tested in 2007 and 2010 and its working fine for me

+ 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