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.

I found a way . [I hope this helps](https://stackoverflow.com/questions/65447034/is-there-any-excel-formula-to-record-cell-last-change-date) — Erfan Gholami, Dec 26 '20 at 08:00

score 9 · Answer 1 · edited Oct 19 '21 at 13:40

9

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.

edited Oct 19 '21 at 13:40

Stephen Turner

7,125
4
51
68

answered Jan 12 '15 at 14:35

Drocs

91
1
3

Can you explain why the iteration value should be 1000. Will it not work for more than 1000 entries or something? – Ashish Sharma Oct 09 '20 at 11:22
Note: I checked it with iteration value 3 also and it still works. So I will need explanation for why we are putting iteration value to 1000. – Ashish Sharma Oct 09 '20 at 11:29
@XeeMezAsHu I added a note about the iterations setting. – Stephen Turner Oct 19 '21 at 13:41

score 6 · Answer 2 · answered Jun 11 '14 at 19:14

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

I know this is a few years old, but this vba code was exactly what i was looking for, with the exception of one modification. I only made the modification to prevent the code from making two checks upon every **worksheet_change**. Now the code will only look for an existing date when column 2 is modified, using this code: `If Target.Column = 2 Then If Target.Offset(0, 3).Value = "" Then Target.Offset(0, 3) = Now() End If End If` — famousKaneis, Apr 17 '17 at 21:43

score 2 · Answer 3 · answered Jun 30 '17 at 17:41

Not sure if this works for cells with functions but I found this code elsewhere for single cell entries and modified it for my use. If done properly, you do not need to worry about entering a function in a cell or the file changing the dates to that day's date every time it is opened.

open Excel
press "Alt+F11"
Double-click on the worksheet that you want to apply the change to (listed on the left)
copy/paste the code below
adjust the Range(:) input to correspond to the column you will update
adjust the Offset(0,_) input to correspond to the column where you would like the date displayed (in the version below I am making updates to column D and I want the date displayed in column F, hence the input entry of "2" for 2 columns over from column D)
hit save
repeat steps above if there are other worksheets in your workbook that need the same code
you may have to change the number format of the column displaying the date to "General" and increase the column's width if it is displaying "####" after you make an updated entry

Copy/Paste Code below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
Target.Offset(0, 2) = Date

End Sub

Good luck...

score 0 · Answer 4 · answered Jun 11 '14 at 19:11

I'm afraid there is not such a function. You'll need a macro to acomplish this task.

You could do something like this in column E(remember to set custom format "dd/mm/yyyy hh:mm"):

=If(B1="";"";Now())

But it will change value everytime file opens.

You'll need save the value via macro.

score 0 · Answer 5 · answered Jun 21 '16 at 06:01

0

You can use If function Write in the cell where you want to input the date the following formula: =IF(MODIFIED-CELLNUMBER<>"",IF(CELLNUMBER-WHERE-TO-INPUT-DATE="",NOW(),CELLNUMBER-WHERE-TO-INPUT-DATE),"")

answered Jun 21 '16 at 06:01

user6492413

1

score -1 · Answer 6 · answered Nov 14 '19 at 15:18

-1

Here is the solution that worked for me

=IF(H14<>"",NOW(),"")

answered Nov 14 '19 at 15:18

Faisal Ansari

352
2
5

This formula will update the value to current time upon excel loading which will make it unsuitable for recording time. – Ashish Sharma Oct 09 '20 at 11:19

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

6 Answers6

Linked