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

Question

Since I'll be posting this as a shared workbook on SharePoint, I'm hoping there's a method to do this using a formula.

Tasks are listed in Column B, and the Assignment Date and Time are listed in Column E. Is there a formula that, each time data is entered into column B, would automatically enter the current date and time in column E?

Answer 1

Here's how to accomplish things in another method. Iterative calculations should first be enabled by going to File - Options - Formulas - Enable Iterative Calculation. set the maximum number of iterations to 1000.

Although the 1000 iterations are not necessary for this calculation, they do prevent Excel from becoming trapped in an endless loop when using other circular references.

Use the following formula after completing this.

=If(D55="","",IF(C55="",NOW(),C55))

When text is entered into cell D55 (in this example), cell C55 populates with the current date and/or time, depending on the format of the cell. This date and time will remain the same even if fresh information is added to cell C55, so it displays the time and date at which the data was initially entered.

Every time you open the worksheet, you will be warned that this formula uses a circular reference, so be aware of that. Whatever the case, the formula is accurate and simple to use anywhere in the worksheet.