Clear contents based on date

[deemer98]

Good afternoon all. Is there a way to set a formula or conditional format that will clear the contents of a cell after so many days?

Cell D1 contains a date. After365 days, I want that cell to clear the date out. Is that possible?

Thanks!

 

[Joe4]

Conditional formatting can change the format of a cell, but not its contents. So you could change the cell text to white, so it is virtually invisible to see against the background.
If you wanted to remove it, you would need to use VBA.

If VBA is an option you want to pursue, let us know, along with any other pertinent details (i.e. are there other cells this should apply to)?

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet that contains the date and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro will check the date in D1 every time the sheet is activated and clear the date if it is a year or more old.

Private Sub Worksheet_Activate()
    If Date > Range("D1").Value + 365 Then Range("D1").ClearContents
End Sub

 

[deemer98]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet that contains the date and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro will check the date in D1 every time the sheet is activated and clear the date if it is a year or more old.

Private Sub Worksheet_Activate()
    If Date > Range("D1").Value + 365 Then Range("D1").ClearContents
End Sub

So I used D1 as an example, but the real cells will be D3:AA3, D5:AA5, etc on odd numbered row through D59:AA59. I'm assuming I would just enter them all like I started with D3:AA3, D5:AA5?

 

[deemer98]

So, I tried the exact VBA code as described for D1 and it worked...once. I put in the code, save it, close the workbook, open it up and the date is gone. If I put the date back in and go through that process again, the date remains. Am I missing something?

 

[Joe4]

So, I tried the exact VBA code as described for D1 and it worked...once. I put in the code, save it, close the workbook, open it up and the date is gone. If I put the date back in and go through that process again, the date remains. Am I missing something?

How many sheets are in your workbook?
What is the name of the sheet that you want this to apply to?
What value do you have in cell D1?

 

[deemer98]

How many sheets are in your workbook?
What is the name of the sheet that you want this to apply to?
What value do you have in cell D1?

1. Just one sheet
2. Attendance
3. Actually it's cell D3 that we need to start with. In that cell is a date of 10 Aug 2020.
Each ODD numbered row (3-59) and columns D-AA could have a date in it if I enter it.

 

[Joe4]

OK, because there is only one sheet, I think it is best to use a Workbook_Open event that runs when you first open the workbook.
Place the following code in the "ThisWorkbook" module, and the code will run automatically when opening the file:

Private Sub Workbook_Open()

    Dim cell As Range
    Dim cl As Long
    Dim rw As Long

    Application.ScreenUpdating = False

'   Loop through columns D to AA
    For cl = 4 To 27
'       Loop through odd number rows from to 3 to 59
        For rw = 3 To 59 Step 2
'           Check dates in cells to see they are more than a year old
            If (Cells(rw, cl) > 0) And (Date > (Cells(rw, cl) + 365)) Then
'               Clear date from cell
                Cells(rw, cl).ClearContents
            End If
        Next rw
    Next cl
    
    Application.ScreenUpdating = True

End Sub

 

[deemer98]

OK, because there is only one sheet, I think it is best to use a Workbook_Open event that runs when you first open the workbook.
Place the following code in the "ThisWorkbook" module, and the code will run automatically when opening the file:

Private Sub Workbook_Open()

    Dim cell As Range
    Dim cl As Long
    Dim rw As Long

    Application.ScreenUpdating = False

'   Loop through columns D to AA
    For cl = 4 To 27
'       Loop through odd number rows from to 3 to 59
        For rw = 3 To 59 Step 2
'           Check dates in cells to see they are more than a year old
            If (Cells(rw, cl) > 0) And (Date > (Cells(rw, cl) + 365)) Then
'               Clear date from cell
                Cells(rw, cl).ClearContents
            End If
        Next rw
    Next cl
   
    Application.ScreenUpdating = True

End Sub

I copied and pasted your code into the "view code" as you described yesterday. I saved it as a Macro Enabled Excel file. I reopened the file, enabled content, entered a couple random dates (over a year old) in ODD numbered rows and saved it. Reopened the file and the dates were still displayed

 

[deemer98]

Hang tight.....Let me try putting it in the THISWORKBOOK module as you told me