How to add current date and time in Google Sheets

Adding the current date and time in your Google Sheet can be very helpful for several reasons. For instance, it can help you timestamp activities assigned or done. There are multiple ways to add the current date and time in your Google Sheet.

3 methods to add current date and time in Google Sheets

  • Using a formula
  • Using keyboard shortcuts
  • Using a script

Method 1: Using a formula

Google Sheets provide you with a couple of basic formulas to extract the date and time. Note that these formulae recalculate the time and update it. This means every time you refresh or make changes in your sheet, Google Sheets will update the time.

Step 1: Choose a cell to insert the date and time.

You can choose a cell by clicking the cell. This would highlight the cell with a blue border. You can also see the name of the cell selected in the Name box at the top-right corner, just above the column names. For instance, the cell selected here is A1.

Step 2: Type the formula.

The formula to get the current date and time is =NOW(). To just get the date, you can use the formula = TODAY(). Type the formula you’d like in the chosen cell.

Step 3: Press Enter.

After you type the formula, press Enter to see the formula applied to your cell. You can see the current date and time in your cell now.

Step 4: Formatting the date and time.

If you like how the date and time looks, you can skip this step. However, if you want the current date and time in a different format, you can follow this step.

Highlight the cell by clicking on it, like the first step of this method. In the main menu bar, select Format and hover over Number.

This would open another dropdown. Scroll all the way to the bottom and select Custom date and time formats. This option will open a dialog box. You can scroll down and look at all the different formats. You can pick one of the formats by clicking on it and then the green Apply button. For instance, let’s choose the second format here.

Now you can see the format applied to the cell.

Alternatively, you can look at all the possible options by clicking the dropdown icon. It’s present next to the Apply button.

You can add any of the details from the options given. All you have to do is just tap on it to add it.

To delete a detail already present, click on the icon next to the option.

Select the Delete option to delete it.

After all your modifications, click on the green Apply button to apply all your changes.


Method 2: Using keyboard shortcuts

If you don’t want the time recalculated and updated on your sheet, you can use this method. This method gives the static current date and time, which means it won’t be recalculated again. 

Step 1: Highlight a cell to display the current date and time.

This is similar to the first step of the previous method. Select a cell by clicking on it. This would highlight it in a blue border and the name of the cell will be displayed in the Name box. Let’s choose A1 in this instance.

Step 2: Enter the keyboard shortcut.

There’s no single keyboard shortcut for adding the date and time. So, you can use two keyboard shortcuts. One for the date and another for the time. Then, you can combine both the cells to give you the date and time in a single cell.

Click on a cell and enter the shortcut. The keyboard shortcut for the date is the Control key and the colon key (Ctrl + :).

Now, click on another cell, B1, and enter the shortcut for the time. The keyboard shortcut for the time is the Control key, Shift key, and the colon key (Ctrl + Shift + :).

Step 3: Concatenate the two cells.

To get the date and time in a single cell, you have to concatenate the contents of the date cell and time cell. In this instance, it’s A1 and B1. Choose another cell and add the two cells. Since it’s A1 and B1 here, the formula would be =(A1+B1).

Step 4: Press Enter.

To see the formula applied to your cell, press Enter. You can now see the date and time in a single cell.


Method 3: Using a script

You can use this method if you want to add a timestamp when a particular change happens in a cell.

Step 1: Select Apps Script.

From the main menu bar, click on Extensions. Now, select Apps Script. This will open a new window. 

Step 2: Copy-paste the code.

Once you click Apps Script, it would open the Apps Script page. 

Delete the existing code and copy-paste the code below.

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());
    }
  }
}

Now, save the code by clicking the Save icon.

Step 3: Type something in the first column to see its timestamp.

Since the 5th line of code equates to 1, it represents the first column or the ‘A’ column. To change your column, you can just change the number — 2 for B, 3 for C, 4 for D, and so on. When you type something in the first column and press Enter, you can see its timestamp in the cell next to it.


Wrapping up

You can use one of the above methods as per your requirements to add the current date and time in your Google Sheet.

Leave a Comment