How do I lock cells in excel365 by date and time

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.

• Prepare your table: Organize your data in a table format with date and time values in specific columns (e.g., Column A for dates and Column B for times). Ensure each row has the appropriate date and time information.
• Set up cell protection: By default, all cells in Excel are locked, but this won't be effective until you protect the worksheet. To adjust this, select all the cells in the table that you want to remain editable (the ones that should not be locked). Right-click and select Format Cells. In the Protection tab, uncheck Locked. This ensures the selected cells remain editable after the worksheet is protected.
• Use VBA to lock cells based on date and time:
  Press Alt+F11 to open the Visual Basic for Applications editor. In the editor, go to Insert > Module and paste the following code:

Sub LockCellsByDateTime()
    Dim cell As Range
    Dim currentTime As Date
    
    currentTime = Now ' Get the current date and time
    
    ' Loop through the range of cells (modify the range according to your table)
    For Each cell In Range("A2:A10") ' Assuming dates are in A2:A10
        ' Check if the cell's date and time have passed
        If cell.Value + TimeValue(cell.Offset(0, 1).Value) < currentTime Then
            ' Lock the corresponding cells in the row (assuming data is in 3 columns)
            cell.Resize(1, 3).Locked = True
        End If
    Next cell
    
    ' Protect the sheet to enforce cell locking
    ActiveSheet.Protect
End Sub

• Run the VBA code: After entering the code, press F5 or run the macro. This will lock the cells based on the specified date and time.

Testing: The cells will become locked once the current date and time exceed the value in the corresponding row's date and time columns.

This solution locks specific rows after a certain date and time, ensuring they cannot be edited.

VBA does not work if the file is uploaded to SharePoint.

I think that can only be done using VBA

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.