Insert current date to cell based on content in other cell

Question

Background:

I am trying to create a sheet which tracks my costs on daily basis. Instead of entering the current date everyday, I want the sheet to store the current day based on the content I enter in the Costs column.

For the cells in column C I use the following formula:

=if(D2="","empty",TODAY())

The problem with this function is that it updates everyday. As you can see from the picture the date on C appears as the most current date although the information there was inserted days ago.

Question:

How do I achieve that the content in the cells of column C stay only on the date of insertion content to column D and they do not update everyday to the current date?

Answer 1

This is related to Is there any way to create a timestamp in a Google Spreadsheet? so my answer is similar to those, but adapted to your setup.

Automatic insertion: requires Apps Script

Use the following script (Tools > Script Editor):

function onEdit(e) {
  if (e.range.getSheet().getSheetName() == 'Sheet1' && e.range.getColumn() == 4) {
    e.range.offset(0,-1).setValue(e.value.length>0 ? new Date() : '');
  }
}

On every edit, the script checks whether the edit was to "Sheet1" (put the name of the relevant sheet here) and to the column 4 (that is D). If so, it fills the cell directly to the left (offset(0,-1)) with the current date. Unless the edit removed content from column D; then the corresponding C cell becomes empty.

Manual insertion: no script

Make the habit of pressing Ctrl+; in column C when editing column D. This inserts the current date.