Questions tagged [google-sheets-dates]
Use for questions related to how Google Sheets handle dates, times, durations.
308
questions
110
votes
14
answers
277k
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,037
83
votes
6
answers
204k
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 ...
- 963
44
votes
4
answers
86k
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 ...
- 551
36
votes
9
answers
251k
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 ...
- 521
35
votes
5
answers
65k
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
29
votes
2
answers
52k
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 ...
- 427
23
votes
4
answers
48k
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 ...
- 1,193
20
votes
2
answers
27k
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,520
15
votes
6
answers
29k
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 ...
- 253
15
votes
1
answer
97k
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?
13
votes
2
answers
29k
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 ...
- 139
11
votes
4
answers
24k
views
How to convert datetime string to datetime value in Google Sheets?
Let's say cells A1 and A2 have these values:
13 Jan, 02:21 am
12 Jan, 10:15 pm
And many more values like those below in that column.
What formula could I use so that the B column would look like ...
- 704
10
votes
1
answer
7k
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 =...
- 103
9
votes
4
answers
19k
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.
...
- 191
9
votes
3
answers
23k
views
How to reduce number of X axis labels?
I have a chart with dates along the X axis, but the chart displays them quite close together, producing a rather ugly graph. How might I reduce the number of visible labels to increase the readability ...
- 193
7
votes
3
answers
34k
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?
- 615
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
7
votes
1
answer
3k
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/...
- 1,032
6
votes
3
answers
30k
views
Convert Text String into Date/Time Value
I'm fetching data into a Google Sheet. The date column contains multiple cells with the following text string pattern:
Mon Mar 11 19:05:32 +0000 2019
The pattern always repeat itself:
[day] [month] ...
- 772
6
votes
2
answers
13k
views
How to set Monday as start day of the week in Google Sheets date picker
How to set Monday as start day of the week in Google Sheets date picker?
I have changed the locale, changed start day of the week in Google Calendar and can't find a way to solve this.
- 161
6
votes
2
answers
3k
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
6
votes
1
answer
2k
views
How can I horizontally space data points on a chart when the dates are arbitrary?
Say I have events that occur on sporadic dates, like this:
Date
Qty
2022-01-01
100
2022-02-08
200
2022-06-17
150
2022-08-03
700
2023-01-27
300
I have rows for arbitrary days in Jan, Feb, June, ...
- 161
6
votes
2
answers
6k
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
3
answers
12k
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 ...
- 51
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
5
votes
1
answer
2k
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 ...
- 51
4
votes
2
answers
140
views
How to get a new date when calculating from today plus an integer
I use Google Sheets to calculate my word count & rate for daily writing sessions. I'm trying to come up with a formula that will return the estimated date of hitting a goal, based on my current ...
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
3
votes
2
answers
8k
views
How to increment dates by exactly one month from a given date in Google Sheets
I'm using Google Sheets and want to set a date in say E:7, then in Column B I want the first row to be the value E:7 and then all the rows below that to be incremented by exactly one month from the ...
- 157
3
votes
1
answer
647
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
3
votes
1
answer
530
views
Print incremental numbers in Google Sheets
Cell A1 holds a numerical value (e.g. 10), and I'd like to output the list of numbers from 1 to that number in B1:
What's the formula I need for B1? It seems trivial, and I know it probably requires ...
- 133
3
votes
1
answer
2k
views
Date format in spreadsheet
I want to create spreadsheets to record the money I spend each year.
Each file is named as Cost-2012, Cost-2013,...etc.
Inside each file, there are in fact 12 sheets, which are named as 01, 02, ..., ...
- 141
3
votes
2
answers
88
views
How to get a difference of "X years, Y months, Z days" between two dates?
I have two dates: 29/05/2020 and today.
How can I get a date diff of these 2 dates, in a meaningful format such as "Z years, X months, Y days" separating these dates?
Ideally this should ...
- 31
3
votes
1
answer
80
views
Rolling Year Formula
I have a Google sheet where I would like a score based on a rolling year figure.
I have managed to do these separately using the formulas:
=query(A2:B," where A >= date '"&TEXT(today()-E1,"...
- 33
3
votes
1
answer
3k
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 ...
- 31
3
votes
2
answers
14k
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
3
votes
1
answer
3k
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 ...
- 487
3
votes
2
answers
8k
views
Query(Importrange) or Query with dates
I am trying to show or limit what is seen to just entries made in 2019. I found a few places that referenced this and have tried the following without success.
=query(IMPORTRANGE("1bSyor-...
- 33
3
votes
0
answers
468
views
How do I specify a consistent interval for a date-range axis in a Google Sheet Chart
I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or ...
- 31
3
votes
3
answers
511
views
Conditional formatting for columns, date & time specific?
I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this:
2018-11-01T20:16:52
I have tried multiple approaches to sorting ...
- 31
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 ...
- 173
2
votes
1
answer
202
views
Difference between today and a date with negative numbers
I need to calculate the difference in days between today and a set date allowing also negative differences.
Basically it's the shipping date for each order and how many days we have left till each ...
2
votes
1
answer
2k
views
Google Sheets' MONTH function on an empty cell returns 12
Is this an expected behaviour that month(ref) returns 12 when the ref is an empty cell?
I would have thought that this is a serious bug.
Am I correct?
No mention of that behaviour in the official ...
- 321
2
votes
2
answers
43
views
Query form data by date to return counts by item and category
I'm a teacher and need help with a Google Form that collects the following info:
Timestamp
Name
Class
Lunch option
All of the data comes into a Google sheet. I'm using successfully using the QUERY ...
- 21
2
votes
1
answer
615
views
Convert string to real date time value?
Here is my current input:
14.05.20
12.37.01
And yes, as it shows, there is a line break in the cell...
Here is example sheet: https://docs.google.com/spreadsheets/d/...
- 648
2
votes
1
answer
6k
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
2
votes
1
answer
65
views
Counting rows based on the date it has and the current end of the week
Problem to solve:
Given 2 rows, containing the author's name and a date, you have to count how many rows that author has.
The tricky part here is that I have to use the date row and check how many ...
- 23
2
votes
2
answers
39
views
LOOKUP-like behaviour adding up rows down to the found one
I have a table with dates in column A and values in column B.
A B
1 2020-12-30 1
2 2020-12-31 2
3 2021-01-04 4
Given a DATE, I can LOOKUP the last value before (including) ...
- 759
2
votes
1
answer
7k
views
Using dates as criterion for COUNTIFS in Google Sheets
So I'm currently making a recruiting data sheet for an online community. I'm trying to make it as automated as possible without actually making a bot to do it.
How can I make a COUNTIF(or similar) ...
- 43
2
votes
1
answer
109
views
How to add every Sunday in sequential order with 3 blank cells under each date?
How to add every Sunday in sequential order with 3 blank cells under each date?
For example:
March 22
Blank cell
Blank cell
Blank cell
March 29
etc etc
- 21