Questions tagged [google-sheets-dates]
Use for questions related to how Google Sheets handle dates, times, durations.
281
questions
3
votes
1
answer
76
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
0
votes
1
answer
38
views
Sheets date formula help
Im trying to find a way to input a due date associated with a task with a column next to it with check marks that can clicked once the task is complete, if it doesnt get checked by the due date I'd ...
- 1
0
votes
1
answer
182
views
Google Sheets format a date into a completely different date [duplicate]
I am trying to format a list of dates in google sheets. One of the dates are 20161001, I highlight the cell and go up to Format-> Number -> More Formats -> More Date and Time Formats and choose DD MMM ...
- 1
2
votes
2
answers
481
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 ...
- 33
0
votes
2
answers
951
views
Google Sheets count consecutive dates
I have a Google Sheet with a list of dates & times like this:
10/24/2019 4:25:00
10/29/2019 4:33:22
10/30/2019 4:33:11
10/31/2019 4:24:11
11/1/2019 4:36:11
11/2/2019 5:21:37
11/4/2019 5:30:20
11/...
- 103
7
votes
2
answers
17k
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 ...
- 173
0
votes
1
answer
27
views
Add year (multiple different years) in cells that have only day and month (Google Sheets)
I have a spreadsheet as follows in column B:
10/11
27/10
05/05
01/01
24/12
11/07
03/03
27/11
15/10
The top ones are always from the year that we are, in this example it's 2019, but according to what ...
- 149
1
vote
1
answer
2k
views
How do you interpolate data in a horizontal range that's non-linear?
I've been told what I'm hoping to do is called 'spline interpolation'.
I've imported monthly youtube channel viewership figures from google API. And I've layed out the dates and views in a horizontal ...
- 11
0
votes
2
answers
2k
views
Convert from duration ( HH-MM-SS) to minutes
I want to convert from duration ( HH-MM-SS) to minutes, but the result return only correct when duration of hour < 24 hour with function HOUR(). Otherwise result is not correct.
Anyone can help?
...
1
vote
0
answers
23
views
Using a query on an importrange, not pulling information
I am trying to do a query on importrange but it is only showing me the headers not any of the data. I am really stuck.
=QUERY(IMPORTRANGE("19Nx2EtMktFW-xwNMjY4IiQpN33QHrXIwcqDsvKqCiHo", "MASTER!A1:...
- 11
1
vote
1
answer
74
views
How to determine which working day of the month today is?
Using Google Sheets, I need to know what the numeric working day of today is. Somehow I'm just missing something here.
I can already easily determine the number of working days remaining, with
=...
- 1,009
1
vote
2
answers
3k
views
Why does close price results have a date shift (one day off) while using GOOGLEFINANCE in a Query
I want to get last 40 days close price of stocks.
I used below formula for the same but the result not showing the last day close price.
=QUERY(
GOOGLEFINANCE("nse:"&B2,"all",$A$2-80,81),...
- 11
2
votes
1
answer
55
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
0
votes
1
answer
1k
views
search/match a date on Google Sheets using Google script and copy a source range to a destination range OFFSET from that date
UPDATE: I FIGURED IT OUT.
I'm struggling with some simple copy/paste scripting in Google Sheets. I'm a complete scripting newbie. Advanced at Excel/Sheets.
I have a "Today" data entry sheet. I ...
2
votes
1
answer
2k
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 ...
- 21
2
votes
1
answer
492
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 ...
- 23
0
votes
1
answer
176
views
How to give a column a green border if the second row's holds a date = TODAY()?
I'm trying to make a useful schedule sheet to keep track of all of my class assignments:
I want to write a script in the script editor that says,
for(Column A-Z){
if(A2,B2,etc = TODAY()){
...
- 101
2
votes
1
answer
2k
views
How to sum amount with a criteria separately by day in Google Sheets?
I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month.
The file for monthly account contains a couple of sheets - each ...
- 21
-1
votes
3
answers
717
views
How to use Importrange in a Query and filter by dates
I am trying to IMPORTRANGE if the condition in column1 that is text formated ( Column1 = September 4, 2019 alas 5:10 pm ) contains the word September.
I am using the following formula. The other ...
0
votes
1
answer
49
views
How to copy values that have just been deleted into 'Archive' Sheet?
Setting up so any expired date rows get deleted. But I would Like to Delete and Move to Archive tab (sheet).
Have tried taking other codes and integrating but cannot find the code.
function ...
1
vote
1
answer
2k
views
Google Sheets - How to count the occurrence of each day of the week if they are not public holidays?
I would like to count the occurrence of each day of the week in a month if they are not national holidays.
In more details, I would like to know how many Mondays, Tuesdays, Wednesdays, Thursdays, and ...
- 121
0
votes
2
answers
159
views
Google Sheets duration format does wrap around 24
If I format the cells of a sheet to duration, it wraps around 24h
So adding those three cells (which should represent HH:MM):
05:00
18:00
02:00
with SUM, the result is 01:00 where I need the result ...
- 101
0
votes
2
answers
782
views
Using TODAY function with QUERY
I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output."
...
1
vote
2
answers
5k
views
How to countif with dates before today's date that auto updates
I am trying to use countif to count a certain number prior to today's date. So if today's date is 8/15/2019 the formula would count the number that is the B column that had a corresponding date prior ...
- 11
0
votes
1
answer
147
views
Query runs but returns first row greater than the DATE at 00:00:00 not the row after 10:00:00
Trying to use the following query:
=query(WABrdg729!A:B, "select A, B where A > date '"&text(datevalue($A$6), "yyyy-mm-dd")&"' AND A > timeofday '"&text(timevalue($A$6), "hh:mm:ss"...
- 11
1
vote
1
answer
1k
views
Google Sheets Calendar Monday start of week
I don't want my weekday to start on a Sunday, who does? I want it to start on a Monday like most normal people, but impossible to find a way to do this. It's Ok on Google Calendar, you go to 'settings'...
- 11
1
vote
1
answer
62
views
Returning max value based on two conditions
I've been reading through a lot of other solutions to similar problems but am not able to resolve my issues.
I have a Google Sheet with two sheets I am working with:
Sheet 1
A list of contacts (...
- 13
1
vote
1
answer
22
views
Rank days of the month that occur most
I would like to know which days of the month customers have historically paid on the most. I would like to create a report that has days 1,2,3,4...31 all with a count next to each day for how many ...
- 35
1
vote
1
answer
36
views
Is there any way to dynamically modify dates based on number of days something would take?
Imagine you're creating a Google Sheet to track all of your tasks, how long they take, and their deadlines:
Task |Number of days| Start Date | Date Done
Buy plane tickets | ...
- 125
1
vote
1
answer
84
views
Google sheets spreadsheet will not format date correctly
I have a Google spreadsheet with a date column formatted to dd mmm yyyy. Many of the dates are formatted this way, but some cells will not format this way, and no matter what I select under "Format", "...
2
votes
1
answer
86
views
Google Sheets date conditional formatting
I have dates in column D in Google Sheets. I want the cells to highlight red if 30 days have passed and there is not a comment date in column F.
Example:
Column D: Submittal Date 6/29/2019
...
1
vote
1
answer
4k
views
Adding date picker to Google Sheets cells while data validation is customized
I have a column with customized data validation (custom formula) that accepts only dates, provided that a few other conditions are met.
How can I add a date picker to the cells of this column? ...
- 123
1
vote
1
answer
158
views
Google Sheets time sorting chart
I have a MailChimp data export in google sheets and I want to create some charts that can show the best time of day/day of the week based on my data for our mail outs when compared to open rate (%). I ...
Dave Kempton
2
votes
1
answer
331
views
Dynamically creating column names based on data in another tab
I have a Google sheet that logs all submissions on my website of various contact forms. I need to know how many times each contact form has been submitted, and in which week they were submitted.
The ...
- 23
0
votes
1
answer
276
views
Replacing data in Google Sheets
I am new to coding! I have data in Sheet 1 of a Larger Spreadsheet that looks like this
25.06.18
and wish to change it to look like
26/06/18
I have tried to write a macro:
function Dated() {
...
- 1
0
votes
1
answer
102
views
SUM weekly values according to date in one column A and values in column B
As shown in the screenshot, I have values I am looking to add for weekly totals to track, however, this is very tedious having to create a new code for every week, especially as the weeks change per ...
- 1
1
vote
1
answer
3k
views
Google Sheets Pulling data between a specific date and time, from a timestamp
I am trying to pull specific data from a timestamp that I will be using in several pivot tables.
Here is my workbook https://docs.google.com/spreadsheets/d/1iYHYdATK06Wxl-Jtxe2P92Kh50XEsWgwx5d-...
1
vote
1
answer
49
views
ArrayFormula not showing 0s
I use the following formula to calculate plain text so to show leading zeros.
=ARRAYFORMULA(SUM(S2:S17+0))
but it's only displaying the results without the leading zero.
Example, if It is adding up ...
- 49
1
vote
1
answer
25
views
Adding mm:ss times when expressed as TEXT
I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets.
The numbers are supposed to be minute second. mm:ss. For the ...
2
votes
2
answers
77
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 ...
- 216
2
votes
1
answer
567
views
Google Sheets color row if date is closest to today's date
If possible, I would like to have a color schema applied to the table.
For instance, the cell that has the closest date to today will be green
The cell that has the furthest date to today will be red....
- 61
1
vote
1
answer
19
views
If a cell equals to this date, then filter through another sheet and grab that cell that has the same date
When you input a date in sheet 1, I want it to automatically grab data from sheet 2 and input text that matches that date back into sheet 1
- 61
1
vote
1
answer
183
views
IMPORTRANGE from a separate sheet based on closest date to today's date
Sheet1 will have data with different past dates
Sheet2 - I want to import data from the 1st sheet and find the closest date to today's date.
https://docs.google.com/spreadsheets/d/...
- 61
1
vote
1
answer
1k
views
Google Sheets Conditional Formatting not working for all cells
This is my first question using StackExchange, so please bear with me. I am making a spreadsheet for tracking due dates for training. Sheet 1 shows due dates that people with view only capabilities ...
- 13
2
votes
1
answer
55
views
Date highlighting of a cell, separated by weeks, given only a starting date and a cell range
I am trying to build a "life calendar" similar to this - https://labs.coruscantconsulting.co.uk/life/weeks/ - in Google Sheets.
I have the manually designed sheet finished, but want to add the ...
- 23
1
vote
1
answer
26
views
How do I refer to data in a column under this month?
In the below screenshot, I'm trying to get B42 to show the value of whichever cell on row 40 is under the current month's column:
As the dates are automatically generated using a transpose(...
- 13
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 ...
- 311
1
vote
1
answer
27
views
Looking for a way to get the current date represented as a numbered month with decimals
Essentially I'm hoping to find a formula that would return the current date as a month in decimal form. So if it's October 15th, the number returned would be 10.48. September 10th would come back as 9....
- 13
0
votes
1
answer
48
views
What function formula should I use to calculate my salary so that I don't have to differentiate between Friday and weekend manually?
These are the formulae I am currently using where I have to manually sort out if its Friday or weekend by using conditional formatting to bring attention to the minority that needs a different formula....
2
votes
2
answers
321
views
Boolean test of whether a time is before or after midnight in Google Sheets?
I'm having a job trying to create a Google sheet which calculates the difference between the time I fell asleep and the time I woke up. Here's what I want to happen:
Sleep time Wake time ...
- 215