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
Insert Date and Timestamp Using NOW FunctionRight-click on the cell and select 'Format cells'. In the Format Cells dialog box, select 'Custom' category in the Number tab. In the Type field, enter dd-mm-yyyy hh:mm:ss. Click OK.
First of all, select the cell where you need to insert a timestamp. After that, use the shortcut key Control + : (Press and hold control and then press colon). Once you press this, it will insert the current date (according to your system) in the cell.
You can use the SEQUENCE function to auto-populate dates without having to click and drag your mouse. The structure for this is =SEQUENCE(rows, [columns], [start], [step]). This function allows you to specify the number of rows and columns for your dates, the start date and the numbered steps for the series.
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
.
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());
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With