Generate Automatic Date in Google Sheets (3 Easy Ways)

In this tutorial, we will show you how to insert or generate an automatic date in Google Sheets. While the task itself is quite simple, it is the attached conditions that we will focus on most and the scenarios where they may be useful.

Let’s get started.

Table of Contents

3 Ways to Generate Automatic Dates in Google Sheets

1. Insert Dates Automatically Using Functions in Google Sheets

I. Insert Date Automatically Regardless of Format in Google Sheets

One of the common issues that many Google Sheets users face is a wrong date format creating miscalculations in a worksheet.

While it is not a critical issue and can be easily overcome by using one of the many date formatting methods in Google Sheets, it can build up to be an annoyance as spreadsheets are constantly exchanged between users with different regional settings.

A solid way to insert a date in Google Sheets that automatically updates to the current date format is by using the DATE function.

DATE(year, month, day)

The DATE function takes three different arguments of Year, Month, and Day. Filling up these three fields will give us the date in the default date format of the spreadsheet.

The DATE function arguments can also be retrieved from different cells as cell references:

In the image above, we have auto-filled the dates down the Date column, each time retrieving the data from the adjacent rows.

But that brings us to the only disadvantage of the DATE function. You cannot automatically generate or autofill sequential dates with the DATE function.

Reason: The DATE function is dependent on its argument values, which do not change when we use the fill handle down a column or row.

So, if you are looking for a way to autofill a series of dates, please check out this article: How to Autofill Date in Google Sheets (A Comprehensive Guide)

II. Using TODAY and NOW to Automatically Generate Current Date

Using TODAY and NOW to Automatically Generate Current Date

We have two more functions in Google Sheets that can automatically insert and update dates in a cell. These are the TODAY and NOW functions.

They are quite similar to each other:

They are both “running” functions. This means that they update every time a change is made in the worksheet. This makes them a dynamic option when used as timestamps.
They require no arguments.

The only difference is in their results:

The TODAY function outputs only the current date.

The NOW function outputs both the current date and time.

As we’ve mentioned before, the primary use of these functions is as timestamps as they automatically update the date and time with any change to the worksheet:

A new data is entered every two seconds and we can see that from the NOW function timestamp.

If any changes were made on another date, the date values of both timestamps would have changed as well.

2. Insert Dates Automatically Using Keyboard Shortcuts

Keyboard shortcuts have always been a handy way to operate spreadsheets. The same is also true when it comes to automatically inserting dates and timestamps in Google Sheets.

The first shortcut we have is:

CTRL+; (Hold the CTRL key and press the ; key of the keyboard)

This shortcut automatically generates the current date.

The second shortcut we have is:

CTRL+SHIFT+; (Hold CTRL and SHIFT keys together then press the ; key of the keyboard)

This shortcut automatically generates the current time.

Note: For macOS, simply replace the CTRL key with the CMD key.

A crucial point to keep in mind: These are static dates and times. Meaning that they don’t automatically update when cell values are changed. You will have to apply the shortcut again manually.

This is unlike the TODAY and NOW functions as we’ve discussed previously, where the values update with any change. With keyboard shortcuts, while the implementation is somewhat manual, the user has more control.

Speaking of the NOW function, you can combine both of the shortcuts mentioned above to replicate the output format of the NOW function:

3. Using Apps Script to Generate Automatic Dates when a Cell is Updated in Google Sheets

Perhaps the best way to achieve full control of a Google Sheets spreadsheet to get customized results is by using Apps Script.

This feature will allow any user to create a function catered to their specific requirements to give the desired output.

For us in this article, we will use Apps Script to generate an automatic date or timestamp in Google Sheets. Let’s see how to do that step by step.

As for our example, we have the following incomplete dataset which we will be populating:

The objective is to generate a timestamp in the last modified column every time data is entered or updated in the row.

Step 1: Open the Apps Script window. You can find it under the Extensions tab.

Extensions > Apps Script

Step 2: In the Apps Script window, make sure to name your project. We have named ours ‘Last Modified’ after the column where the timestamp will be generated.

Step 3: Apply the following script:

function onEdit(e) {
  
  var range = e.range;
  var spreadSheet = e.source;
  var sheetName = spreadSheet.getActiveSheet().getName();
  var row = range.getRow();  
  
  if(sheetName == 'Data')
  {
    var new_date = new Date();
    spreadSheet.getActiveSheet().getRange(row,3).setValue(new_date).setNumberFormat("MM/dd/yyyy hh:mm:ss"); 
  }
  
}

Make sure that the worksheet name and the output column number are correct. Our worksheet name is Data and the Last Modified column is column C or column 3 (line 11, getRange(row,3)).

We have also set an output date-time format. You can also use “MM/dd/yyyy hh:mm:ss A/P” for the 12-hour clock format.

Step 4: Click Save project and Run to apply and execute the script.

For more conditions and customizations, like only updating when a specific column is changed or getting only the timestamp when a new data is entered, please see: Apply an Automatic Timestamp Using Google Sheets Script (3 Easy Ways)

Final Words

There are multiple approaches that one can use to generate an automatic date in Google Sheets, from keyboard shortcuts, built-in functions, and even creating your very own script function.

Feel free to leave any queries or advice you might have in the comments section below. Or have a look at some of our other date-related articles that are sure to interest you.