Automatic timestamp when a cell is full

Question

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

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

His pass / fail check sheet, and he adds a timestamp when someone passes or fails the test. We have added a few more people, and I want to move the document to Google applications in order to work with more than one person at a time.

The only problem I came across is the circular link that causes this. In excel, I can limit # iterations in options, I no longer have this ability. Any help would be great.

EDIT: what I tried. I tried to find a VBA script input method that a colleague created that would work for me. I am not good at scripting, so I cannot do this in google script applications:

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 with a timestamp on it, and then try to "paste special" and just paste the value into the desired cell. This will work, except that I could not find a way to insert a special Google application script.

Thanks for any help / Edit

Answer 1

Stackoverflow is a place where you can ask questions related to programming, for example. what you really work. In fact, you are not asking others to develop it for you, i.e. You have not tried to use any Script application code yet. I recommend that you read his manuals and manuals . It is really easy to get started.

In any case, to help you get started, I will drop everything you said and stick to the question heading: "Automatic timestamp when cell is full"

I advise you to do all this in script applications and completely abandon your formulas, for example.

 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, namely: if something is edited in column D and column E empty, add the current date to E

Answer 2

Just adding the FOR Multi Column AutoStamp code to the same sheet to the above

 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

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

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

By the way, how can I format the date in i.e. dd / mm / yyyy instead of the standard format dd / mm / yyyy hh: mm: ss

Answer 4

Actually, in this case you do not need a script of anything. Google (or someone) has already done this. In your Google spreadsheet, go to "Paste → Script" and search for "time." There are two ready-made scripts that will do what you want. I found that "Last Last Modified Date" works fine. Select it and click "Install." You can reformat the column to display date, date + time, etc. You can also transfer the date in a column or move them from another column if you tracked it before and they will remain as they are set. But updating any cell in the row will update the timestamp.

Answer 5

I set a timestamp to enable HH: MM: SS, but after testing the stamp 4 times in a minute I get: 03,14,11,07 fluctuate like MM in my timestamp.

Answer 6

it is much easier! = Now

or

= today

Depending on what you need