Date and time in Google Sheets (2023)

Today we will discuss what you can do with dates and times in a Google sheet. Let's take a look at how to insert the date and time into your table and how to format and convert them to numbers.

How to insert date and time in Google Sheets

Let's start by entering the date and time in a Google Sheets cell.

Advice.Date and time formats depend on your spreadsheet's default locale settings. To change it, go toFile > Preferences. You will see a popup window where you can set your regionIn generalaba >Location. In this way you ensure the date and time formats that you are used to.

There are three ways to insert the date and time into your Google sheet:

Add date and time manually

Observation.Regardless of how you want the time to look at the end, always enter it with a colon. This is important for Google Sheets to distinguish between time and numbers.

It may seem like the easiest way, but the locale I mentioned above plays a crucial role here. Each country has its own standard for displaying the date and time.

As everyone knows, the American date format differs from the European one. If you define "EU" as your location and enter the date in European format: mm/dd/yyyy, it just doesn't work. The date you enter will be treated as a text value, so keep this in mind.

Advice.You can add today's date much faster. Guy@in the required cells and continue with the wordHoy:Date and time in Google Sheets (1)

Google Sheets acknowledges your request and suggests that you addHoy. Click on it and the text on it will change to today's date:Date and time in Google Sheets (2)

confirm by pressingWriting.

Have Google Sheets auto-fill your date or time column

Fill some cells with the required date/time/datetime values.

Select these cells to see a small circle in the lower right corner of the selection:Date and time in Google Sheets (3)

Click on the circle and drag down the selection that covers all the required cells.

You'll see how Google Sheets automatically fills these cells based on two examples while preserving the ranges:Date and time in Google Sheets (4)

Use key combinations to enter the current date and time

Place the cursor in the cell of interest and press one of the following shortcuts:

  • Ctrl+;(semicolon) to insert the current date.
  • Ctrl+Shift+;(semicolon) to insert the current time.
  • Ctrl+Alt+Shift+;(semicolon) to add the current date and time.

Later you can edit the values. This method helps to fix the problem of entering the wrong date format.

Take advantage of the date and time features of Google Sheets

HOY()— returns the current date to a cell.

NO()— returns the current date and time of a cell.

Observation.These formulas will be recalculated and the result will be updated with each change made to the table.

Here it is: you have set the date and time in our cells. The next step is to format the information so that it appears the way you need it.

Just like with numbers, you can have your spreadsheet return timestamps in a variety of formats.

Place the cursor in the desired cell and go toFormat > Such. You can choose from four different standard formats or create a custom format usingcustom date and timelink:Date and time in Google Sheets (5)

As a result, the same date looks different when multiple formats are applied:Date and time in Google Sheets (6)

As you can see, depending on your needs, there are a few ways to set the date format. Allows you to display any date and time value, from one day to one millisecond.

Make your date/time part of the data validation.

If you need to use date or time in data validation, proceed toData > Data Validationfirst in the Google Sheets menu:

  • As for the dates, you just have to define them as criteria and choose the option that best suits you:Date and time in Google Sheets (7)
  • As for the time units, since they are absent in these configurations by default, create an additional column with time units and query this column with your data validation criteria (list of an array) or enter units of time directly in the criteria field (list of goods) separate them with a comma:Date and time in Google Sheets (8)

Enter the time in Google Sheets in a custom number format

Suppose you need to add the time in minutes and seconds: 12 minutes and 50 seconds. Place the cursor on A2, type12.50and pressureWritingon your keyboard.

Observation.Regardless of how you want the time to look at the end, always enter it with a colon. This is important for Google Sheets to distinguish between time and numbers.

What you will see is that Google Sheets treats its value as 12 hours and 50 minutes. if you applyDurationformat to A2, it will still display the time as 12:50:00.

So how can you make Google spreadsheets return only minutes and seconds?

  • Enter 00:12:50 on your mobile phone.

    To be honest, this can be a tedious process if you have to enter multiple timestamps of just minutes and seconds.

  • Type 12:50 in cell A2 and insert the following formula in A3:

    then applyDurationnumerical format to A3. Otherwise, your table always returns at 12.00.

  • Use special formulas.

    Enter the minutes in A1 and the seconds in B1. Enter the following formula for C1:

    To remove unnecessary symbols from our time, set the format again. Gonnacustom date and timeand create a format that only shows the elapsed minutes and seconds:Date and time in Google Sheets (9)

    The TIME function references cells, takes the values, and converts them to hours (0), minutes (A1), and seconds (B1).Date and time in Google Sheets (10)

Convert time to decimal in Google Sheets

Let's move on to the different actions you can take with date and time in Google Sheets.

There may be cases where you need to display the time as decimal instead of "hh:mm:ss" to perform various calculations. Because? For example, count hourly wages, since it is not possible to perform arithmetic operations with numbers and time.

But the problem disappears if the time is decimal.

Let's say column A contains the time you started working on a task, and column B shows the completion time. You want to know how long it took, so you use the following formula in column C:


Copy the formula into cells C3:C5 and get the result in hours and minutes. Then transfer the values ​​to column D using this formula:


Then select the entire column D and go toFormat > From > From:Date and time in Google Sheets (11)

Unfortunately, the result you get at first won't mean much. But Google Sheets has a reason for this: it shows the time as part of a 24-hour period. In other words, 50 minutes equals 0.034722 of 24 hours.

Of course, this result can be used in calculations.

But since we're used to seeing time in hours, you may want to introduce more calculations into your table. To be more specific, multiply the number you got by 24 (24 hours):Date and time in Google Sheets (12)

You now have a decimal value where the whole number and the fraction reflect the number of hours. In summary, 50 minutes equals 0.8333 hours, while 1 hour and 30 minutes equals 1.5 hours.

Format Dates from Text to Date Format with Power Tools for Google Sheets

There is a quick solution to convert dates formatted as text to date format. CalledElectric tools. Power Tools is a plugin for Google Sheets that allows you toconvert your information in a few clicks:

  1. Download the pluginfor your spreadsheets in the Google Sheets online store.
  2. GonnaExtensions > Power Tools > Hometo run the plugin and click the buttonConvertertool icon in plugins panel. Alternatively, you can chooseTools > Converttool directly from the power tools menu.
  3. Select the range of cells that contain dates formatted as text.
  4. Check the option boxConvert text to datesand clickcarrera:Date and time in Google Sheets (13)

Formatted dates in your text will be formatted as dates in seconds.

I hope you have learned something new today. If you still have questions, feel free to ask them in the comments below.

Next timeYou will learn how to calculate the time difference and add dates and times.

You may also be interested in

  • Calculation of time in Google Sheets: subtract, add and extract units of date and time
  • Calculate days, months and years between two dates in Google Sheets: DATEDIF formulas
  • Change Google Sheets date format and convert dates to numbers and text
  • Percentage in Google Sheets: examples of formulas included
  • Exchange rates in Google Sheets with Google Finance


Top Articles
Latest Posts
Article information

Author: Rubie Ullrich

Last Updated: 05/10/2023

Views: 6789

Rating: 4.1 / 5 (52 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Rubie Ullrich

Birthday: 1998-02-02

Address: 743 Stoltenberg Center, Genovevaville, NJ 59925-3119

Phone: +2202978377583

Job: Administration Engineer

Hobby: Surfing, Sailing, Listening to music, Web surfing, Kitesurfing, Geocaching, Backpacking

Introduction: My name is Rubie Ullrich, I am a enthusiastic, perfect, tender, vivacious, talented, famous, delightful person who loves writing and wants to share my knowledge and understanding with you.