Questions tagged [google-sheets-dates]

Use for questions related to how Google Sheets handle dates, times, durations.

Filter by
Sorted by
Tagged with
11 votes
2 answers
24k views

Entering mm:ss: milliseconds into Google Sheets for time duration (arithmetic)

I would like to enter times mm, seconds, milliseconds into Google Sheets and subtract times to determine duration. I use format> numbers > More formats > more dates and times > and apply mm:ss: ms. I ...
user avatar
9 votes
1 answer
6k views

Year function in Google Sheets gives unexpected results

Quick questions with Google Sheets. I'm trying to extract the year from a date using the YEAR() function. In the first column, I formatted all cells as dates, but in the second column, when I use =...
user avatar
  • 93
3 votes
1 answer
625 views

Time tracking with Google Spreadsheets

I'm using Tasker and am trying to set up a profile that would track how many hours I spend at work. I've managed to set up the tracking part, which inserts the data in a Google Spreadsheet like this: ...
user avatar
28 votes
2 answers
45k views

Change Google Sheets default date format with other locale

I would like to have the "dd/MM/yyyy" (e.g. 09/03/2017) as default date format for my Google sheets. I know how to do this for one sheet: Format > Number > More formats > More date and time ...
user avatar
  • 417
8 votes
4 answers
18k views

Using Google Sheets, how can I calculate the average per month based on multiple rows?

I have a spreadsheet where I enter Groceries on each row every time I visit a store. I have calculated the overall sum, but now I would like to calculate the average cost per month for the groceries. ...
user avatar
  • 181
1 vote
2 answers
640 views

Misinterpreted hours and minutes in Google Apps Script while reading Google Spreadsheet data

I have a datetime columns in Google Spreadsheet which I'd like to iterate and use "hour" value to calculate something in custom Google Script. This is an example of a value I have in Spreadsheet: My ...
user avatar
  • 131
14 votes
6 answers
25k views

Change date format [YYYYMMDD] to [MM/DD/YYYY]

I generated a report that formats date as 20160509 and my Google Sheets locale is already set to United States. I've played around with various options within Format > Number > Date Format but I can't ...
user avatar
  • 243
-1 votes
1 answer
2k views

How to use Now() to subtract from column of date/time to get hour difference

I have searched a bit and can't figure this out. I've tried using Now() in the formula along with putting it in a cell and running the formula against it. I have a column of date/times. I would ...
user avatar
3 votes
2 answers
12k views

Time duration formatting in Google Spreadsheets

A couple issues here: I have a formula that is trying to determine if a time duration (ex 7:51) is greater than or less than a baseline duration value. When typing the formula I have to put the ...
user avatar
2 votes
1 answer
3k views

How do I calculate average amount of days between multiple dates?

I have an ever increasing list of dates (YYYY-MM-DD format from 8601:2004) running in a column from cell B5 down. I want to add the average amount of days between all dates in A2. example: [B6] ...
user avatar
  • 23
1 vote
1 answer
2k views

Human readable duration values on Google Spreadsheet

I'd like to have a column where I can enter a human readable duration that is not ambiguous (to users who don't know the spreadsheet). I'd like to use the follow format: 1m 2h 50m 20s 20sec There ...
user avatar
  • 113
15 votes
1 answer
87k views

Compare date value to current date in Google Sheets

How can I compare the date in a row to a current date, so that if it's greater than today the next steps in my process need to be executed?
user avatar
6 votes
2 answers
2k views

Fill zeros for the dates when no data was recorded

I have a table with dates (in increasing order) and corresponding values. Some dates are missing, like 3/27 below. I'd like to insert them in Date column, and have 0 entered in the Value column for ...
user avatar
4 votes
3 answers
10k views

Query or filter the data where the start time is past 3:00 pm

I've started using Google Sheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the start time ...
user avatar
  • 41
2 votes
1 answer
287 views

Google Spreadsheet Inconsistent Datetime Format

When we push date and time values to a google spreadsheet in the format d/m/Y H:i a (eg. 25/12/2015 1:43 pm) the date and time is displayed inconsistently, see below: Any idea why the format is not ...
user avatar
  • 342
