In this post, you’ll see how to add the current date and time in Google Sheets.

If you are working in Google Sheets, then needing to add the current date or time is something you might frequently encounter.

You might want to add the date and time whenever an event occurs such as opening the workbook or entering data in a specific cell.

Manually adding the current date and time for such scenarios will be a lot of effort. Instead, you can automatically insert the current date and time with functions, keyboard shortcuts, and Apps Scripts.

If you want to discover how to insert the current date and time in Google Sheets, then get your copy of the example workbook and follow along.

Insert the Current Date and Time with Functions

If you agree that repeatedly updating the current date and time in your workbook is not a feasible task, then using the built-in functions available inside Google Sheets is the best way to do this.

The TODAY and NOW functions can insert the current date and the current date-time in your Google Sheets.

Insert Current Date Using the TODAY Function

When you are working with Google Sheets and come up with a situation where you want to add the current date to the sheet, you can use the TODAY function.

This function doesn’t take any argument, and it will provide you with the current date.

The standard date format for your Google Sheets is MM/DD/YYYY or MM/DD/YY, and this function will return the same by default.

Depending on the location and the format you set, the output format may vary for this function’s results.

= TODAY ()

To get the current date in your sheet, insert the =TODAY() formula without any arguments into any cell. It will give you the current date, as shown in the screenshot above.

Notes

• The TODAY function will automatically change the date the next day. It will always show the date on which the entire sheet is recalculated.
• The TODAY function is volatile, this means it will recalculate whenever you edit the workbook, and you should be cautious while using the function.

Insert the Current Time Using the NOW Function

If you want to show the current date and time together, the NOW function is the best option you have.

If you only want to see the current time, you can use the custom time format to convert the date and time value into only a time value.

This function doesn’t require any arguments, and the date and time will be shown in the default format used by Google Sheets which is the 24-hour time format MM/DD/YYYY HH:MM:SS. The timestamp format may vary based on your region.

= NOW ()

Type the function =NOW() into any cell of your sheet to return the current date and time in the cell as shown above.

To only show the time value, you need to format the timestamp value.

Follow these steps to format the timestamp output as a time only.

1. Go to the Format menu.
2. Select the Number option from the menu.
3. Select the Time format option from the submenu.

The value in the cell should be now shown as a time value. The standard time format is HH:MM:SS AM/PM which is a classic 12 hours time format.

The value is still the full date and time, but only the time is now displayed.

It is possible to use a formula that can give you only the time value part of the date-time. For this, you will need to use a combination of the NOW and TRUNC functions.

Use the =TRUNC(NOW()) formula to get only the date part from the date-time value that the NOW function generates.

Now, you can subtract this combination from NOW to get the time part only.

You can then format this value as a time. Go to the Format menu ➜ then select the Number option ➜ then select the Time format to convert this value into a proper 12 hour time.

= NOW () - TRUNC ( NOW () )

You can directly copy and paste the formula above to get the time component.

Interestingly, you can control when this formula recalculates through the settings.

By default, the TODAY and NOW functions will recalculate their value only when the sheet changes. To control the recalculation, go to the File menu ➜ Click on the Settings option to access the spreadsheet settings.

Inside the Calculation tab, you can see the Recalculation dropdown. There are three options available.

• On Change – Formulas will recalculate when the sheet changes with any edits.
• On change and every minute – Formulas recalculate when the sheet changes with any eidts and every minute.
• On change and every hour – Formulas recalculate when the sheet changes with any eidts and every hour.

Choose any of these settings to set the formula recalculation and then press the Save settings button to implement the changes on your spreadsheet.

Notes

• Please remember that the NOW function recalculates by default on every edit in the sheet. This makes it a volatile function that can cause performance issues for your spreadsheet.
• It always specifies the last datetimestamps the recalculation happened rather than showing the first instance of the change happening.
• You can always use the Format menu to change the format to either date only or time only for the output of the NOW function.

Insert the Current Date and Time with the Keyboard Shortcuts

Google Sheets is full of shortcuts, and there are many of them that you could use to save time on manual tasks.

You already know the NOW and TODAY functions make your Google Sheets volatile. If you want to add a static date or time that doesn’t change, these functions will not help.

To counter this effect, you can use the keyboard shortcuts to add static, date, time, or date-time both inside the Google Sheets. The benefit of using the keyboard shortcuts is the date and time you add using these shortcuts don’t update every time the sheet recalculates.

Keyboard Shortcut to add Current Date

To add the current date in Google Sheets, you can use the Ctrl + ; (semicolon) keyboard shortcut.

Keyboard Shortcut to add Current Time

To add current time into Google Sheets, you can use the Ctrl + Shift + : (colon) keyboard shortcut.

Keyboard Shortcut to add Current Date and Time

To add the current date and time in Google Sheets, use the Ctrl + Shift + Alt + ; (semicolon) keyboard shortcut.

Insert the Current Date and Time with Apps Script

Almost everything can be automated in Google Sheets through the Apps Script, a powerful JavaScript-based programming language adopted by Google for automation.

If you are new to Google Apps Script and want to learn more then check out this Apps Scripts guide for beginners to learn more.

Suppose you want an Apps Script that can automatically fill in the current date and time as soon as someone from your team marks the task as completed.

You have data as shown above, and you want the system to add the current date as soon as anyone enters Completed in the Status column.

You can achieve this by writing a onEdit function in Google Apps Script. You can copy the following code that does this for you.

function onEdit(t) {
  var row = t.range.getRow();
  var col = t.range.getColumn();
  if (col == 4 && row > 1 && t.range.getValue() == "Complete") {
    t.source.getActiveSheet().getRange(row, 3).setValue(new Date());
  };
};

Explanation

• Define a onEdit(t) function with the function keyword within the Apps Script.
• Then the range.getRow() method is called on t to get the rows that are being edited. The data is stored into the row variable.
• Similarly, the range.getColumn() method is called on t to get the columns that are being edited. The data is stored into the col variable.
• You want to add a time stamp as soon as someone types Complete in column D. Therefore, the if condition is added. It checks if user has entered Complete column 4 (i.e. column D) or not. You used the t.range.getValue() method to check if the value is entered or not. Then, col == 4 arguments checks whether changes are happening in column 4 or not.
• If the condition is met, you get the active row where change happened and go to the third column to set the value as the current date. The getRange(row, 3) method accesses the current row in third column. The setValue() method allows you to set the values. The new Date() function then allows you to add the current date to that particular cell.

You don’t need to run this code as it is developed to work when the sheet is edited. Therefore, just save it using the Save button placed in the ribbon placed on the upper side of the script.

Now, as soon as you enter the text as Complete in the Status column (column D), you will see that the system adds the current date value automatically.

Also, note that when you enter the Status as Pending, the system doesn’t add the current date value. This is because the condition is set to only work when the value is Complete in column D for any row.

Conclusion

If you are working on Google Sheets, there is usually a situation when you want to add the current date or time to your sheet.

The TODAY function in Google Sheets allows you to get the current date in the cell you call it from. The function doesn’t take any argument.

The NOW function gives you the current date and time. However, if only want the time returned, you achieve this with formatting or a formula.

A combination of the TRUNC and NOW functions can get the current time part on its own.

There are handy keyboard shortcuts that can be used to add the current date and time when a static entry is required. These are handy when you don’t want the date or time to change once it’s been entered.

Finally, you have Apps Scripts that can automate adding the current date and time in a static way. You can use them to automatically add a timestamp based on particular events occurring.

If you know any other ways of adding the current date and time to the Google Sheets, share them in the comment section below.