I couldn't figure out how to make 2 formulas

Occasional Contributor

the first one I need it to check if I5 is 0 to put todays date in the cell.

the second one needs to check if J5 has a date in it, if it does write discarded (in red) if its an empty cell leave empty


Any advice? Thanks in advance 

3 Replies


Where do you want the formulas?

Should the date change every day or should it remain fixed once it has been entered?

For the first one its the formula would be in J5 and check I5 for a 0 and then the date is fixed.
For the second one I think I got it. I used =IF(ISNUMBER(J5),"DISCARDED","")


Right-click the sheet tab.

Select 'View Code' from the context menu,

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("I5"), Target) Is Nothing Then
        If Range("I5").Value = 0 And Range("I5").Value <> "" And Range("J5").Value = "" Then
            Application.EnableEvents = False
            Range("J5").Value = Date
            Application.EnableEvents = True
        End If
    End If
End Sub