4

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.

enter image description here

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?

9

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.

  • I get the following error: TypeError: Cannot read property "range" from undefined. (line 22, file "Code"). Any clues why? FYI: I have already a script running on the startup who's task is to take me to the last edited cell, where should I place the new code snipppet – Kristof Pal Jan 9 '16 at 21:03
  • Did you try to run the script manually? It's not meant for that; the onEdit name means it's run automatically when an edit is done. – user79865 Jan 9 '16 at 21:04
  • Although the error appears, the code works as expected. It would be great if it could do the reverse as well: When deleting an entry from D removing content from corresponding C – Kristof Pal Jan 9 '16 at 21:05
  • I added support for deletion. Still don't know why you're getting an error; it may be some interference of script functions? Function are placed one after another in the script file: function onOpen() ... { } function onEdit()... { } – user79865 Jan 9 '16 at 21:15
  • Great! pity I can not upvote 10x :)... I'll ignore the error for now. – Kristof Pal Jan 9 '16 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.