Questions tagged [google-sheets-dates]
Use for questions related to how Google Sheets handle dates, times, durations.
180
questions
1
vote
1answer
2k views
Google Docs date in yyyy/mm/dd format (year first)
My question is similar to Google Docs date in “dd/MM/yy” format and How can I change the input format for dates in Google Docs' spreadsheet?
BUT
unlike those answers, I'm trying to get the ...
0
votes
1answer
142 views
Plus or minus quarters in Google Sheets using formulas
I have a date format like this
1Q16
2Q16
3Q16
4Q16
Is there a way to get...
input: 1Q16 + 4 output: 1Q17 and...
input: 1Q16 - 3 output: 2Q15
... using formulas only and not code?
0
votes
1answer
121 views
Google sheets query multi conditional date cell ref
I have a doubt regarding a change in this formulas in sheets:
=INDEX(QUERY($A$3:$H; "SELECT sum (D) where C='Pub'");2)
=INDEX(QUERY($A$3:$K; "SELECT sum (F) where C='Reg'and K='"& K15&"'");...
1
vote
1answer
284 views
Pull a value for the following month in Google Sheets
I have a list of values, and they are all for a variety of months (Jan 2018, Feb 2018, &c.). Google Sheets automatically converts these values into 1/1/2018 and so on. Is there a formula I can use ...
5
votes
1answer
2k views
What date formats does Google Sheets recognize?
I have googled this and looked across SO, haven't found anything. How can I get a list of the date formats Google Sheets recognizes?
I found this - https://developers.google.com/sheets/api/guides/...
22
votes
5answers
37k views
How to convert Unix Timestamp to human readable?
As the title says, I have Unix Timestamps on a sheet and I need to convert them to normal human-readable dates. So far I'm striking out, Google searches have turned up a few suggestions but none have ...
7
votes
2answers
17k 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 ...
8
votes
1answer
4k 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 =...
26
votes
1answer
33k 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 ...
7
votes
3answers
12k 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.
...
11
votes
5answers
17k 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 ...
-1
votes
1answer
1k 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 ...
2
votes
1answer
2k 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] ...
11
votes
1answer
64k 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
2answers
1k 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 ...
2
votes
2answers
6k 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 ...
4
votes
1answer
979 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 ...
4
votes
1answer
1k 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 ...
2
votes
2answers
4k 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 ...
2
votes
1answer
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() ...
2
votes
1answer
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 ...
0
votes
1answer
120 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|...
4
votes
3answers
24k 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?
7
votes
3answers
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?
...
29
votes
5answers
46k 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 ...
5
votes
3answers
6k 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 ...
36
votes
4answers
71k 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 ...
26
votes
9answers
154k 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 ...
90
votes
14answers
219k 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 ...
69
votes
6answers
167k 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 ...
