2

I have a Google Spreadsheet that I want to have a unique number created in column A when I enter information in column B. Below is the formula I currently have that works, but I am wondering if there is a way to edit it so that instead of CDI it has the date(datestamp)?

=ArrayFormula(if(len(B:B),"CDI"&text(row(A:A),"000000"),iferror(1/0)))

Desired end result would be something like:

02232015001

1

You could replace "CDI"&text(row(A:A),"000000") with

text(month(now()),"00")&day(now())&year(now())&text(row(A:A),"000")

but this would be the timestamp of now, not of when the cell was last updated.

To capture the update time, you need a script. The script below runs every time the spreadsheet is edited, checks whether there are entries in column B without an entry in column C, and adds a timestamp-based ID to column C in this case.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell;
  var range = sheet.getRange("B:C");
  var values = range.getValues(); 
  for (var i=0; i<values.length; i++) {
    if (values[i][0] && !values[i][1]) {
      cell = range.getCell(i+1, 2);
      cell.setValue(formatDate()+('00'+cell.getRow()).slice(-3));
    }
  } 
}

function formatDate() {
  var month, day, d = new Date();
  month = ('0'+(d.getUTCMonth()+1)).slice(-2);
  day = ('0'+d.getUTCDate()).slice(-2);
  return d.getUTCFullYear()+month+day;
} 

The result looks like this:

+-----------+-------------+
|   Data    |  Timestamp  |
+-----------+-------------+
| something | 20150629002 |
|           |             |
| more      | 20150629004 |
| and more  | 20150629005 |
+-----------+-------------+

I put the year first, because otherwise the leading zero in the month would be omitted, decreasing readability. (The alternative, formatting ID as a string, is somehow unappealing).

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.