Sep 28 2023 01:43 PM
Sep 28 2023 01:43 PM
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!
Sep 29 2023 03:26 AM
You may use data validation rule like
Sep 29 2023 07:37 AM
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?
Sep 29 2023 07:55 AM
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.
Sep 29 2023 08:15 AM
Here is a screenshot of my sheet, some information is blocked for privacy.
Sep 29 2023 08:48 AM
Please check attached file if I understood your logic correctly
Sep 29 2023 09:26 AM
Sep 29 2023 09:36 AM
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
Sep 29 2023 01:11 PM
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.