Excel Formula which places date/time in cell when data is entered in another cell in the same row

Question

Hoping there is a way this can be done with a formula since I will be putting this on SharePoint as a shared workbook.

Column B contains Tasks, while Column E contains the Date and Time of when the Task was assigned. Is there a formula that would automatically enter the current date and time in Column E whenever someone entered data into column B?

Any assistance would be greatly appreciated.

Answer 1

This can be accomplished with a simple VBA function. Excel has support for a Worksheet Change Sub which can be programmed to put a date in a related column every time it fires.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Offset(0, 3).Value = "" Then
        Target.Offset(0, 3) = Format(Now(), "HH:MM:SS")
    End If
End Sub

A quick explanation. The following "if" statement checks for two things: (1) if it is the second column that changed (Column B), and (2) if the cell 3 columns over (Column E) is currently empty.

If Target.Column = 2 And Target.Offset(0, 3).Value = "" Then

If both conditions are true, then it puts the date into the cell in Column E with the NOW() function.

Target.Offset(0, 3) = Format(Now(), "HH:MM:SS")

Range.Offset

Range.Column

Answer 2

Another way to do this is described below.

First, turn on iterative calculations on under File - Options - Formulas - Enable Iterative Calculation. Then set maximum iterations to 1000.

The 1000 iterations doesn't matter for this formula, but it stops excel getting stuck in an infinite loop for other circular references.

After doing this, use the following formula.

=If(D55="","",IF(C55="",NOW(),C55))

Once anything is typed into cell D55 (for this example) then C55 populates today's date and/or time depending on the cell format. This date/time will not change again even if new data is entered into cell C55 so it shows the date/time that the data was entered originally.

This is a circular reference formula so you will get a warning about it every time you open the workbook. Regardless, the formula works and is easy to use anywhere you would like in the worksheet.