How do I lock cells in excel365 by date and time

Question

Hi,
I have a table which is editable. So I have say 10 rows 3 columns. In each of the rows I want to be able to stop them being editable after a date and time. Each row will may have the same date but a different time.
So in row one and the 3 columns ( 3 cells) I want to stop editing after 24th August 2023 at 1800 hours.
In row 2 and the 3 columns ( 3 cells) I want to stop editing on the 24th August at 18:30
Row 3 would be 26th August at 19:00
How would I do that on the worksheet please.

Answer 1

Hi,

To make cells uneditable based on specific dates and times, you can use a combination of conditional formatting and data validation in Excel. Here's how you can do it-

1. Select the cells in row 1 (3 cells) that you want to restrict editing for.
2. Go to the "Home" tab in the Excel ribbon and click on "Conditional Formatting" in the "Styles" group.
3. Select "New Rule" from the dropdown menu. The "New Formatting Rule" dialog box will appear.
4. Choose the option "Use a formula to determine which cells to format."
5. In the "Format values where this formula is true" field, enter the following formula: =NOW() > DATE(2023,8,24)+TIME(18,0,0)
6. Click on the "Format" button and go to the "Protection" tab. Check the box for "Locked" to lock the cells.
7. Click "OK" to close the "Format Cells" dialog box, and then click "OK" again in the "New Formatting Rule" dialog box.
8. Now, the selected cells in row 1 will be locked and uneditable after August 24, 2023, at 18:00.

Repeat the above steps for each row, changing the formula to match the specific date and time for each row.

Best Regards.

Answer 2

Thank you @Tanay Prasad that is great. I have manged to do that however I want to lock the cells based on the date and time of another cell.

So for instance what you have said above will work however I would like a conditional formula based on the date and time of another cell.

So the now is 06/13/2023 and the time is 12:12 however in the cell I want to lock based on the date and time of another cell is what I want to do .

So the cells I want to lock are C3,D3,E3 but I dont want them to lock until the date and time has expired on cell F3 . In cell F3 I would manually input the date and time as 06/13/2023 time 12:20

I would put the Now and Time into another cell say B1 so basically I want to lock C3,D3,E3, when the F3 cells goes past the B1 cell.

I hope that makes sense.

Answer 3

I think that can only be done using VBA