How to Show the Current Time in a Google Sheet
One of the many functions Google Sheets users can perform in the app is to show the current time. First-time users may be confused with the syntax at first, but fortunately, displaying time in your spreadsheet is a relatively straightforward process.
In this article, we’ll show you how to use these functions to show current time using the two most popular functions: NOW and TIME. Whether you want to display both date and time or just one value, these functions have got you covered. You’ll also learn how to customize the results so you can get the most out of this handy feature.
Below, you’ll find detailed instructions on how to show current time using the most common functions.
How To Add the Current Time and Date Using NOW
Let’s start with one of the most widely used functions to show the current date. You can use this formula to add the current date (or time) onto your spreadsheet or incorporate it into a different date or time formula.
The syntax for the NOW function is as follows:
This syntax consists of the name, brackets, and comma separators. It doesn’t have any arguments that are usually a part of the syntax and are entered inside the brackets.
If you do bookkeeping or perform other tasks that need you to add the exact date and time on a specific spreadsheet, it’s best to use the NOW function.
- Open a Google spreadsheet or create a new one.
- Click on a cell in which you want to display the current date and time. This will make the cell active.
- Type “
=NOW()“and press enter. The brackets make sure you’re using this word as a function. You’ll now see the date and time appear in the cell where you’ve entered the formula. You can see the complete function in the bar on top of the worksheet.
Here’s what you should know about the NOW function:
- The NOW function is volatile. It recalculates or updates every time its spreadsheets are edited. In the spreadsheet settings, you can choose to recalculate the worksheet “On change and every minute” or “On change and every hour.” However, there is no way to turn off volatile function recalculations.
- The date and time showing in the spreadsheet will always refer to the current time after recalculating the spreadsheet and not the date and time of its first entry.
- You can change the number formatting to hide one component of the function.
How to Insert the Current Date Using TODAY
To only display the current date in Google Sheets without the time stamp, it’s best to use the TODAY function. Depending on your local settings, the date will show in the DD/MM/YY or the MM/DD/YY format. Similar to the NOW function, TODAY doesn’t have any arguments. This means there will be no syntax between the brackets.
Follow the steps below to insert the current date using the TODAY function:
- Select an empty cell from your Google Sheet to make it active.
- Type “
=TODAY()” and press enter.
The cells containing the TODAY formula update each day, and you can further customize the formatting to use numbers or text according to your preference.
Formatting Your Date and Time Formulas
By default, the NOW function shows a time and date timestamp on your Google Sheet. To change this setting, you need to tweak the formatting for the cell that contains that timestamp. Also, the same formatting rules go for the TODAY formula as well.
Follow the steps below to change the formatting of your formulas:
- Select the cell showing the time and date with the NOW (or TODAY) formula.
- Click on “Format” from the toolbar above the spreadsheet.
- Hover over the “Number” option from the drop-down menu.
- Select “Time” if you want the function only to show the current time. Likewise, select “Date” for the function to only display the current date.
If you want to modify the NOW or TODAY function format, you can do so from the Format menu by following the steps below:
- Select the cell you wish to modify. You can select a range of cells as well.
- Click on “Format,” then “Number,” and “More Formats.” Next, look for the “More Date and Time Formats” option, which will open a new dialog box.
- You can choose from more than a dozen formats. Select the one you want and click on the “Apply” button in the upper right-hand of the dialog box. The date can be written in the form of numbers, include text, or have additional characters (forward slash).
- Go back to the cells to check whether they match the format you set.
You can apply various formats for different cells on your Google Sheet.
How do I Insert Static Times or Dates in Google Sheet?
If you prefer to work with static date and time, you can use a shortcut option that includes either manually writing the date and time or entering them using the following commands:
• “Ctrl +;” – static date shortcut (Windows)
• “Ctrl + Shift + :” – static time and date shortcut (Windows)
• “Command + ;” – static time shortcut (Mac)
The NOW and TODAY formulas can’t display static times.
Can I still add the current time and date using GoogleClock?
Adding current time and date using GoogleClock is not supported anymore. This function was used for spreadsheets that were published on the web and only occasionally updated. Instead of GoogleClock, you can use the NOW or TODAY formulas to add current time and date into your Google Spreadsheet.
Can I convert the time to a decimal in Google Sheets?
Chances are, you may have to convert the time and date data values in your Sheet into decimals. This conversion is often present when converting the difference between the beginning and end times of a task into decimals.
To convert time to decimal numbers in Google Sheets, you can use the time functions such as HOUR, MINUTE, or SECOND, or the TIMEVALUE function.
The HOUR Function
The hour function will take in a specific time value and return only its hour component. So, for the time value of “05:14:40,” it will return ‘’5’’ as it rounds to the nearest hour.
Here’s the function syntax:
In this function, “time” shows a time value or a reference to a cell with a time value.
The MINUTE Function
This function does the same thing as the previous one but only returns the minute value. For the same time value of “15:14:40,” it will return ‘’15.’’
Here is the syntax:
The SECOND Function
Like the HOUR and the MINUTE function, the SECOND function will return the second component of a cell’s time value. So, if we take the same example, “15:14:40,” the function will return ‘’40.’’
The syntax is as follows:
Converting Time to the Number of Hours
When you have the hour, minute, and the second number in a time value, you can also convert it to a decimal that’s equivalent to that value in terms of hours by using the formula below:
B3 in this example refers to the cell where the time value is active. It can be any cell you select on your spreadsheet.
Convert Time to Number of Minutes
You can follow the same logic to convert a time value to the decimal equivalent to the number of minutes.
Again, the B3 here is only an illustration.
Convert Time to Number of Seconds
This is the formula you should apply to convert time from the cell containing the time value (we assume it’s B3) to a number of seconds.
Time Waits for No One
Showing current time and date in Google Sheets is a simple operation everyone can perform with a little help. This article provided you with all the necessary instructions to seamlessly show all time and date values you need, format them or convert them to decimal numbers.
Whether you’re a financial or data analyst or you simply work on spreadsheets often, you’ll never have to worry about displaying time and date on your sheets again.
For which operations do you use the NOW and TODAY formulas? Do you prefer to keep the default number formatting or change it to a more specific one? Share your experiences in the comments section below.