当数据输入到同一行的另一个单元格时，Excel 公式将日期/时间放在单元格中 [英] Excel Formula which places date/time in cell when data is entered in another cell in the same row

问题描述

希望有一种方法可以通过公式来完成，因为我将把它作为共享工作簿放在 SharePoint 上.

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

B 列包含任务，而 E 列包含分配任务的日期和时间.是否有一个公式可以在有人在 B 列中输入数据时自动在 E 列中输入当前日期和时间?

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.

推荐答案

另一种方法如下所述.

首先，在文件-选项-公式-启用迭代计算下打开迭代计算.然后将最大迭代次数设置为 1000.

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

1000 次迭代对于这个公式无关紧要，但它阻止了 excel 陷入其他循环引用的无限循环.

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

一旦在单元格 D55 中输入任何内容(对于本示例)，C55 就会根据单元格格式填充今天的日期和/或时间.即使在单元格 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.

这篇关于当数据输入到同一行的另一个单元格时，Excel 公式将日期/时间放在单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！