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 insert date and time in Google Sheets
Let's start with entering date and time into a Google Sheets cell.
There are three ways to insert date and time into your Google spreadsheet:
Method #1. We add 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, the American date format differs from the European one. If you set "United States" as your locale and type in 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. Make Google Sheets auto-populate your column with date or time.
- Fill a few cells with the required date/time/date-time values.
- Select these cells so you could see a small square at the bottom right corner of the selection:
- Click that square and drag the selection down, covering all required cells.
You'll see how Google Sheets automatically populates those cells based on two samples you provided, retaining the intervals:
Method #3. Use key combinations to insert 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 add 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 #4. 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.
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:
As a result, one and the same date looks different with various formats applied:
As you can see, depending on your needs, there are a few ways to set the date format. It allows displaying any date and time value, from a day to a millisecond.
Method #5. Make your date/time a part of Data validation.
In case you need to use date or time in Data validation, proceed to Format > Data validation in the Google Sheets menu first:
- As for dates, just set it as a criteria and choose the option that suits you best:
- As for time units, since they are absent from these settings by default, you will need to either create an additional column with time units and refer to this column with your Data validation criteria (List from a range), or enter time units directly to the criteria field (List of items) separating them by a comma:
Insert time to Google Sheets in a custom number format
Suppose we need to add 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. Type 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. Type 12:50 to A2 cell and put the following formula into A3:
=A2/60
Method #3. Use special formulas.
Input minutes to A1, seconds - to B1. Enter the formula below to C1:
=TIME(0,A1,B1)
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:
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:
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):
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:
- Get the add-on for your spreadsheets from Google Sheets webstore.
- 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.
- Select the range of cells that contain dates formatted as text.
- Check the box for the option Convert text to dates and click Convert:
Your text-formatted dates will be formatted as dates in just a few seconds.
I hope you've learned 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 be interested in
30 responses to "Date and time in Google Sheets: enter, format and convert dates and time in your sheet"
I created a script that lets you choose a date from a dropdown, and then auto changes the format to include time. Pasted below:
function formatColCD_ToDateTime() {
// First select columns of interest (C&D in this script) and choose
// Data → Data Validation → Criteria = Date, is valid date.
// This script then: sets the format to be date and time for the range C:D
// after a date is picked in those columns.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('tblInput');
var c = ss.getActiveCell();
if( c.getColumn() == 3 || c.getColumn() == 4)
{
sheet.getRange('C:D').activate();
sheet.getActiveRangeList().setNumberFormat('M/d/yyyy H:mm:ss');
}
}
Update: to paste that code click "Tools" --> "Script editor" --> paste code in. ... NOTE - you will have to change the text "tblInput" in the script to whatever your tab is named that you want the script to work on.
HELLO SAGE ,
i was trying your script code in my personal sheet , but after run process completed , it show up some error alike ""Please select an active sheet first. (line 5, file "Code")".
So my question is , how can i add active sheet in that code itself . kindly help me to solve this issue .
Hi Sage,
Below is the script you provided . . . was wondering if you could add another line of code that would set a rule to fix the time range from 8 am to 4 pm on a work day. In event the time data entered falls outside that time range, it would be default set for the next work day at 8 am. Is that possible?
Thank you in advance,
Bruce
_________________________________________________
function formatColCD_ToDateTime() {
// First select columns of interest (C&D in this script) and choose
// Data → Data Validation → Criteria = Date, is valid date.
// This script then: sets the format to be date and time for the range C:D
// after a date is picked in those columns.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('tblInput');
var c = ss.getActiveCell();
if( c.getColumn() == 3 || c.getColumn() == 4)
{
sheet.getRange('C:D').activate();
sheet.getActiveRangeList().setNumberFormat('M/d/yyyy H:mm:ss');
}
}
I think this article would have solved my problem - but I didn.t stop toread it. The moving bar across the bottom is too distracting.
This is worse than auto-play videos - at least you can mute those.
Thank you for your feedback, Flip.
You can make the bar much smaller by clicking on the corresponding button at its upper right corner.
I will pass your feedback to our team so they could take it into account.
agree, the moving bar is distracting. It would be better if it moved when hovering mouse over it rather
Thank you for the feedback, Ibrahim!
Hello, talking about time... I'm new to sheets, coming from excel and still adapting to things I thought were basic and sheets doesnt' do.
I'm trying to write the time manually in a cell, wich by the way is formatted appropriately, and I have to write 8:00 or 8:01, in excel I could just type 8: or 8:1 and because the cell is time formatted it would understand it. Is there a way to do this in sheets or quicker way to manually write time?
Hello Emanuel,
If you enter simply 8:1, Google won't recognize it since it can be 8:10 as well as 8:01. Thus, I'm afraid in Google Sheets you should indicate the time in a way so spreadsheet understands it correctly: with two digits after the colon. This way the time format will be applied automatically.
Is there a way to change the value of the column to subtract 2 hours from the time entered? Say I enter 15:00 can I have it show 13:00 instead?
Henry,
in one and the same cell - no.
You'll need a formula to subtract 2 hours from your time. For example, you can enter the time to A2 and put the formula below to B2:
=A2-TIME(2,0,0)
Whatever time you enter to A2, B2 will always subtract two hours from it and will show you the result.
Is there an easy way in Sheets to set up a cell for data entry of time so that rather than having to type in 14:54 I can just type in 1454 and the cell will be formatted to 14:54. looking to save data entry stokes.
Hello Justine,
I'm afraid no. These characters are essential for Google Sheets to distinguish between numbers and other formats and it's the simplest way.
I should mention that there's a number behind each time unit. For example, 14:54 stands for 0.620833333333333 for Google Sheets. You could enter numbers like this and then format them as time, but you can hardly call it 'easy', I'm afraid, since you should know exactly what to enter.
So entering 14:54 is the simplest way there is.
I am trying to make a over time calculation using the formula
=F6-TIME(8,0,0) +(8>F6)
Where F6 has the duration of the workday.
However i get results that are not working so something is wrong.
Eg if the worktime is 0 the result will show 16:00 (24h-8h)
Can anyone point me in the right direction?
Hello Henrik,
I'm sorry, I don't understand why you add (8>F6). If this condition is true, "1" will be added, if not true - "0" will be added.
Please try to explain how your over time should be calculated and I'll try to provide you with a correct formula.
Creating a User Friendly time sheet that is flexible based upon input.
I almost have everything perfect except.
Is there a way based upon if input was a date and time input vs a decimal input for the same cell.
ex 4/10/2020 7:30:00 or 7:30:00 or 7.5
So I want to give the cell a format for "date and time" OR "decimal" input based upon the input. Is this possible?
Hello Brett,
Once you enter the value, Google immediately recognizes it as Date time, Duration, or Number respectively. However, if you always want to see a tick next to the corresponding format in the Format menu, I'm afraid you'll have to do that manually.
Is it possible to drag down a date and time cell but only change the time by increments of 20 minutes as per below?
May-18-2020: 08:00:00
May-18-2020: 08:30:00
May-18-2020: 09:00:00
May-18-2020: 09:30:00
May-18-2020: 10:00:00
May-18-2020: 10:30:00
May-18-2020: 11:00:00
May-18-2020: 11:30:00
May-18-2020: 12:00:00
May-18-2020: 12:30:00
May-18-2020: 13:00:00
May-18-2020: 13:30:00
May-18-2020: 14:00:00
May-18-2020: 14:30:00
May-18-2020: 15:00:00
May-18-2020: 15:30:00
Hello Luis,
Yes, it's possible. Fill the first two cells, but make sure they are of the format Google Sheets understands. I'd advise you to use the Custom date and time formats for that. Please refer to the end of the first paragraph above for details.
Once you enter the first two dates and times, select both cells and then drag them down. Google Sheets will add 30 minutes in each cell automatically.
very similar to Justine's question above, i have a column for times on my sheet, and i want them to be in Military time, but i want to input like 0800 or 1450 and it auto format to HH:MM format. and if i do a custom number format like "##:##" it works, for the most part, except for the AM(Morning) times, it will show "8:00" instead of the Proper "08:00". Is there any way to fix this to where it will properly show the first zero?
Hello Kelly,
I'm afraid my answer would be the same. The number format you set simply displays the number as you want, but for Google Sheets it is still a number, not time. You can see that by clicking any such cell: formula bar still shows 1450 as a number.
If this is enough for your goal and you're not going to calculate these timestamps, you can keep leading zeros by creating a custom number format like this: 00:00
How can I enter a numerical date MM/YY (03/18) into Google Sheets and keep it from converting the YY to the current year (03/20)? Cannot enter dates as text because I want to use DATEIF.
Hello,
I replied to your comment under another blog post, please have a look.
why wouldn't google allow data validation for "time" ?
Hello Lanalee,
to use time in data validation, you need to either create an additional column with time units and refer to this column with your Data validation criteria (List from a range), or enter time units directly to the criteria field (List of items) separating them by a comma.
I'm trying to input a time value in a minutes:seconds format. However, it automatically changes it to hours:minutes:seconds.milliseconds. Is there any way to circumvent this?
Hello Gus,
Looks like your cells are formatted this way. Please try to change the format of your column by following the steps described in the blog post.
Hello
I want to enter medical data into a spreadsheet such as Temperature & Blood pressure and have the Date and Time data auto fill. I tried this formula but it kept changing the date and time info when I made the next entry. =if(A2="","",today()) I don't want the date or time to ever change as long as data is present in A2.
Thanks
Hello,
You see, such functions as TODAY and NOW are volatile: they recalculate themselves in all cells each time anything is edited in the sheet. There are 2 workarounds I can offer: