6

I am creating a spreadsheet which will contain records of my daily expenditure. Something like the following:

A  | B
----------
0  | 50.04
0  | 20.00
0  | 19.99
0  | 2.40

What I am trying to achieve is each time I insert data into a cell in the B-Column, the current date is inserted into the A-Column on the right. This way I only have to manually input data into one cell per entry.

So, if I spent the amounts above on separate days, the dates in the A-Column will indicate those dates.

  • Possible duplicate of: webapps.stackexchange.com/questions/37408/… – OnenOnlyWalter Jan 16 '13 at 20:22
  • This question is about the entry date. The possible duplicate is about the last change date. – Jacob Jan Tuinstra Jan 16 '13 at 20:33
  • 1
    The answer could be tweaked to record the timestamp on entry - it assumes that the amount would not be edited after being initially recorded, but I see your point :) – OnenOnlyWalter Jan 16 '13 at 20:40
  • I have tried the possible duplicate suggested by OnenOnlyWalter. It does work however the script works across all my sheets instead of a select few. Also, in the relevant sheets, editing "ANY" cell inserts a date. Sorry but as I am totally new to this, I don't know how to fine-tune the code to better control it – sisko Jan 16 '13 at 23:05
4

This answer is coming late to the party, but you can achieve what you want by installing this script:

function onEdit(event) {
  var eventRange = event.range;
  if (eventRange.getColumn() == 4) { // 4 == column D
    var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 1, eventRange.getNumRows(), 1);
    var values = columnARange.getValues();
    for (var i = 0; i < values.length; i++) {
       values[i][0] = new Date();
    }
    columnARange.setValues(values);
  }
}

EDIT: It seems you just want to trigger the script when column D is modified, so I have modified the code accordingly.

To install it, go to Tools -> Script editor, paste the code above, and save. From then on, every time you edit a cell in column D, the current timestamp will be inserted in column A.

4

I made an adjustment to Vidar's script for anyone who only wants the autodate to work once (the first time a cell is changed). This may be helpful in a timesheet where users sometimes go back to add hours in the past and won't want the date updated to the current time.

Note: For my version, I changed the range to column B, rather than D.

 //------------------------------------------------------------
 //Auto-Populate date in Column A of when column B is updated 
 //Edited 01/13/16 - MK
 //------------------------------------------------------------

function onEdit(event) {
  var eventRange = event.range;
  if (eventRange.getColumn() == 2) { // 2 == column B

    // getRange(row, column, numRows, numColumns)
    var columnXRange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 1, eventRange.getNumRows(), 1);

    var values = columnXRange.getValues();

    for (var i = 0; i < values.length; i++) {
      if (!values[i][0]) {  // If cell isn't empty
       values[i][0] = new Date();
      }
    }
    columnXRange.setValues(values);  
  }
}
1

You can use a Google Form for that:

See example I've prepared:

  • Thank you for your input. I can see your point but I am looking for a solution which means I am editing in one place rather than two. – sisko Jan 16 '13 at 23:07

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