Skip to main content

Questions tagged [google-sheets-dates]

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

Filter by
Sorted by
Tagged with
111 votes
14 answers
282k 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 ...
Stefano Palazzo's user avatar
86 votes
6 answers
219k 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 ...
Sam Bisbee's user avatar
45 votes
4 answers
90k 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 ...
Santiago's user avatar
  • 561
38 votes
9 answers
272k 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 ...
stim's user avatar
  • 541
37 votes
5 answers
70k 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 ...
Mike Ellis's user avatar
31 votes
2 answers
58k 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 ...
agold's user avatar
  • 447
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 ...
JVC's user avatar
  • 1,246
20 votes
2 answers
29k 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?
Robert Munteanu's user avatar
15 votes
6 answers
32k 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 ...
lineplay's user avatar
  • 253
15 votes
1 answer
104k 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?
francis fernandes's user avatar
14 votes
2 answers
36k 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 ...
kristen maul's user avatar
13 votes
4 answers
31k 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 ...
Ryan's user avatar
  • 774
11 votes
4 answers
27k 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 ...
Dr-Bracket's user avatar
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 =...
pcdr's user avatar
  • 103
10 votes
9 answers
28k views

How can I format time as 12-hour but without the period indicator?

I am trying to display times in Google Sheets without an AM/PM indicator, but with 12-hour time (not 24-hour "military-style" time). I do not need the AM/PM indicator as it is redundant in my ...
Laurence Gonsalves's user avatar
9 votes
2 answers
17k 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 E7, then in Column B I want the first row to be the value from E7 and then all the rows below that to be incremented by exactly one month from the ...
Eabryt's user avatar
  • 217
9 votes
4 answers
21k 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. ...
zeckdude's user avatar
  • 191
8 votes
3 answers
39k 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?
sashoalm's user avatar
  • 655
8 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/...
Joshua Dance's user avatar
  • 1,092
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? ...
jamesmstone's user avatar
7 votes
3 answers
6k 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, ...
Cam Jackson's user avatar
6 votes
3 answers
32k 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] ...
Elad Ratson's user avatar
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 ...
user avatar
6 votes
2 answers
15k 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.
dodo254's user avatar
  • 161
6 votes
2 answers
7k 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 ...
Travis Manning's 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 ...
techturtle's user avatar
  • 1,040
5 votes
3 answers
13k 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 ...
Jason's user avatar
  • 51
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 ...
Brian King's user avatar
4 votes
2 answers
170 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 ...
Katherine Anderson's user avatar
4 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, ..., ...
user565739's 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 ...
JohanHorak's user avatar
4 votes
0 answers
746 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 ...
Todd Powers's user avatar
4 votes
3 answers
547 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 ...
Cassandra's user avatar
3 votes
1 answer
654 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: ...
Jernej Pangeršič's user avatar
3 votes
1 answer
664 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 ...
FloatingRock's user avatar
3 votes
3 answers
14k views

How do you get current date to be added to a cell when a check box is checked in another cell

I want to get the current date added into a cell when a tick box is checked in another cell. I currently have another formula in the date cell which changes its colour when the tick box is checked, ...
Christopher Grant's user avatar
3 votes
2 answers
129 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 ...
user256515's user avatar
3 votes
1 answer
84 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,"...
user233395's user avatar
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 ...
Mike's user avatar
  • 31
3 votes
2 answers
15k 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 ...
Cory Hooper's user avatar
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 ...
B Seven's user avatar
  • 517
3 votes
1 answer
164 views

Making a bubble chart in Goggle Sheets for year-on-year comparison - ie year versus month?

I'd like to make a multi-series bubble chart from 17 years of sporadic data with Year on the Y axis and Month+Day on the X axis and the bubble size corresponding to each data point's value. I've ...
flyhigher's user avatar
3 votes
2 answers
3k views

Conditionally format if date is same/greater or smaller than current date

I'm trying to get my Google sheet to automatically colour a cell red if the current date is the same or greater than the date in the cell. Example: today is 21/02/2020. The date in the cell is 25/...
Callum's user avatar
  • 31
3 votes
1 answer
538 views

How to access the "unformatted" date in Google Sheets?

I am working with a spreadsheet that includes a row where each cell contains the date of the first day in each month in a series of years: 1-Jan-18, 1-Feb-18, 1-Mar-18, ... , 1-Dec-18, 1-Jan-19, 1-Feb-...
Jim's user avatar
  • 183
3 votes
2 answers
9k 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-...
FVA's user avatar
  • 33
3 votes
1 answer
382 views

Filter date, and add blank rows between different time slots

I'm trying to build a reservations spreadsheet for my friend's restaurant where she can input on monthly sheets as resos come in, then see a filtered, sorted summary of any selected day. Here is a ...
Emma's user avatar
  • 31
3 votes
1 answer
108 views

Round up duration calculation to the next multiple of five minutes

I currently have this formula: =text(D2 - C2, "hh:mm:ss") I need this formula to round up to the nearest 5 minutes. I have tried mround(), ceiling() and roundup(), but I'm not getting the ...
Cate Kemp  SBFO's user avatar
3 votes
1 answer
369 views

Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day: DATE | WHAT | HALF? ... 2021-4-1 | Something Something | false 2021-4-2 | Something Something | false ...
User1291's user avatar
  • 143
3 votes
2 answers
534 views

How to make sequential list of months?

I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
Elliott B's user avatar
  • 266
2 votes
5 answers
808 views

How to get Sheets to recognize dates that are entered without any separators?

I want to enter dates in yyyymmdd format, but have Google Sheets format the values as yyyy-mm-dd. I don't want to enter any dashes (or slashes, or spaces) because it slows down my data entry, but ...
Alisa's user avatar
  • 129

1
2 3 4 5
7