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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Here is the solution that worked for me    IF H14 lt  gt    NOW

User · Answer

You can use If function Write in the cell where you want to input the date the following formula   IF MODIFIED-CELLNUMBER lt     IF CELLNUMBER-WHERE-TO-INPUT-DATE    NOW   CELLNUMBER-WHERE-TO-INPUT-DATE

User · Answer

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

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

Examples related to excel

Examples related to excel-formula

Examples related to excel-2007