Oct
12

Date and time in Google Sheets: enter, format and convert dates and time in your sheet

Today we will start discussing what can be done with dates and time in a Google spreadsheet. Let's take a closer look at how date and time can be entered into your table, and how to format and convert them to numbers.

How to enter date and time in Google Sheets

Let's start with entering date and time into a Google Sheets cell.

Tip. Date and time formats depend on the default locale of your spreadsheet. To change it, go to File > Spreadsheet settings. You'll see a pop-up window where you can set your region under the General tab > Locale. Thus, you'll ensure those date and time formats you're accustomed to.

There are three ways of entering date and time into your Google spreadsheet:

Method #1. We enter date and time manually.

It may seem to be the easiest way but locale settings that we mentioned above play a vital role here. Every country has its own pattern for displaying date and time.

As we all know, American date format differs from the European one. If you set "United States" as your locale and enter the date in the European format, dd/mm/yyyy, it simply won't work. The entered date will be treated as a textual value. So, pay attention to that.

Method #2. Use key combinations to enter the current date and time.

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

  • Ctrl+; (semicolon) to enter the current date.
  • Ctrl+Shift+; (semicolon) to enter the current time.
  • Ctrl+Alt+Shift+; (semicolon) to enter both, current date and time.

Later you'll be able to edit the values. This method helps you bypass the problem of entering incorrect date format.

Method #3. Take advantage of Google Sheets date and time functions:

TODAY() - returns the current date to a cell.

NOW() - returns the current date and time to a cell.

Note. These formulas will be recalculated, and the result will be renewed with every change made in the table.

Here we are, we've placed date and time to our cells. The next step is to format the information to display it the way we need.

As it is with numbers, we can make our spreadsheet return date and time in various formats.

Place the cursor into the needed cell and go to Format > Number. You can choose between four different default formats or decide to create a custom one using More formats > More date and time formats:
Choose the existent date and time format or create a custom one in Google Sheets

As a result, one and the same date looks different with various formats applied:
One date - different number formats

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

Entering time to Google spreadsheet in a custom number format

Suppose we need to enter time in minutes and seconds: 12 minutes, 50 seconds. Place the cursor to A2, type 12:50 and press Enter on your keyboard.

What we see is Google Sheet treating our value as 12 hours 50 minutes. If we apply Duration format to A2 cell, it'll still show the time as 12:50:00.

So how can we make Google spreadsheet return only minutes and seconds?

Method #1. Enter 00:12:50 to your cell.

To be honest, this may turn out a tiresome process if you need to enter multiple timestamps with minutes and seconds only.

Method #2. Enter 12:50 to A2 cell and put the following formula into A3:

=A2/60

Tip. Apply the Duration number format to cell A3. Otherwise your table will always return 12 hours AM.

Method #3. Use special formulas.

Input minutes to A1, seconds - to B1. Enter the formula below to C1:

=TIME(0,A1,B1)
Time formula

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

In order to delete excess symbols from our time, set the format again. Go to More date and time formats, and create a custom format that will show only elapsed minutes and seconds:
Custom time format

Convert time to decimal in Google Sheets

We move on to various operations we can do with date and time in Google Sheets.

There may be cases when you need to display time as a decimal rather than "hh:mm:ss" to perform various calculations. Why? For example, to count per-hour salary, since you can't perform any arithmetic operations using both, numbers and time.

But the problem disappears if time is decimal.

Let's say column A contains the time we started working on some task and column B shows the end time. We want to know how much time it took, and for that in column C we use the formula below:

=B2-A2

We copy the formula down cells C3:C5 and get the result of hours and minutes. Then we transfer the values to column D using the formula:

=$C3

Then select entire column D and go to Format > Number > Number:
The first steps of converting time to decimal

Unfortunately, the result we get doesn't say much at first glance. But Google Sheets has a reason for that: it displays time as a 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're used to seeing time in hours, we'd like to introduce more calculations to our table. To be specific, we need to multiply the number we got by 24 (24 hours):
Time converted to decimal

Now we have a decimal value, where integer and fractional reflect the number of hours. To put it simply, 50 minutes is 0.8333 hours, while 1 hour 30 minutes is 1.5 hours.

Text-formatted dates to date format with Power Tools for Google Sheets

There's one quick solution for converting dates formatted as text to a date format. It's called Power Tools. Power Tools is an add-on for Google Sheets that allows you to convert your information in a couple of clicks:

  1. Get the add-on for your spreadsheets from Google Sheets webstore.
  2. Go to Add-ons > Power Tools > Start to run the add-on and click the Convert tool icon on the add-on pane. Alternatively, you can pick the Convert tool right from the Power Tools menu.
  3. Select the range of cells that contain dates formatted as text.
  4. Check the box for the option Convert text to dates and click Convert:

Convert format on Google Sheets with Power Tools

Your text-formatted dates will be formatted as dates in just a few seconds.

I hope you've learnt something new today. If you have any questions left, feel free to ask them in comments below.

Next time we'll continue with calculating time difference and summing dates and time together.

You may also like:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard