Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Making cells/columns inaccessible until a certain amount of time has passed since a specific date?

Copper Contributor

I want to edit a timesheet so that a employee cannot edit specific cells/columns till 6 months past a certain date has passed. I have attempted with conditional formatting and data validation using an =AND formula (=AND('Employee Info'!$C$5<>"",'Employee Info'!$C$5>='Employee Info'!$C$10)), but neither has worked. Will this require coding? Thanks!

9 Replies

@Sergei Baklan 

 

Thank you, this worked partially. The validation needs to happen across multiple sheets (pay periods), and automatically prevent the user from entering data in those cells till the date identified by the formula has been reached in real time. So if the equation is =TODAY()>EDATE(C5, 6) with C5=1/1/2023 and the answer to EDATE(C5,6) being 7/1/2023, I want excel to recognize that the date 7/1/2023 has been reached in real time and disable the function for the remaining cells. Is this possible?

@ATWork1075 

Sorry, I didn't catch. Date 7/1/2023 is fixed through cell C5 and number of months. We compare it with current date, thus the rule works dynamically. If the rule meets condition you may enter any value into the cell with that rule. If not we have an alert and no value could be added here. We may apply the rule to as many cell as needed and use in formula cells from another sheets. 

Perhaps you illustrate on small sample file or at least screenshot how exactly your data is structured and which cells are to be verified.

@Sergei Baklan 

 

Here is a screenshot of my sheet, some information is blocked for privacy. 

 

ATWork1075_0-1696000496865.png

 

@ATWork1075 image.png

Please check attached file if I understood your logic correctly

 

Thank you, this is very close! The formula needed to be changed to say > rather than <, which created the conditions I was looking for. Only issue is that the A column value has to be selected in the equation, so if I apply this =$A11>EDATE($O$24,6) to the Data Validation, then the command is not based on whether the user is accessing the document 6 months from the date in O24 at the time of access.

@ATWork1075 

I guess time of access is TODAY(). If user open the file (s)he accessed it. If you need both conditions it could be like

=($A11<EDATE($O$24,6) )*( TODAY()<EDATE($O$24,6)  ) or like

@Sergei Baklan 

Thank you. Unfortunately I am still not achieving what I am looking for. As you can see, all cells except for R9 and R13 allow for values to be entered into the cells. 

 

ATWork1075_0-1696018297382.png