Excel Formula which places date/time in cell when

问题:

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.

回答1:

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.

回答2:

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

回答3:

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

回答4:

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.

回答5:

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

回答6:

Here is the solution that worked for me

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