1 vote
1 answer
5k views

How to delete a row in a Google Sheet based on date

I have a Google Sheet I'm using to geocode addresses, which shows where current road closures are located. In my table, I have a date field "StartDate" and a number field "Duration". "Duration" ...
user avatar
4 votes
1 answer
1k views

Autopopulate Tuesdays, Thursdays, Saturdays starting from a fixed date

I am trying to auto-populate dates from a fixed date in Google Sheets. Essentially, I want it to auto-populate the dates for every Tuesday, Thursday and Saturday. I have found stuff on skipping ...
user avatar
4 votes
1 answer
2k views

Formatting Google Sheet Dates Inconsistent

I have a sheet where various data is entered through a form. Dates are picked on the form using the standard date picker provided by Google Forms. Some dates in a column I can change the date format ...
user avatar
2 votes
1 answer
5k views

In Google Sheets, how do I change a string which encodes a date into a date?

I have some cells in the format 15/05/15. Here is an example. These are dates, but as I copied them into Google Spreadsheets, they are apparently treated as strings. How do I convert them into date ...
user avatar
2 votes
1 answer
5k views

Dynamic date range query

I'm trying to create two cells that will update my query: A >= date"A1" and A <= date "B1". My goal is to create a search page for the data, where the end user could insert a date or the =today() ...
user avatar
  • 21
3 votes
1 answer
2k views

How to parse a date and time in the format 2012-12-12T00:00:00+00:00?

I have a CSV file with dates in the following format: 2012-06-20T00:00:00+00:00 Is it possible to parse a date or date and time in this format? The goal is to turn it into a date and time that ...
user avatar
  • 447
0 votes
1 answer
125 views

Conditional counts based on dates

I am creating a master tracker for all the technology in my school and it is pulling data from many sheets and placing it in to blocks and I am having trouble figuring out the following School|Room|...
user avatar
7 votes
3 answers
32k views

Calculate days between now and a future date

I need to calculate how many days are left from today to a certain date in the future in Google Sheets. How can I do that?
user avatar
  • 585
7 votes
3 answers
4k views

Date and time format

I have lots of dates in the first column in the format output by IFTTT: August 23, 2010 at 11:01PM Is there a way to get them formatted in a way that I can then use for date and time calculations? ...
user avatar
35 votes
5 answers
59k views

Conditionally format by day of the week

How can I conditionally format cells in Google spreadsheets containing dates to have a different background according to the day of the week. For example, if a date falls on Saturday or Sunday, set ...
user avatar
5 votes
3 answers
7k views

Sorting Google spreadsheet by month and day but not year

I made a Google spreadsheet with a list of birth dates, anniversaries, etc. that I would like to sort by when they occur in the year, not when they first happened. If you sort by the date normally, it ...
user avatar
  • 1,040
43 votes
4 answers
81k views

Add 3 hours to Date value (DD/MM/YYYY 00:00:00)

I have cells with dates in this format: DD/MM/YYYY 00:00:00 and I want to add 3 hours because I'm in a different GMT zone. Is possible to use dates in a formula? Something like: 30/12/2012 22:15:00 ...
user avatar
  • 541
34 votes
9 answers
223k views

How to Fill a Column with Sequential Dates in Google Sheets

In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
user avatar
  • 501
106 votes
14 answers
266k views

How do I calculate a time delta in my Google Spreadsheets?

I've got a Google Spreadsheet that looks roughly like this: Date | Start time | End time | Minutes ------------+-------------+-------------+----------- 1/11/2012 | 11:39 | 12:41 ...
user avatar
82 votes
6 answers
200k views

How to add days to a date

I'm using a Google Spreadsheet to perform calculations and I'd like to do some simple date math. For example, TODAY() add 7 days. I don't see any functions to do it and some quick stabs in the ...
user avatar
20 votes
2 answers
26k views

How can I change the input format for dates in Google Docs' spreadsheet?

The input string for dates in Google Docs's spreadsheet app seems to be set to en_US, which is quite unnatural to me. How can I change the input format?
user avatar

1 2 3 4 5
6