Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Dates

  1. #1

    Dates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have an Excel spreadsheet (Excel 2007) that requires data to be entered by individuals prior to a certain date. I would like to know when the data is entered. Using Now and Today provides the information but does not remain constant but changes as the date changes. I can come up with no way to have the entry date entered and then remain constant unless I ask the individuals to actually enter the date when data is entered. This is not condusive to what I am atempting to accomplish which is tracking data input for a number of items without having the individual take the time to enter multiple dates, multiple times and possibly creating errors. If anyone has any suggestions I would very much appreciae them.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    Hi Delore, and welcome to the forum.

    What you're after can be done, but it requires a macro to do it. Not a big deal, and will most likely be easy to write, but we'd need to know a few things...

    Would you want the date tagged in the file as of last edit date, or on each line? What if someone goes back and edits the data after your cutoff period... would that overwrite the date, or leave it as is? Do you want the user the be able to edit the date timestamp, or even see it?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Ken,

    Thank you for relying. Your response sounds promising. To answer your questions, I need multiple dates for the spreadsheet but not for each line but for each column as a different individual is reponsible for a section (column). No editing after cutoff period. It can be hidden but is not a requirement. It is important that once the cutoff period is reached the date cannot be changed.

    Thanks

  4. #4
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    Try this example workbook, the code is in sheet 1 (right click the sheet tab and choose view code)
    Attached Files Attached Files
    Last edited by Simon Lloyd; 2011-06-16 at 05:09 AM.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    I took a slightly different approach to Simon's. I have the dates being cached in row 3, and have set your cutoff date cell B1.

    From there there is a simple macro to record the date/time every time data in the column is modified. In addition, I check if the current date is greater than/equal to the cutoff date and protect the worksheet at opening if so.

    As with Simon's, right click the sheet tab and choose View Code to see the code that records the dates. To see the code that protects the sheet, you'll need to browse to the ThisWorkbook module in the project explorer (in the VBA window.)
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    Hi Ken, that will work (nice and neat as always) if there is only one cut off per sheet, i assumed there would be multiple records that had different cut-off dates, i tried to remove the ability to directly edit the date cell or the check date cell, of course it needs refining to suit but i think in the end it may be a more flexible approach
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    I wasn't entirely sure, but I'm pretty sure that Delore will let us know.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Thanks Ken and Simon. O am traveling until Monday. As soon as I can I will let you know how things went. Thanks again.

    Delore

  9. #9
    Ken,

    Your solution was simple and met my requirements. Thanks for responding to my request for help. Simon's solution also worked but I went with yours because of it's simplicity.
    Thanks again,
    Delore

  10. #10
    Simon,
    Tried your solution and it worked. However I went with Ken's because of it's simplicity. Can't thank the two of you enough for your responses.
    Delore

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •