Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets

You’ve probably come across the problem where you need to know when a piece of data has been added to your spreadsheet. You probably have been equally frustrated that there is no out-of-the-box function that will do just this.

You’ve tried TODAY() and NOW(), but they change dynamically. What you really need here is something that does not change.

Let’s look at two workarounds that can help you out with this problem.

1. The Simple, But Not So Useful Approach

This approach does not automatically update the sheet with the current date and/or time, but it is a fast solution to adding the current date and time to the sheet automatically.

Date

To add the current date to a cell, simply press <CTRL>+<;>

Time

To add the current time to a cell, press <CTRL>+<SHIFT>+<;> (This is shift and semi-colon is essentially a colon (:).

Like I said, quick but not exactly what you came here for.

2. The Harder, But Exceedingly More Useful Approach

This approach requires the use of Google Apps Script, a built-in scripting program for the Google Suite. You can access it by clicking on the <Tools> menu and then <Script Editor>.

How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script

Bare Bones Basics for the Non-Coder

Simply delete everything in the Code.gs file that first appears. Then paste in the following.

Then save the document. A popup window will appear asking you to name your project. I usually name it the same as my spreadsheet, but you can call it what you want.  For this tutorial, however, I’ll call the project: DateTimeStamp.

Date Time Stamp - Google Apps Script with Google Sheets

There are only 3 things you need to modify to make this script work how you want and where you want. They can be found at the top of the code and are documented fairly clearly.

COLUMNTOCHECK

This is the column that you will add data in that will cause Google Apps Script to put the date and/or time in another column of your choosing.

Here is an example: I want to create a sheet recording the date I took my blood pressure.

In column A, I want the current date to appear when I add my blood pressure to column B.

My Blood Pressure Sheet

 

In Google Apps Script, each column is represented by a number, starting at 1 (One). In my example Column 2 is he one we will add data to so we will need to change this in the code by changing the value of the COLUMNTOCHECK.

DATETIMELOCATION

The next thing we need to look at is where we want to put the date. In our example we want to put it in Column 1 when something is added to Column 2. To do this we modify the DATETIMELOCATION variable.  This variable has two sets of numbers. The first is a row number and the second is the column so the variable could look like this:

var DATETIMELOCATION = [row number, column number];

These row and column numbers are OFFSET from the cell that we enter information into. So, for example if we enter something into the cell of B3 (column = 2, row = 3 in Google Apps Script) and our offset is something like [0,1], then the Date and/or Time will stay on the same row and move to the right 1 column to C3.

Let’s look at our Blood Pressure example. In Column A we want the date, and in column B we enter our blood pressure. If I make my first blood pressure entry in cell B2 (column = 2, row = 2) then I expect the Date to automatically appear in A2.

So we are staying on the same row, so row = 0. But we are going back one column, so column = -1.

Offset - Google Apps Script

So we’ll go ahead and change the DATETIMELOCATION  on line 9 variable to:

var DATETIMELOCATION = [ 0,-1];

SHEETNAME

Finally we want to tell Google Apps Script what sheet we are working on. Right now in our script the SHEETNAME is equal to “Sheet1”, which is the standard sheet when you open a new spreadsheet. For our example, the name of our sheet is “BP” for Blood Pressure, of course.

Sheet name

 

Go ahead and change SHEETNAME to “BP”:

var SHEETNAME = "BP"

 

Okay, you are all done with Google Apps Script. You can go ahead and save and close the script tab. It will continue to run in the sheet and will run every time you open the sheet.

Check out the example below:

Current Date Time Stamp Google Sheets

 

Modifying the Date Type

You can now change the date information Date, Date and Time or just Time by selecting the column and then going to the <Format> menu and then chose one of the Date options. In my example, I want both the time and date. This is how I changed it:

Changing The Date Type in Google Sheets

 

 

33 thoughts on “Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets”

  1. Is there a way to adapt this for when a form is entered or information is submitted from a form? I tried it but it doesn’t add the date when information comes from the form into the Form Responses Tab.

    1. Hi Mario,

      Good question. Maybe look at changing the onEdit function to a general function that checks if the reference cell is not empty and the date cell does not already contain a date, then add today’s date. Then apply a time trigger to the function. Alternatively, you could change the above modified function to and onOpen() function that would update when the sheet is opened, should you not access the sheet regularly.
      The form responses should already come with a date-time stamp if you want to use that too. Just modify how you want them displayed by selecting the column and your preferred date type.

      Hope this helps. If you come up with something better. Please share.

  2. I’m really a script noob here, so… is there a way to make it only add a date if a specific word is in the column? Like it should add a date if there’s a ‘added’ in the column to check, but not if there’s a ‘missing’ in it.

    1. Hi Anne,

      Yes, you can create and extra ‘if’ statement once you get the cell value and if that cell value equals say, ‘added’ then the date will appear.

      /**
      * Creates a Date Stamp if a column is edited.
      */

      //CORE VARIABLES
      // The column you want to check if something is entered.
      var COLUMNTOCHECK = 1;
      // Where you want the date time stamp offset from the input location. [row, column]
      var DATETIMELOCATION = [0,1];
      // Sheet you are working on
      var SHEETNAME = ‘Sheet1’
      // Value condition.
      var VALUETOCHECK = “checked”;

      function onEdit(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      //checks that we’re on the correct sheet.
      if( sheet.getSheetName() == SHEETNAME ) {
      var selectedCell = ss.getActiveCell();
      //checks the column to ensure it is on the one we want to cause the date to appear.
      if( selectedCell.getColumn() == COLUMNTOCHECK) {
      if(selectedCell.getValue() == VALUETOCHECK){
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      }
      }
      }
      }

  3. Thanks a lot for this extremely useful tool. Is there anyway to run this on all sheets instead of only 1?
    I need to have one sheet for each month and i want the same time stamp for each month.
    Probably i can replicate the script with a different sheetname but maybe there is an easier way.
    Thanks.

    1. Hi Ers,

      Take a look at the code, you can see that one of the if statements checks to see if the sheet name matches. We can remove this if statement and then any sheet with the column selected can be uses.

      /**
      * Creates a Date Stamp if a column is edited.
      */

      //CORE VARIABLES
      // The column you want to check if something is entered.
      var COLUMNTOCHECK = 1;
      // Where you want the date time stamp offset from the input location. [row, column]
      var DATETIMELOCATION = [0,1];

      function onEdit(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      //checks that we’re on the correct sheet.
      var selectedCell = ss.getActiveCell();
      //checks the column to ensure it is on the one we want to cause the date to appear.
      if( selectedCell.getColumn() == COLUMNTOCHECK) {
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      };
      };

  4. Glad I found this tool, this is so helpful. Just wondering what to do if I want to put the Date stamp and Time stamp on separate columns. Thank you so much.

    1. Hi Riz,

      You could certainly duplicate line 21 and extend the line 9 array to add another offset location.

      Then all you would need to do is to format to date and time in Google Sheets.

      1. Hi Yagi, thanks for the quick reply. Could you please tell me how to do it exactly? Sorry, I just don’t have any idea. Thanks again.

        1. Hi Riz,
          You can add to this Array.
          // Where you want the date time stamp offset from the input location. [row, column]
          var DATETIMELOCATION = [0,1];
          for example :
          var DATETIMELOCATION = [0,1,0,2];

          Then add a line like:
          var dateCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          var timeCell = selectedCell.offset(DATETIMELOCATION[2],DATETIMELOCATION[3]);

          Then in your sheet, click in the column header you want the date. Then go to Format>Number>Date and then click on the next column, Format>Number>Time.

          Make an attempt at the code and if you have a problem feel free to post it.

          1. Hi Yagi,

            Should I put in the duplicate in line 22? It doesn’t work. Thanks again.

          2. Hi Riz,

            Yes. you will also need to setValue for the new duplicate variable. Copy and paste the code you are working on in the reply and I can take a look.

            Alternatively, you can simply reference the cell in another cell in Google Sheets. For example, if your date is in cell B2 then say, in cell C2, you could add the formula =B2. Then click and drag the little box on the bottom right of the cell and drag it all the way down. You can then go ahead and click Format>Numbers>Date or Time etc.

          3. Hi Yagi,
            So, I simply referenced the cell into another cell and it perfectly worked! Awesome! Thanks a lot!

  5. Thank you so much Yagi,… but I don’t know for sheet that has some column, and for workbook that has some sheet?
    I’m looking forward your hand…. Thank you…

    1. Sorry… to continue my question…,
      How to make script to set Time such as: dd-MM-yyyy HH:mm:ss,
      Ok, Yagi… I’m waiting your hand….please…

    2. Hi Sugianto,

      You could change the spreadsheet location using something like var targetSheet = SpreadsheetApp.getSheetByName("sheet name"). Have a try and post your attempt and I will take a look.

  6. I tried to make time stamp for several columns is like the below, but it couldn’t run, and also how about making time stamp for several sheet?
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 4;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,38];
    // Sheet you are working on
    var SHEETNAME = ‘PR’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 5;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,38];
    // Sheet you are working on
    var SHEETNAME = ‘PR’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 6;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,38];
    // Sheet you are working on
    var SHEETNAME = ‘PR’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 7;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,38];
    // Sheet you are working on
    var SHEETNAME = ‘PR’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }

    1. The variables “ss” is used to get the active spreadsheet and “sheet” is used to get the active sheet only need to be called once. These basically state, I want to work on this spreadheet (ss) and this specific sheet(sheet).
      Considering you are changing the COLUMNTOCHECK variable, I think you are trying to do the following:
      – if column 4, 5, 6 or 7 is edited, then add the date in column 38.
      Is this correct?

  7. Hi Yagi
    Q: if column 4, 5, 6 or 7 is edited, then add the date in column 38.
    Is this correct?
    A: Only column 4 and 5 can be edited and can appear time stamp in column38, but column 6 and 7, they cann’t. please your hand… Here the link of my google spreadsheet.

  8. Hi Yagi.. here link the sheet
    “https://docs.google.com/spreadsheets/d/19wfQZd9LLGYjtF5xQ19Whh0jratuiHgj5BVZhyTPvMY/edit?usp=sharing”

    1. Hi Sugianto,
      So it looks like you want to offset each time you add a value in the first half of the month or the second half of the month section.

      Why don’t you just change var DATETIMELOCATION to equal [0,38];
      And then change var COLUMNTOCHECK = 1; to something like var COLUMNSTOCHECK = [4,27];
      Then you can change the “if” statement on line 20 to something like this:
      if( selectedCell.getColumn() >= COLUMNSTOCHECK[0] && selectedCell.getColumn() < = COLUMNSTOCHECK[1]) { Play with this and see if you get the results you want.

      1. Wow..so quickly respon…thank you I Will try… actually I’m not programmer, so I sent you a link of my google sheet so that you can see my false of script… “https://docs.google.com/spreadsheets/d/19wfQZd9LLGYjtF5xQ19Whh0jratuiHgj5BVZhyTPvMY/edit?usp=sharing”

      2. Hello Yagi, I’ve tried to follow your advice, but it cann’t still run to make time stamp; and then I got a problem when the input cell is updated so the time stamp is not updated too… why? if I wanna updated the timestamp… what the script? for clearly you can see my sheet at the link: “https://docs.google.com/spreadsheets/d/19wfQZd9LLGYjtF5xQ19Whh0jratuiHgj5BVZhyTPvMY/edit?usp=sharing”
        ….. please your hand

        /**
        * Creates a Date Stamp if a column is edited.
        */

        //CORE VARIABLES
        // The column you want to check if something is entered.
        var COLUMNTOCHECK = [4,38];
        // Where you want the date time stamp offset from the input location. [row, column]
        var DATETIMELOCATION = [0,38];
        // Sheet you are working on
        var SHEETNAME = ‘PR’

        function onEdit(e) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        //checks that we’re on the correct sheet.
        if( sheet.getSheetName() == SHEETNAME ) {
        var selectedCell = ss.getActiveCell();
        //checks the column to ensure it is on the one we want to cause the date to appear.
        if( selectedCell.getColumn() >= COLUMNSTOCHECK[0] && selectedCell.getColumn() < = COLUMNSTOCHECK[1]) {
        var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
        dateTimeCell.setValue(new Date());
        }
        }
        }

    1. Hi Raul,

      I haven’t really set anything up like this yet, but thank you for your generous thoughts.

  9. Hello Yagi,
    Your Sample is for one column input and one time stamp column and one sheet… How about more than that, for example: more than one column input, more than one column timestamp and more than one sheet…

    1. Sure, you could look at ‘for’ loops, ‘if’ and ‘if else’ statements to achieve this. Give it a try, you’d be surprised what you can come up with.

Leave a Reply

Your email address will not be published. Required fields are marked *