Automatic timestamp when a cell is filled out

Question

I have an excel formula that is very simple and it works because I can restrict the recursive iterations. I am not very script savvy, but this is what it is and it works.

=IF(D24="P",IF(E24="",DateStamp,E24),IF(D24="F",IF(E24="",DateStamp,E24),""))

Its a pass/fail testing sheet and it adds a timestamp when someone passes or fails the test. We've added a few more people and I want to move the document to google apps to allow more than 1 person to work on it at the same time.

The only issue i've come in is the circular reference that this causes. In excel I can limit the # of iterations in the options, I dont have this ability anymore. Any help would be great.

EDIT: What I've tried. I've tried to find a way to input a VBA Script that a coworker created that would work for me. I'm not good with scripting so I'm unable to make this into a google apps script:

VBA SCRIPT:

    Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 Then
If Cells(Target.Row, 5).Value = "" Then

Cells(Target.Row, 5).Value = Now
End If
Else
End If


End Sub

In theory I tried to create a script that will copy a cell that has a timestamp on it and then try to 'paste special' and just paste the value into the cell needed. This would work except I was unable to find a way to paste special with the google apps scripting.

Thanks for any help /edit

Answer 1

Stackoverflow is a place to ask questions related to programming, e.g. that you're actually working on. Not really asking for others to develop it for you, i.e. you didn't even started trying any Apps Script code yet. I recommend you reading its tutorials and guides. It's really easy to start.

Anyway, just to help you get started, I'll drop everything you said and stick to the question title: "automatic timestamp when a cell is filled out"

I advise you to do it all on apps script, and drop your formulas entirely, e.g.

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 4 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setValue(new Date());
    }
  }
}

This code does what I understood from yours, which is: if something is edited on column D and column E is empty, add the current date to E.

Answer 2

Just addition to above code FOR Multi Column AutoStamp in Same Sheet

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 5 ) { //checks the column
      var nextCell = r.offset(0, 1);
      //if( nextCell.getValue() !== '' ) //is empty?
      nextCell.setValue(new Date());
    }

    if( r.getColumn() == 7 ) { //checks the column
      var nextCell = r.offset(0, 1);
      //if( nextCell.getValue() !== '' ) //is empty?
      nextCell.setValue(new Date());
    }

    if( r.getColumn() == 9 ) { //checks the column
      var nextCell = r.offset(0, 1);
      //if( nextCell.getValue() !== '' ) //is empty?
      nextCell.setValue(new Date());
    }
  }
}

Answer 3

You just need to use Apps Script. I'll explain using an example:

function onEdit(e) {
var row = e.range.getRow();
if (row > 1 && e.source.getActiveSheet().getName() === "Sheet1") {
    e.source.getActiveSheet().getRange(row, 14).setValue(new Date());
} else {
    if ((row > 1 && e.source.getActiveSheet().getName() === "Sheet2") || (row > 1 && e.source.getActiveSheet().getName() === "Sheet3")) {
        e.source.getActiveSheet().getRange(row, 6).setValue(new Date());
    }}}

This first of all check which sheet is being edited. If sheet1, then it takes the 14th column of all rows (getRange(row,14)) in that sheet & whenever anything is edited (edit(e)), it adds timestamp (setValue(new Date())) in that 14th column. Similarly, if it's a different sheet,i.e., Sheet2 or Sheet3 or any other name, we can select different columns for timestamp as per requirement. Also, if( (row > 1) condition has been added so that timestamp does NOT get added in the first row upon edit as it's usually headings.

Now you select to get a range of cells & use them as per requirement. See this question for a better idea on getRange().

Answer 4

and if you want it to update if the cell is changed again just delete this line

if( nextCell.getValue() !== '' ) //is empty?

By the way, how can the date be formatted to ie. dd/mm/yyyy instead of the default dd/mm/yyyy hh:MM:ss format

Answer 5

Actually, in this case you don't have to script anything. Google (or someone) has done it already. In your Google spreadsheet, go to "Insert -> Script" and search on "time". There are two ready-made scripts which will do what you want. I found "Cell Last Modified Date" works perfectly. Select it and click the "Install" button. You can reformat the column to show date, date+time, and so on. You can also hand code a date in the column, or move them from another column if you were tracking it before, and they will stay as you set them. But updating any cell in the row will update the timestamp.

Answer 6

I set the timestamp to include HH:MM:SS but upon testing the stamp 4 times in under a minute I get: 03,14,11,07 fluctuate as the MM in my timestamp.

Answer 7

it's much easier than that! =now

or;

=today

Depending what you need