Dates

[Delore]

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.

[Ken Puls]

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?

[Delore]

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

[Simon Lloyd]

Try this example workbook, the code is in sheet 1 (right click the sheet tab and choose view code)

[Ken Puls]

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.)

[Simon Lloyd]

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

[Ken Puls]

I wasn't entirely sure, but I'm pretty sure that Delore will let us know.

[Delore]

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

[Delore]

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

[Delore]

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