Today() or No Change if cell is already a date

[tomc72]

COLUMN	F	G	H	I	J	K	L	M
HEADING	MATCH NO.	DATE	COMP. LEVEL	PLAYER	MATCH PTS	PLAYER Pts	COMPUTER Pts	WINNER
DATA	1		EXPERT	Tom	5	7	2	Tom

The above represents a section of a table that records a competitor's results against a computer.

I have successfuly managed to get all columns (except G) automatically filled once the scores in Col K and Col L are entered. But Col G is driving me mad!

1) I want the date to be Today() at the time that Col K and Col L are entered, BUT, if there is a already a date in the cell, I need that date to be left as it is.

2) I want to be able to put the formula into subsequent rows so that if Columns K and L are empty then the equivalent cell in Column G also remains empty.

I have tried many permutation of If, And, Or. IsBlank() and Isnumber(), but I cannot crack it. I thought I had, but then found that the following day the Today() overwrote earlier entries! Nightmare!

I would be so grateful for the solution, please!

Thank you...

[#2]

@tomc72 

There are two ways to do this:

1. Using iterative calculation.
2. Using VBA code.

For iterative calculation:

• Select File > Options.
• Select Formulas.
• Tick the check box 'Enable iterative calculation'. You have to do this only once.
• In G2, enter the formula =IF(AND(K2:L2<>""),IF(G2<>"",G2,TODAY()),"")
• Fill down.

For a VBA solution:

• 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 the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim r As Long
    If Not Intersect(Range("K2:L" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("K2:L" & Rows.Count), Target)
            r = rng.Row
            If Range("K" & r).Value <> "" And Range("L" & rng.Row).Value <> "" Then
                Range("G" & rng.Row).Value = Date
            Else
                Range("G" & rng.Row).ClearContents
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

[#3]

Hi Hans. I´ve yet to try the code solution, but sad to say that you formula leaves G2 blank. Sorry!
I´ll try the VBA, tomorrow!
Thank you,
Tom

[#4]

@tomc72 

The formula will enter a date in G2 if you enter something in both K2 and L2 (and if you have enabled iterative calculation)

See the attached demo workbook.

[#5 · Accepted solution]

Works like a dream. I had already allowed iterations but, and it´s a HUGE but, having copied your formula into my spreadsheet, I made an error with the row number! What a dummy!

Thank you so much for your help - and patience! :thumbs_up:☺