Questions tagged [google-sheets-dates]
Use for questions related to how Google Sheets handle dates, times, durations.
281
questions
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 ...
- 119
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 =...
- 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:
...
- 133
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 ...
- 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.
...
- 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 ...
- 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 ...
- 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 ...
- 1
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 ...
- 81
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] ...
- 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 ...
- 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?
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 ...
user79865
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 ...
- 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 ...
- 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" ...
- 11
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 ...
- 41
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 ...
- 51
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 ...
- 163
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() ...
- 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 ...
- 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|...
- 11
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?
- 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?
...
- 433
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 ...
- 461
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 ...
- 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 ...
- 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 ...
- 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 ...
- 1,957
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 ...
- 953
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?
- 1,470