4 Ways to get current time on Google Sheets

If you’re here, you probably want to know how you can get the current time on google sheets, and that’s exactly what we’ll explain in this article.

As a programmer, I always say “as long as we have to manage dates, we won’t lose our jobs”. Dates and times are a fundamental aspect of our lives, but they can be painful to manage in the correct way on our computers.

Table of Contents

• Why do you need the current time?
• The NOW() Function
• Using the TODAY() Function
• Using the functions inside a formula
• Volatile functions
• Insert the current time and date statically with a keyboard shortcut
• Using Google Apps Script to generate the current time
• Conclusion

Why do you need the current time?

Knowing the current time can be helpful in a variety of situations. For example, when you print a sheet, you may want to include the current time so you’ll know later if that piece of paper is outdated or not. Additionally, if you’re creating a timeline or scheduling events, knowing the current time is essential for ensuring that everything happens on schedule for example by calculating the time remaining to the event.

Fortunately, there are some simple (and some less-than-simple) methods to insert the current time so you can keep reading till the end and choose the one that best meets your need.

The NOW() Function

The easiest and quickest way to get the time on a Google Sheet is by using the NOW() function. This function returns the current date and time. To use it, just enter the function into the cell you want the current time to be displayed on.

Let’s start with our first example, and simply write the formula on the A1 cell of our Google sheet.

=NOW()

This will by default display the current date and time in the default format. If you want it to be formatted differently, you just need to click on the cell to select it and go to the Format -> Number menu option. From there, you can choose the desired format from the list, or use the “Custom date and time” option for a completely custom format.

You can find the detailed documentation of the NOW() function on the dedicated google support page:

https://support.google.com/docs/answer/3092981

Using the TODAY() Function

As you probably noticed, the NOW function returns a complete time stamp, with date and time down to the seconds. There are, perhaps, situations in which you don’t need all that precision, and maybe you just need the date up to the day without the actual time. For these occasions, you can use the TODAY function and get exactly what you want.

To use the TODAY function, just like with NOW, you need to enter it in the cell where you want the current date to be displayed.

Let’s add today to our previous example in cell A2 so that we have the outputs from the two formulas side by side.

=TODAY()

As you can clearly see, now we have only the current day, without the actual time.

As previously, the cell is formatted with the default format (American Date format in the example, but it depends on your spreadsheet settings that you can change using the Settings option from the File menu). As with the NOW function, you can use the Number option of the Format menu to customize the formatting.

You can find the detailed documentation of the TODAY() function on the dedicated google support page:

https://support.google.com/docs/answer/3092984

Using the functions inside a formula

In some cases, you may want to reference the current time or date in a formula or calculation. To do this, you can use cell referencing to refer to the cell containing the NOW() or TODAY() function, or just insert the function inside your formula.

This is easier to explain with an example than with words. Let’s expand our previous example and make a day countdown.
We’ll write 01/01/2024 on the B2 cell to have a reference to next year’s start, and then put on B3 a counter of the days remaining.

The formula for the counter for the example would be just:

=B2-B1

and that’s it!

As we said, you can use these time functions directly inside a formula. This helps our example to get rid of the B1 and B2 cells and have just a single cell with the countdown. The new formula will be:

=DATE(2024, 1, 1)-TODAY()

The DATE function creates a date given year, month, and day. From that date, we just have to subtract TODAY and we’ll get the difference in days.

Obviously, being a formula, you can mix functions and cell references, so another correct solution could be:

=B2-TODAY()

Volatile functions

NOW() and TODAY() are volatile functions. This means that their value is updated on every edit you make on the spreadsheet (not only to the cells containing them). This has the obvious advantage of auto-updating all the formulas containing them. On the other side, updating all the time can hurt your spreadsheet performance, especially if you have lots of formulas depending on the NOW() and TODAY() functions that need to be recalculated every time.

You would expect that being Google aware that extensive use of these functions can cause performance issues, they would offer a way to stop or limit this behavior. Unfortunately, this is not the case, as there’s a “Calculation” tab in the File -> Settings dialog box, but the Recalculation section where you can change the time update settings has only three options: “On change”, “On change and every minute”, and “On change and every hour”. So not only they are not providing a solution, but they are providing ways to make it worse!

This article is part of our productivity tips for Google Sheets series. You can find them all on our Tips and tricks for Google Sheets page.

Insert the current time and date statically with a keyboard shortcut

The solutions we just explained are for the cases where you need to add a dynamic current date/time. As we saw both functions are volatile functions, updating every time you edit the document with the current timestamp. There are, however, some times when you want to just insert the actual date or date and time without the need to update them regularly.

The first solution coming to mind is to just manually input the current date and get done with it. But if you need to regularly input the current timestamp, maybe with date and time, it can get pretty annoying and time-consuming. Fortunately for us, Google sheets have some keyboard shortcut options to help you streamline your work by working more efficiently.

The available keyboard shortcuts to insert dates and times are:

• Insert date: <Ctrl> + ; on windows, <Cmd> + ; on MAC.
• Insert time: <Ctrl> + <Shift> + ; on windows, <Cmd> + <Shift> + ; on MAC.
• Insert date and time: <Ctrl> + <Alt> +<Shift> +; on windows, <Cmd> + <Option> + <Shift> + ; on MAC.

As always, after you have the date on a cell you can select it and change the format from the format menu. The Format -> Number menu can be used when selecting a range of cells too, the formatting will be applied to all the selected cells.

Using Google Apps Script to generate the current time

While the solutions above should solve 99% of the use cases, there’s another solution to the problem that could be worth at least knowing. This solution could help solve some of the performance issues we talked about when exploring the Volatile functions.

Google Sheets allows users to create custom scripts in their documents by using Google Apps Script, a JavaScript-based scripting language. With a script, you can automate tasks, perform complex calculations, and even access external APIs. We will use it in a very simple way, but the concept can be used in much more complicated scenarios. To start creating a script, click on “Extensions” in the menu and select “Apps Script”

In the editor that will open, you can write your script. In our example, we will add the following script to write the current time on the A2 cell every 5 minutes. In this way we will have an update every 5 minutes instead of the “every edit” we had with NOW() and TODAY().

function updateTime() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2");
  var time = newDate();
  range.setValue(time);
}

function setup() {
  ScriptApp.newTrigger("updateTime")
    .timeBased()
    .everyMinutes(15)
    .create();
}

Then we have to select the setup function from the drop-down menu in the toolbar and hit run to initialize the trigger. Before doing that, check that both the cell on row 3 and the time on row 11 are set to the values you need.

The first time, a short warning will pop up, requesting you to authorize your script to access your data. This is a nice security feature and you should think twice before accepting these authorizations, but since this is a very simple script, you wrote it, and you can check that nothing malicious is executed, you should allow the requested access.

Once the script is initialized, you can close the app script window, get back to your spreadsheet, and see that you now have an “updating every x minutes” date.

If you want a longer interval you can change the “everyMinutes” function with one of the other available functions (everyHours, everyDays, everyWeeks).

Conclusion

In conclusion, there are many ways to get the current time in Google Sheets, from simple formulas like NOW() and TODAY() or the “static” shortcuts, to more advanced methods like scripts. The method you choose will depend on your specific needs and the level of automation you require. With the ability to track time effectively, you can create powerful data analysis tools and track your progress over time. By incorporating these tips into your workflow, you’ll be able to use Google Sheets more effectively and save time in the long run.