2

I’ve created myself a budget tracker where I can track what I bought and how much I spent across a few categories. I specified column B (purchase entries are rows) to be the date and time at which I made a purchase using a conditional formula and today() that populates the cell with the date as for when an entry is made in that row.

I soon found out that today() auto refreshes when the worksheet is modified. Is there another way to write this to fit my needs for more of an automatic timestamp? I’m reluctant to hand jamming the date because I already am using Google sheets on my phone to enter most entries and it’s time-consuming enough.

  • -today cant be used for reasons you already found out, but you can use script that will auto stamp the row after you edit that row. – user0 Jan 14 at 3:50
0
  • add this script to your sheet:

    function onEdit() {
      var s = SpreadsheetApp.getActiveSheet();
      if( s.getName() == "Sheet1" ) {
        var r = s.getActiveCell();
        if( r.getColumn() == 1 ) {
          var nextCell = r.offset(0, 1);
          if( nextCell.getValue() === '' )
            nextCell.setValue(new Date(), "GMT+2", "MM/dd/yyyy hh:mm:ss");
        }
      }
    }

  • if( s.getName() == "Sheet1" ) { - change Sheet1 if your sheet has a different name
  • if( r.getColumn() == 1 ) { - change 1 if your non-date data are not in A column
  • var nextCell = r.offset(0, 1); - change 1 to move date stamp further than next column
  • nextCell.setValue(new Date() "GMT+2"); - change GMT+2 to mach your timezone

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.