+ Reply to Thread
Results 1 to 30 of 30

insert date fixed

  1. #1
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    insert date fixed

    Hi to all

    if yes in column O or column P put the modification date in column Q - it must remain fixed

    attached example
    Attached Files Attached Files
    Last edited by xam99; 01-17-2021 at 05:41 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: insert date dixed

    If I understand what you are trying to do, that sounds like you are trying to enter a time stamp. If I've understood correctly and you are allowed to enable iteration in your spreadsheet(s), then try this tutorial: https://chandoo.org/wp/timestamps-excel-formula-help/
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date dixed

    The only other alternative is to use a Worksheet Change Event handler monitoring columns O and P.

    Are you ok with a VBA solution?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date dixed

    Hi to all,
    ok tms

  5. #5
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date dixed

    mrshorty
    i tried the formula as in the link but it gives circular reference

    Please Login or Register  to view this content.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date dixed

    i tried the formula as in the link but it gives circular reference
    and that's why you need to "enable iteration in your spreadsheet(s)".

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date dixed

    Code option:

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date dixed

    Hi tms, thanks you.
    The macro work well, unfortunately the word "yes" is inserted by a VLOOKUP formula and the date is not inserted
    if I enter "yes" by hand it works

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date fixed

    Provided the cells in columns O:P are dependent on other cells on the same sheet this should work.

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    Last macro doesn't work, even with yes entered by hand

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date fixed

    Did you note:
    Provided the cells in columns O:P are dependent on other cells on the same sheet this should work.
    Post a sample workbook. See the yellow banner.

  12. #12
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    HI
    here example
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: insert date fixed

    I don't know if the reason you did not enable iteration is because you were not allowed to or if you just overlooked that requirement. Assuming it was just overlooked, here's what I did (using Chandoo's circular reference formula method):

    1) The condition is not only a single cell, but 2 cells, so the simple if is blank check (outer if()) will not work. I need a way to check if either (or both) of two cells contain the word "si". A COUNTIFS() should work for this COUNTIFS(O5:P5,"si").
    2) The rest should be the same as in Chandoo's tutorial =IF(COUNTIFS(O5:P5,"si")>0,IF(Q5="",NOW(),Q5),"").

    As you enter values in Foglio2, it will add time stamps in column Q for when the word "si" was first entered into D or E of each row.

    Will that work, or would you prefer the VBA approach because you are not allowed to enable iteration?

  14. #14
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    Hi mrshorty
    if active
    Activate Iterative Calculation
    (Attiva Calcolo Iterativo)
    then it will work for all workbooks
    it is better maybe vba
    Last edited by xam99; 01-18-2021 at 04:50 PM.

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: insert date fixed

    I don't know that VBA is better or not -- it really depends on whether or not you are comfortable having iteration enabled. The main downside that I have seen from having iteration enabled is that Excel will not give you warnings when you accidentally make a circular reference. In my experience, it really depends on how important those circular reference warnings are to you.

    I am not as experienced with creating these kind of VBA procedures as TMS and others. Perhaps something like what TMS has already proposed, but moved up to a workbook.sheetchange event procedure (https://docs.microsoft.com/en-us/off...ok.sheetchange )? Or maybe change it to a .calculate event procedure (that probably needs some place to store the previous state of Foglio1 and/or Foglio2).

    I don't know which approach is better. How would you like to proceed?

  16. #16
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    Since then the workbook will be used by other colleagues where I work I would not have any problems.
    I think it is better vba

  17. #17
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    HI to all,
    i posted this thread in other forum
    https://www.mrexcel.com/board/thread...-fixed.1158917
    Last edited by xam99; 01-19-2021 at 11:27 AM.

  18. #18
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: insert date fixed

    How well did you understand what TMS did? Did you read the help file for the workbook.sheetchange event?

    I created a workbook.sheet change event in the ThisWorkbook module, copied TMS's code (without the Sub/End Sub statements) into this procedure. Added "If sh.name<>"Foglio2" then exit sub" statement, a few other minor modifications, and it seems to work.
    Please Login or Register  to view this content.
    Does that work?

  19. #19
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    Hi mrshorty
    i put the macro in thisworbook but it doesn't work
    Attached Files Attached Files

  20. #20
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date fixed

    Adjusted to work for "yes" or "si"

    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    Hi tms
    now it works perfectly
    thanks you

  22. #22
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    Hi tms the above macro works on a workbook and is fine
    Now there is a problem for another macro that inserts the date into a cell
    The post#7 macro is perfect:
    Please Login or Register  to view this content.
    the problem that "yes" is entered not by hand, not by formula
    but it is inserted with another macro and this macro does not insert the date
    Last edited by xam99; 01-19-2021 at 04:45 PM.

  23. #23
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date fixed

    Entering a value with VBA is the same as entering it manually. The code in post #20 only works for "yes" and it needs to be in the worksheet where the changes occur.

    The alternative code provided by Alan and amended by me only works for changes made to Foglio2 and updates Foglio1. It works for "yes" and "si" but nothing else.

  24. #24
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    The macro in post#7 works fine but YES or NO is entered
    with a macro with this function

    Please Login or Register  to view this content.
    Attached Files Attached Files

  25. #25
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date fixed

    Try this then:

    Please Login or Register  to view this content.

  26. #26
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date fixed

    Note that my code excludes any changes other than those in columns O:P. Your code examples are changing column 10, which is column J.

  27. #27
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    Hi
    this is the position of the cells in the new workbook, only colums J

    Please Login or Register  to view this content.
    however the "YES" is inserted by the post#24 function
    perhaps for the new macro does not recognize
    Worksheets("ordina_mail_manuale").Cells(ultimariga, 10).Value = "SI"
    Attached Files Attached Files
    Last edited by xam99; 01-19-2021 at 05:37 PM.

  28. #28
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: insert date fixed

    So we have two macros trying to run here -- one that makes changes to one tab and we are trying to come up with a second macro that detects the change made by the first macro and makes another change (adds date stamp). Am I understanding correctly?

    Is there some reason you cannot have the first macro simply write the date stamp into the appropriate cell when it makes the change (and a second macro is not even needed)? It seems like it should be as easy as following the statement(s) in post 24 with something like thisworkbook.sheets(??).Cells(??,??).value=date
    Please Login or Register  to view this content.
    It seems to me that if macro 1 is making the Yes/No/Si changes, have it make the date stamp change as well.

  29. #29
    Forum Contributor
    Join Date
    08-27-2019
    Location
    europa
    MS-Off Ver
    excel 2007
    Posts
    437

    Re: insert date fixed

    HI mrshorty now works
    Please Login or Register  to view this content.
    with the TMS macro
    thanks to TMS and mrshorty
    a greeting
    xam

  30. #30
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: insert date fixed

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. [SOLVED] Insert default date into blank cells in date column
    By deleter in forum Excel General
    Replies: 9
    Last Post: 08-01-2017, 11:36 AM
  2. [SOLVED] Insert static date automatically on pasting large amount of date
    By Toro- in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-21-2016, 08:11 AM
  3. [SOLVED] Function/formula to compare and insert todays date or yesterdays date
    By TC922 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2013, 05:28 AM
  4. Complex VBA problem auto insert date and time, auto insert comment, etc.
    By rojashan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-29-2013, 09:40 PM
  5. [SOLVED] Insert 'From Date - To Date into pivot table/chart as a title
    By Smudge.Smith in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-09-2013, 10:45 AM
  6. Insert tomorrow's date unless Friday. If Friday, insert Monday's date.
    By Cboeree in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-13-2012, 01:21 AM
  7. Replies: 4
    Last Post: 07-27-2012, 04:10 AM

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