CODEWITHSUNDEEP
excelexcel-formulaexcel-2007
IndyMom83
IndyMom83

Reputation: 153

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

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.

Upvotes: 12

Views: 159274

Answers (6)

Drocs
Drocs

Reputation: 91

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.

Upvotes: 9

Faisal Ansari
Faisal Ansari

Reputation: 362

Here is the solution that worked for me 

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

Upvotes: -1

Nick
Nick

Reputation: 21

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...

Upvotes: 2

user6492413
user6492413

Reputation: 1

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),"")

Upvotes: 0

StephenH
StephenH

Reputation: 1126

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

Upvotes: 6

Horaciux
Horaciux

Reputation: 6477

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.

Upvotes: 0

Related Questions