0

I need my team to enter their names in Column A, LOGIN or LOGOUT in Column B, and I need the login timestamp in Column C and logout timestamp in Column D. I need this repeated for 30 days (a month) in every sheet, which means that Columns B, C, and D will repeat 30 times across the sheet.

Name - LOGIN/LOGOUT - LOGIN-Timestamp - LOGOUT-Timestamp

I found this code in YouTube (slightly tweaked), which creates two timestamps in 2nd and 3rd columns, where the second one updates to a new time, when column 1 is updated, while the first remains unchanged. I planned to use this by hiding the second timestamp (using conditional formatting) when the user enters LOGIN, and then unhide it when the user enters LOGOUT. However, I do not know how to have this script called to every 4th column for subsequent days. I have spent nearly the entire day figuring this out, but my script know-how is extremely limited. Any help is greatly appreciated. Many thanks.

function onEdit(e) {

  var row = e.range.getRow();
  var col = e.range.getColumn();

  if(col === 1 && row >= 1 && e.source.getActiveSheet().getName() === "Test"){

  var currentDate = new Date();
  e.source.getActiveSheet().getRange(row,3).setValue(currentDate);
  if(e.source.getActiveSheet().getRange(row,2).getValue() == ""){
  e.source.getActiveSheet().getRange(row,2).setValue(currentDate);
  }
 }
}
player0
  • 124,011
  • 12
  • 67
  • 124
ANIPON
  • 1
  • 3
  • According to your example, looks like the trigger column is "LOGIN/LOGOUT", which is the _second_ column (not column 1) and so the timestamps would be in columns 3 & 4. Can you please clarify? – Diego Mar 31 '20 at 13:35
  • Is it always column 2 that is being edited when a user 'logs in'/'logs out'? – Rafa Guillermo Mar 31 '20 at 13:40
  • Yes. The first input in column 2 would add time timestamps in both 3 and 4. Any update in column 2 would update the timestamp in 4. – ANIPON Mar 31 '20 at 13:42
  • @ANIPON So you just want a different action to be taken if Column 4 is edited? – Diego Mar 31 '20 at 13:42
  • I want timestamps in 3 and 4. 3 must receive timestamp when 2 gets LOGIN and 4 must get timestamp when 2 gets LOGOUT. Since I do not know scripting, I was trying to use what I found online. Thanks. – ANIPON Mar 31 '20 at 13:47

1 Answers1

1

Answer:

  1. Get the cell that has been edited.
  2. Check if the cell's column is in a Login/Logout column with the % operator.
  3. If the cell to the right of the edited cell is blank, input the date. Else, input the date two cells to the right.

Code:

function onEdit(e) {  
  var row = e.range.getRow();
  var col = e.range.getColumn();  
  var curRow = e.source.getActiveSheet().getRange(row + ':' + row).getValues();

  var sheetNames = ['Put', 'your', 'sheet', 'names', 'here', '...', 'etc']

  if (!sheetNames.includes(e.source.getActiveSheet().getName())) {
    return;
  }
    
  if((col + 1) % 3 === 0 && row >= 1) {
    var currentDate = new Date();
    if (e.source.getActiveSheet().getRange(row, col + 1).isBlank()) {
      e.source.getActiveSheet().getRange(row, col + 1).setValue(currentDate);
    }
    else {
      e.source.getActiveSheet().getRange(row, col + 2).setValue(currentDate);
    }
  }
}

Reference:

Zoe
  • 27,060
  • 21
  • 118
  • 148
Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54
  • Thanks Rafa. It still works only for the first set of columns. For example, when add input values in Col 2, a timestamp appears in 3, and when I change that value, a timestamp appears in 4. But when I add a input value in col 5, timestamps do not appear in 6 and 7. I am sorry, am I not doing something right. – ANIPON Mar 31 '20 at 14:46
  • That's strange, have you copy + pasted the new script and saved? It should automatically find the next free column and update it. Do you happen to have any installable triggers? Or perhaps two `onEdit()` functions? – Rafa Guillermo Mar 31 '20 at 14:48
  • No, this is a fresh sheet, no other scripts yet. I will try once more just to be sure. Thanks. – ANIPON Mar 31 '20 at 14:49
  • No, it still does not work. Meanwhile I found this other code by Vinit that works in multiple columns just the way I want, but it only adds one timestamp per edit, not two. https://stackoverflow.com/questions/11458470/automatic-timestamp-when-a-cell-is-filled-out – ANIPON Mar 31 '20 at 14:53