+ Reply to Thread
Results 1 to 29 of 29

Save date in cell on sheet modification

  1. #1
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Save date in cell on sheet modification

    Hi Everyone

    I have a costing spreadsheet with the date in cell A:1.
    If I change any cell within that spreadsheet I need the date in A:1 to change automatically to the new date.
    Please help with a formula that I can insert in A:1 that can do this.

    I have read up about timestamps but cant seem to find the solution for me.

    Thanks in advance

    Onesock

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Save date in cell on sheet modification

    In A1 >> =NOW()

    Anytime anything on the sheet happens, A1 will recalculate to the new date.
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Jeff

    That works fine ,but is not altogether what Im needing.
    Let me try explain again.
    In cell A1 the date and time is recorded( time is not really needed in my case , but helps with testing your solution).
    If for instance on 20/10/2012 I first created a costing.
    On 21/10/2012 I return to that same spreadsheet to check on something.
    I open it and the date in cell A1 should read 20/10/2012 ( I now know when last that sheet was modified).
    If I happen to change any cell within the spreadsheet - and save ( perhaps a price,quantity, etc ) , I need A1 to automatically update to the current date.
    If I open that spreadsheet a few days later I will be able to then tell when last it was modified.

    Hope this gives a better and clearer understanding of my challenge.

    Regards

    Onesock

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Save date in cell on sheet modification

    In the sheet, place this code which will only fire when the sheet calculates.

    Format the cell for time

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    Maybe try this?
    Please Login or Register  to view this content.
    Change the ("A1:F25") to whatever your workbook peramaters are....

  6. #6
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    and you can format cell A1 to whatever you like...year month day with or without time...

  7. #7
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    if this solves your post, remember to mark solved, and also click the star below my name.

  8. #8
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Jeff

    I have inserted the code in a new module ( Alt F11 ) > insert module > copy/paste code > ( Alt +Q) > save as .xlsm.
    But im not winning . What could I be doing wrong.

    Again - thanks

    Onesock

  9. #9
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Jeff

    Works !! Is there a way that the code could be written that the date/time only modifies if the save tab is clicked.

    Onesock

  10. #10
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Jeff
    On this challenge - is there maybe a formula that will work instead.

    Onesock

  11. #11
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    To do it before it saves use...
    Please Login or Register  to view this content.
    As far as a formula... what code did you insert of Jeff's code? i dont see anything but the "=NOW()"?

  12. #12
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    OOOPS Sorry.

    I missed that I was talking to Legend Rubber - Please forgive
    But if you could please see above and respond I would appreciate it

    Onesock

  13. #13
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Legend Rubber

    Where do I put that code. Is it part of your original code ? or is it a new module.
    Please advise.

    ps. Jeffs code was :

    Please Login or Register  to view this content.
    Thanks

    Onesock
    Last edited by jeffreybrown; 11-20-2012 at 07:00 PM. Reason: Warning...Please use code tags...Thanks.

  14. #14
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    Enter this...

    Please Login or Register  to view this content.
    Put this in "AltF11>ThisWorkbook...
    I have attached...
    Book1.xlsxSavetest.xlsm
    Last edited by Legend Rubber; 11-20-2012 at 05:48 PM.

  15. #15
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Rubber Legend

    Thanks for your patience.
    I have inserted your code : ie POST #5
    CELL A1 has the formula =NOW().
    But each time I change a cell ( just by clicking in/out that cell ) the time(seconds) change.
    I need it to only change if I "SAVE"

    Your code in POST#11 - not sure what to do with that code ( Is it part of your original code or must it be a seperate entry or ?? )

    Then if possible - if all this coding can be dropped, and perhaps instead a formula is placed in A1 that does what Ive been trying to explain

    I would prefer a formula as I need to insert the solution into 100's of files.Saving all files as .xlsm could/will take a long time.

    Thanks again

    Onesock

  16. #16
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Rubber Legend

    please dont give up on me !!

    Onesock

  17. #17
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Save date in cell on sheet modification

    You have somewhat lost me. At first you wanted the date to change when a change was made on the sheet and now you are talking about before a save. What do the two have to do with each other?

    If you do "before save", every time you hit save you will get a new date. I thought when you open the file but did not make a change then you don't want the date updated. You only want the date changed when a modification takes place.

    Please think this through and clearly explain what you are after. Not sure it is a formula you want due to as I explained earlier, Now() will fire every time you open the workbook.

  18. #18
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Jeff

    Please forgive me for the confusion.
    I will try and explain again.

    a) I have 100's of individual costing files all with their inception date in cell A1 ( NO formula )
    b) The costing consists of different variables . ie: material cost , labour , overhead ,etc
    c) As the year progress , so do my costs change
    d) So sometimes I return to a particular spreadsheet to check on pricing.
    e) I do 'click' around to see what I have done previously
    f) Sometimes I make changes to a cell , just to see what the implication would be to the total.
    g) If Im happy with the new total I will 'save' that change - if not I will just exit the spreadsheet.
    h) So my QUESTION - If I make a change anywhere on the spreadsheet - and 'save' - then I need the date (A1) to automatically update to the current date.
    i) If I dont 'save' then no change to the date (A1).
    j) It would suit me better if I could insert a formula in A1 that would do this , but macros would also work.
    k) I find with macros it might take me a really long time to make the changes to all the files .
    l) Also with macros everytime I open the file it has the security window

    Hope this explains a bit better than before.
    And again thanks for everyones patience

    Regards

    Onesock

  19. #19
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    No problem... take out the "=NOW()" in A1, you don't need it...
    Click AltF11, you should see...
    - VBAProject (yourbookname)
    - Microsoft Excel Objects
    Sheet1 (yoursheetname)
    Sheet2 (yoursheetname)
    Sheet3 (yoursheetname)
    ThisWorkbook
    In ThisWorkbook put the following code...
    Please Login or Register  to view this content.
    Change A1 to whatever cell you need...
    That solves this part...
    I will try to figure out a formula, but i am not sure if that will be possible...
    If i have helped, click the little star under my name...

  20. #20
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    this will change to the current date every time you click save...
    so if you dont want to update the date and time, dont press save...
    Also, sorry about leaving you hanging last night, but i had to go...
    Hopefully my previous post helped...
    I still have had now luck wiht a formula.
    Last edited by Legend Rubber; 11-21-2012 at 07:41 AM. Reason: add to

  21. #21
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Rubber Legend
    I too had a crisis to attend to .So sorry for the delay.
    I have entered the code - and voila it works great ----- BUT
    After I close the spreadsheet and then return to it some time later - make changes etc and then save . OOOPs doesnt work anymore.
    Any suggestions.
    Also I have 100's of files and this way will take me forever - so yip Im still after a formula.

    I DO appreciate you efforts so far - THANKS

    Onesock

  22. #22
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    Book1.xlsxSavetest.xlsm
    This works fine for me, and i closed and saved it 10 times...

  23. #23
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Rubber Legend
    Nope not working for me .
    Im using xcel 2003 . Does that make difference.

    Onesock

  24. #24
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    OOOOps sorry

    Legend Rubber - NOT Rubber Legend

  25. #25
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    Sorry, i dont have any way to test in 2003...
    So it works the first time you zave, but no other times?
    or it has never worked again since the first time?

  26. #26
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi
    Works fine after inputting the code . But as soon as I close the sheet and reopen - it doesnt work anymore

  27. #27
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    can you attach your spreadsheet?
    are you sure you have it in the right spot?

  28. #28
    Forum Contributor Legend Rubber's Avatar
    Join Date
    09-21-2012
    Location
    Tillsonburg, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    392

    Re: Save date in cell on sheet modification

    i am going, but will be back at 5am and will try to look at it if you send it...

  29. #29
    Forum Contributor
    Join Date
    08-25-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Save date in cell on sheet modification

    Hi Legend
    Your sample file also doesnt work on my pc.
    So back to the fORMULA option ???

+ 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