Date and time in Google Sheets (2023)

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.

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:

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:

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:

As a result, the same date looks different when multiple formats are applied:

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:
• 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:

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:

  =A2/60
  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:

  =TEMPO(0;A2;B2)
  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:

  The TIME function references cells, takes the values, and converts them to hours (0), minutes (A1), and seconds (B1).