+ Reply to Thread
Results 1 to 11 of 11

How to have =Today() Function keep today's date adn nt change tomorrow or in future

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    How to have =Today() Function keep today's date adn nt change tomorrow or in future

    Hello!

    First post!

    I am a newbie to excel and have no idea what the Macro stuff is all about (Ive been searching for an answer to this and they all say to work with macros, so if you suggest that, could you please guide me through how to do that?

    Anyway, Id' like to have each cell (E2 through E46) say the date that a certain other respective cell in G2 thoguh G46 contains the phrase "Done!" and give me the date that that phrase is input into but have the date not change when I open the excel file tomorrow or any other date. The cell will hae a formula that will give me last month's date in case the cell in the G column says "Call!" in case that is important. If I need a macros or a code for this, please explain with detail since I have no idea how to run one.

    Thanks so much! Ive been trying to figure this out for a week now!

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: How to have =Today() Function keep today's date adn nt change tomorrow or in future

    Right Click on the sheet name at the bottom of excel.

    Paste this into the window that opens and close it.

    Save the file as a Macro enabled workbook.

    Ok now change any cell in the range G2 to G46.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-09-2013
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to have =Today() Function keep today's date adn nt change tomorrow or in future

    Thanks for the help mehmetcik, but it didnt work I tried changing the date in my internal system and the date on the cells change along with it.

    I'm guessing I was supposed to paste the above formula into the code box? You didnt specify.
    Am I supposed to click save or apply somehow after I paste the formula?
    Here's a picture of what I put in:
    Untitled.png

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: How to have =Today() Function keep today's date adn nt change tomorrow or in future

    You didn't follow my instructions.

    Right Click on the sheet name at the bottom of excel.

    Paste this into the window that opens and close it.

    Save the file as a Macro enabled workbook.

    Ok now change any cell in the range G2 to G46.




    The attached file works.

    Try it

    Then right Click on the sheet name at the bottom of excel to see the code.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-09-2013
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to have =Today() Function keep today's date adn nt change tomorrow or in future

    Great! it worked! But now the only problem is that I need the cell in Column E to tell me the date of exactly last month from today's date if instead of done in column G, it says "Call!" . I tried, putting in an =IF formula in the E cells but the Today's date code overrides it if I change the entry in G from Call! to Done. Is there a way so that I can have both?

    Thanks!

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: How to have =Today() Function keep today's date adn nt change tomorrow or in future

    Try This Sample
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-26-2014
    Location
    Washington state
    MS-Off Ver
    2007
    Posts
    3

    Re: How to have =Today() Function keep today's date adn nt change tomorrow or in future

    I have the same issue. I have operators who enter text in column B and used the =IF(B20<>"",TODAY(),"") operation to enter the date and time inot column A. Of course, column A keeps changing to current date. I would like to lock the date so it doesn't change. For instance, if I enter data today into column B and the date automatically goes into column A, I don't want that date to change when I open the worksheet tomorrow or in the future. Any help would be greatly appreciated.

  8. #8
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: How to have =Today() Function keep today's date adn nt change tomorrow or in future

    See Tony Valko's post dated yesterday at 10:40am. This may be what you're trying to accomplish.

    http://www.excelforum.com/excel-form...ml#post3845200

  9. #9
    Registered User
    Join Date
    09-26-2014
    Location
    Washington state
    MS-Off Ver
    2007
    Posts
    3

    Re: How to have =Today() Function keep today's date adn nt change tomorrow or in future

    Thanks hoyasaxa215 . But not quite what I was looking for. My operators will be entering a bunch of data in multiple rows on a given day. My formula has the date and time stamp autofilled into the adjacent row (cell) and they continue on down the line. When they save the information at the end of the day, all is fine, but when they re-open the worksheet the following day, the date changes to whatever day the sheet was re-opened. I cannot for the life of me get the date and time stamp to keep from changing in future re-opens of the worksheet. I see multiple threads regarding my topic, but all are quite different than what I am trying to accomplish. Here is my formula again. Column B gets the entered data (i.e. manually typing or scanning information) then Column A gets =IF(B12<>"",NOW(),"")and the date and time stamp are created. In this case, data is entered into line 12. I have the same formula all the way down to line 6860 in the spreadsheet. I just cannot keep the time and date stamp from changing. I don't know VB but I am almost positive that is my only solution. Any other help would be great. Thanks.

  10. #10
    Registered User
    Join Date
    09-06-2023
    Location
    Philippines
    MS-Off Ver
    10
    Posts
    1
    Need help with same issue. Mine is automated date in Column D when I entered a data in B column but I want to retain the date when I entered the data the next day.



    Quote Originally Posted by mehmetcik View Post
    Right Click on the sheet name at the bottom of excel.

    Paste this into the window that opens and close it.

    Save the file as a Macro enabled workbook.

    Ok now change any cell in the range G2 to G46.

    Please Login or Register  to view this content.

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: How to have =Today() Function keep today's date adn nt change tomorrow or in future

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #1 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 09-29-2014, 12:35 PM
  2. [SOLVED] Future Date - TODAY() : not giving expected result
    By Psycho_uk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2013, 04:37 AM
  3. Date should not change based on cell 'c7' today function
    By mukeshbaviskar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-14-2013, 03:10 AM
  4. Replies: 6
    Last Post: 08-23-2012, 12:14 PM
  5. Finding the closest future date from Today from a list of dates
    By maharg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2008, 09:45 AM

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