Guide on Working with Date and Time in Google Sheets

How do you format date and time when working with Google Sheets? A simple and efficient method for working with date and time will be detailed below.

Google Sheets is an online spreadsheet tool widely used by many. While it shares similarities with Excel in terms of functionality, its interface is slightly different, which may pose challenges for some users. In this article, Officehabit will guide you through formatting dates and times in Google Sheets.

How to Insert Date and Time in Google Sheets

Let’s begin by entering the date and time into a Google Sheets cell.

Note: The date and time format depends on the default language your Google Sheets spreadsheet is using. To check and modify this, go to the File menu > Spreadsheet settings. You will see a pop-up window where you can set your region under the General > Locale tab. Make sure to select the correct date and time format that you are familiar with.

There are five ways to insert date and time into a Google Sheets spreadsheet:

Method 1: Manually Inserting Date and Time

Note: Regardless of how you want the time to appear, it is essential to input it with a colon. This is necessary for Google Sheets to differentiate between time and numbers.

While it may seem like the easiest method, the language setting we discussed earlier plays a crucial role here. Each country has its own way of displaying dates and times.

As we know, the date format in the United States differs from the format in Europe. If you set “United States” as your language and input the date in the European format (dd/mm/yyyy), it will not work. The entered date will be treated as a text value. Therefore, pay attention to that.

Method 2: Automatically Populate Date or Time in Google Sheets

Step 1: Enter some cells with mandatory time values (as shown below, times are pre-entered in cells A2 and A3). Step 2: Click on a cell that has a time value; you will see a small square appear at the bottom right corner of the selected range.

Step 3: Left-click and hold on that square, then drag the selection down to the cell where you want, and release the left mouse button.

Step 4: You will observe how Google Sheets automatically populates those cells based on the two date and time patterns you provided in step 1.

Method 3: Use Keyboard Shortcuts to Insert Current Date and Time

Place the cursor in the cell where you want to insert the current date and time in your Google Sheets, then press one of the following keyboard shortcuts:

  • Ctrl +; (semicolon): To input the current date.
  • Ctrl + Shift +; (semicolon): To input the current time.
  • Ctrl + Alt + Shift +; (semicolon): To add both the current date and time.

Afterward, you can edit the values as needed. This method helps you bypass the issue of entering the date format incorrectly.

Method 4: Use Google Sheets TODAY and NOW Functions

  • TODAY() Function: Returns the current date for a cell.
  • NOW() Function: Returns the current date and time for a cell.

Note: These functions will recalculate, and the result will refresh with any changes made in the spreadsheet. Therefore, if you need to fix this value, after inserting it, you should copy and paste it as a numerical value.

Now, we have set the date and time for the cells in the Google Sheets table. The next step is to format the information to display it the way we need.

Similar to handling numeric values, we can set the date and time in our spreadsheet in various formats. The process is as follows:

  • Step 1: Place the cursor in the cell where you’ve entered the date and time using any of the methods above.
  • Step 2: Go to the Format > Number menu.
  • Step 3: In the dropdown menu, you can choose between four different default formats: Date; Time; Date time, and Duration. Alternatively, you can create a custom format by using the More formats > More date and time formats option.
  • Step 4: Depending on the format you select, the result will vary, as shown in the image below.

As you can see, depending on your needs, there are several ways to format date and time, allowing the display of any date and time value, from a date to a fraction of a second.

Method 5: Set Date/Time as Part of Data Validation

In case you need to use date or time in Data Validation, first, go to the Format > Data validation menu in Google Sheets:

  • For dates, simply set it as the criteria and choose the option that suits you best.
  • For time units, as they are not available in these settings by default, you need to create an additional column with time units and reference this column in your data validation criteria (List from a range) or directly input the time units into the Criteria field (List of items), separated by commas.

Inserting Time in Google Sheets in Custom Number Format

Suppose you need to insert a time value, specifically 12 minutes and 50 seconds, into a cell in Google Sheets. Here’s how to do it:

  • Step 1: Place the cursor in A2 (for this example, but you can choose any cell).
  • Step 2: Enter 12:50 and press Enter.

After entering, you will notice that Google Sheets interprets the entered value as 12 hours and 50 minutes. If you apply the Duration format to cell A2, it will still display the time as 12:50:00.

So, how can you make Google Sheets return the value in minutes and seconds precisely? There are three methods you can choose to achieve this.

Method 1: Enter 00:12:50 into Your Cell

In practice, entering in this manner can be time-consuming, and not many users prefer this method, especially when there are multiple cells that need to be formatted in such detail.

Method 2: Enter 12:50 into cell A2 and place the following formula into A3:

=A2/60

Note: Apply the Duration number format to cell A3. Otherwise, the time data will always return as 12:00 AM.

Method 3: Use a special formula

With this approach, input minutes into A1 and seconds into B1. Then, input the following formula into C1:

=TIME(0,A1,B1)

Instructions on how to work with dates and times in Google Sheets

In this case, Google Sheets’ TIME function will reference the cells, take the values, and convert them into hours (0), minutes (A1), and seconds (B1).

Converting time to decimal numbers in Google Sheets

There might be cases when you need to display time as decimal numbers instead of the default “hh:mm:ss” format to perform various calculations.

This scenario is common when you need to calculate hourly wages, as it is challenging to perform any arithmetic operations using both numbers and time. However, this issue disappears if the time is in decimal form.

Assuming column A is the column with the start time of a task, and column B shows the end time, as in the data table below. Now, you want to know how much time the task took, and in this case, in column C, you can perform the following:

Step 1: Click on cell C2 and enter the following formula: =B2-A2 Step 2: Next, copy the formula down to cells C3 to C5. Step 3: The result returned will be in hours and minutes. Step 4: Now, to convert the hours and minutes in column C to values in column D, you use the following formula: =$C3 Step 5: Then, select the entire column D and go to Format > Number > Number.

The initial displayed result might not reveal much. However, Google Sheets does this because it will display time as part of a 24-hour period. In other words, 50 minutes is 0.034722 of 24 hours.

Of course, this result can be used in calculations. But since we are accustomed to viewing time in hours, to make it more understandable in the spreadsheet, you need to continue multiplying the result obtained in column D by 24 (24 hours). The returned result will be displayed in column E.

Now we have a decimal value, where the integer and fractional part reflects the number of hours. Simply put, 50 minutes is 0.8333 hours, while 1 hour 30 minutes is 1.5 hours.

Converting text-formatted date and time to date and time format using the Power Tools utility for Google Sheets

There is a quick solution to convert text-formatted date and time to date and time format in Google Sheets. Called Power Tools, this is an add-on for Google Sheets that allows you to convert information with just a few clicks.

Step 1: Search, download, and install the Power Tools add-on for your spreadsheet from the Google Sheets online store. Step 2: After downloading and installing this add-on, go to Add-ons > Power Tools > Start to run the add-on and click on the Convert icon in the add-on sidebar. Alternatively, you can choose the Convert tool from the Power Tools menu. Step 3: Select the range of cells containing text-formatted dates. Step 4: Click on the Convert text to dates option and press the Convert button to perform the conversion. Step 5: In this way, your text-formatted dates will be converted to date format with just a few clicks.

Here, Officehabits has guided you through all the ways to work with dates and times in Google, from entering, formatting to converting dates and times in Google Sheets for use in calculations. If you have any questions during use, please leave a comment below for us to answer.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top