IFblank function

I have my headers in Row 1: an estimated date in J2 and Date Arrived in K2

The est ship date will have a date, then when I put a date in the date arrived, I want J2 to say completed.

I tried =IF(ISBLANK($K$2),"","Complete") and =IF(ISBLANK(K2),"","Complete") but it doesn't seem to work. It worked once, then I tried it again and everytime I insert text it overwrites my formula.

You can't have a formula in a cell, overwrite it with a date, and then expect it to still have the formula in there.

Without using another column to show whether the delivery is complete the way I would do it is to use conditional formatting to show which jobs are complete.

So do I just copy my formula into a conditional format? If so. I tried it but it doesn't work.

=not(islank(k2))

If you don't mind using a macro, put this in the sheet code. Right click the sheet tab, click view code & paste it there

Private Sub Worksheet_Change(ByVal Target As Range)
'   If a date is in J2 & K2 is blank, nothing happens
'   If the values in J2 & K2 are not dates, nothing happens
'   If a date is in J2 & K2, J2 value changes to Completed
'   if J2 contents is deleted, K2 clears
'   If K2 contents is deleted, J2 clears
'   Cell formatting is returned to General when cleared
    If Intersect(Target, Range("J2:k2")) Is Nothing Then Exit Sub
        If IsDate(Range("J2")) = True And IsDate(Range("K2")) = True Then
            Application.EnableEvents = False
            Range("J2").Value = "Completed"
        ElseIf IsEmpty(Range("K2")) And Range("J2").Value = "Completed" Then
            Range("J2").Value = vbNullString
            Range("J2:K2").NumberFormat = "General"
        ElseIf Not IsEmpty(Range("K2")) And Range("J2").Value = vbNullString Then
            Range("K2").Value = vbNullString
            Range("J2:K2").NumberFormat = "General"
        Else
    End If
            Application.EnableEvents = True
End Sub

Cheers
Phil

gak,

I am assuming you meant =not(isblank(k2)) I copied that into conditional formatting but it had no effect.


Philb1, Sorry, but our system doesn't allow macros.

Where do I place that formula? In the conditional formatting or in J2 or K2. I tried placing it in Conditional formatting and it didn't do anything. I also tried placing it in the cells, but no luck

[QUOTEPhilb1, Sorry, but our system doesn't allow macros.[/QUOTE]

In that case you'll have to use a different cell, as gak67 says, you can't enter manually into a cell with a formula, without deleting the formula. Or use conditional formatting to change the colour of the cell if it contains a date
What about Data Validation? If you restrict J2 to be a date the same as K2, greater than K2, not equal to K2, or whatever to K2, & Conditional Format it to change colour if the condition is met. That way you can't enter anything that doesn't meet the Data Validation condition of being a date & J2 will change colour when you do.
Cheers
Phil

Thank you for your help. I really appreciated your time and effort. It seems no matter how much I study and learn, I get on this forum and I feel dumb again..Some true experts on here.