+ Reply to Thread
Results 1 to 7 of 7

Stamp a cell with todays date without the date changing tomorrow

  1. #1
    Registered User
    Join Date
    01-20-2012
    Location
    Lancashire,England
    MS-Off Ver
    Office 2007
    Posts
    11

    Stamp a cell with todays date without the date changing tomorrow

    Not sure if this can be done without vba (preferred)

    If a1,a21,a41 have a value greater than 0, I wish to stamp cell b1,b21,b41 and so on with todays date, but without the stamped value changing/advancing tomorrow. At the moment cells b1,b21,b41 are copied down as follows. =if(a1>0,TODAY(),"") This works fine, but the date stamp of course changes tomorrow. I can not apply code to the whole b column as cells b2:b20, b22:40 etc have other non-date format data to which the code does not need to apply - the todays date value cell occurs every 20 cells.

    Thanks in anticipation.

  2. #2
    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: Stamp a cell with todays date without the date changing tomorrow

    See here how to do this.

    http://www.mcgimpsey.com/excel/timestamp.html
    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.

  3. #3
    Registered User
    Join Date
    01-20-2012
    Location
    Lancashire,England
    MS-Off Ver
    Office 2007
    Posts
    11

    Re: Stamp a cell with todays date without the date changing tomorrow

    Quote Originally Posted by Fotis1991 View Post
    thanks, but I need the date stamp to not change to tomorrows date when the sheet is viewed tomorrow

  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: Stamp a cell with todays date without the date changing tomorrow

    Both formula and code do EXACTLY this!

  5. #5
    Registered User
    Join Date
    01-20-2012
    Location
    Lancashire,England
    MS-Off Ver
    Office 2007
    Posts
    11

    Re: Stamp a cell with todays date without the date changing tomorrow

    Hi Fotis,

    both formula and code applied yesterday returned "7th February 2013", today, the same cell is displaying "8th February 2013". What am I missing? I require the cell to remain on the date on which it was originally stamped and not to advance 1 day at midnight. I have attempted to alter the clock settings, but always the date advances when the time displays midnight. Thanks again, really appreciate you taking time to look at this.

  6. #6
    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: Stamp a cell with todays date without the date changing tomorrow

    See the example. Wait until Tomorrow to be sure.

    Be carefull!

    Choose Tools/Options/Calculation and check the Iteration checkbox Be sure that you change the 100 to 1!
    Attached Files Attached Files

  7. #7
    Forum Contributor thameem127's Avatar
    Join Date
    04-06-2012
    Location
    Jeddah,Saudi Arabia
    MS-Off Ver
    Excel 2003,Excel 2007
    Posts
    321

    Re: Stamp a cell with todays date without the date changing tomorrow

    Try this code

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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