Questions tagged [google-sheets-dates]

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

Filter by
Sorted by
Tagged with
1 vote
1 answer
2k views

Finding the longest time duration in Google Sheets

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time. =LARGE(...
Marcus's user avatar
  • 21
110 votes
14 answers
278k 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
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() ...
Charles's user avatar
  • 21
29 votes
2 answers
53k 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
  • 427
20 votes
2 answers
28k 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
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/...
Joshua Dance's user avatar
  • 1,052
13 votes
2 answers
31k 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
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
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
1 vote
1 answer
3k 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 ...
Matt3o12's user avatar
  • 113
36 votes
9 answers
256k 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
  • 521
15 votes
6 answers
30k 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
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 ...
Travis Manning's user avatar
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, ..., ...
user565739'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
2 votes
1 answer
695 views

Changing a date in google sheets from MM/DD to DD/MM without changing the values

I have a survey data set where respondents DOB was entered as "DD/MM/YYYY". Sheets has formatted some of the cells as "MM/DD/YYYY", and where that wouldn't make sense because the day is greater than ...
Gumboots's user avatar
1 vote
1 answer
1k views

Sum all values with same month and category

So I have this table where I'm writing down my daily expenses. On one cell I choose the date, the next cell I choose the category and in the third, I insert the amount of money spent. On another sheet ...
Bruno's user avatar
  • 13
7 votes
3 answers
3k 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
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 ...
Jason's user avatar
  • 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 ...
techturtle's user avatar
  • 1,040
4 votes
2 answers
10k 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 ...
Eabryt's user avatar
  • 167
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-...
FVA's user avatar
  • 33
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 ...
Cory Hooper's user avatar
3 votes
1 answer
650 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
580 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
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 ...
Joshua Fox's user avatar
2 votes
2 answers
668 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 ...
Plot Device's user avatar
2 votes
1 answer
556 views

Drag to fill/autocomplete dates only increases year, not day

Please see screenshot below. When I dragged on the blue square in highlighted cell, I only get the result below. I have set correct region in sheet settings. I have set the correct date format in the ...
James T's user avatar
  • 21
1 vote
1 answer
138 views

How to automatically fill, sort and conditionally format 1st day of the week according to given year and month

How can I fill-in column A, make it start with the day's name according to the year in A2 and month in A1 and conditionally format them? https://docs.google.com/spreadsheets/d/...
Hassan Almaateeq's user avatar
1 vote
2 answers
2k 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 ...
Sharkfin's user avatar
1 vote
1 answer
445 views

Query/Filter function finding the closest date that is less than the order date for an array of dates using a given variable

Overview I need to get the cost of something by comparing item dates against an order date for one or more of the same item with different item dates, and making sure to select the item date that is ...
TheDT's user avatar
  • 25
1 vote
1 answer
6k 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" ...
user105301's user avatar
1 vote
1 answer
29 views

Show most recent date a theatre is visited Google Sheets

I visit 4 theatres regularly, sometimes multiple theatres on the same day but never the same theatre on the same day. I have columns A, B and C in the picture attached. Column A records the date a ...
CellbyDate's user avatar
0 votes
1 answer
585 views

How to freeze Google Sheet columns based on date

I am using Google Sheets, and my columns are set up for each month (Jan, Feb, March, etc). I have several rows that are pulling data from other tabs in the sheet to create an at a glance master list. ...
Meghan C's user avatar
0 votes
2 answers
1k views

Conditional Formatting for cells that are within 1 hour of the date and time posted within a cell

I have a Auction that I am updating and posting bids to on a Google Sheet that bidders can view. The condition to the auction is that the item will be sold to a bidder if no one has bid on said item ...
Akito Tennouji's user avatar
0 votes
2 answers
1k views

How to normalize different date formats when importing in google sheets

Background The problem I'm solving is mentioned here in detail. Another glitch is that the spreadsheet I'm reading from has random changes to the date format like so: 10/3/2021 11/3/2021 12/3/2021 ...
abbood's user avatar
  • 1,179
0 votes
1 answer
142 views

Google Sheets VLookup Help

So, I am trying to use the VLookup function to find the value of a cell that corresponds with todays date and return that cell information. However, if there is no date given for todays date, then I ...
TheTechZombie's user avatar
0 votes
0 answers
105 views

Google Sheets, SumIf date in cell matches another date

I have two sheets one with date/times, and names in columns that gets populated automatically from a form submission, and a second one that has dates only across the columns with names down the rows. ...
Keegan Miller's user avatar
0 votes
0 answers
245 views

QUERY function with date range not working and shows wrong results

I have been working on creating a personal finance spreadsheet and from my experience of using QUERY previously to create dashboards, I thought I would have no issues with this but I was wrong! The ...
Ara's user avatar
  • 1
0 votes
1 answer
384 views

Query or filter the data when using date time format

Example sheet. Sheet2 In cell A2 I have: =QUERY(Sheet1!A1:Q, "Select * where B >= datetime '"&TEXT($A$1,"MM-DD-YYYY HH:mm:ss")&"'") In cell A1 I have: 6/17/...
KB John's user avatar
0 votes
1 answer
6k views

Can I sort rows in Google Sheets by date?

In Google sheets I'm trying to sort rows according to a date column. According to my experience I can use column filtering and choose to sort data from A-Z. However this will give a wrong result for ...
Ben Carp's user avatar
  • 101
0 votes
2 answers
821 views

Google Sheets interpreting dates from CSV in a weird way

I'm opening a csv report in Google Sheets. The Google Drive preview shows the dates like this: When I open this file in Google Sheets the dates now look like this: And if I try and convert the date ...
Westicle's user avatar
0 votes
3 answers
129 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 ...
woodie OS's user avatar
-1 votes
1 answer
2k 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 ...
Rubén's user avatar
  • 44.6k