Questions tagged [google-sheets-dates]
Use for questions related to how Google Sheets handle dates, times, durations.
16
questions
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 ...
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() ...
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 ...
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 ...
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 ...
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 ...
2
votes
1answer
587 views
Adding missing dates to Google Sheets
I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function.
For ...
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 ...
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 ...
4
votes
2answers
2k views
Add missing dates to list of dates with values
Current Data:
9/20/2018 6
9/22/2018 3
9/23/2018 1
Desired Output:
9/20/2018 6
9/21/2018
9/22/2018 3
9/23/2018 1
I would like to have the missing dates added to Column A while the ...
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 ...
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/...
2
votes
2answers
162 views
Filtering multiple ranges into a single list
I have a very long list of data, the first column of which is the date. I have several date ranges from within that list that I want to pull out and combine in a smaller list so I can analyze the ...
1
vote
2answers
355 views
Date fields that refuse to be parsed/formatted
I have some very mysterious date fields in a huge (600 000 records) CSV file which absolutely refuses to behave.
They come up aligned left, and conditional formatting shows that they cannot be seen ...
0
votes
3answers
80 views
Picking the Total from the Next Week
I have the next year mapped out (one line for each day) and have the week subtotal at the end of each week (which is a Friday).
I am wanting the Next weeks subtotal to show in another cell for that ...
-1
votes
1answer
793 views
How to find the closest date to today from a list of dates
I have a list of dates on column A. Each day I need to automatically put the date of today or the next closest date on cell B1.
20/8/2018
6/9/2018
25/9/2018
28/9/2018
11/10/2018
30/10/2018
31/10/2018
...
