Questions tagged [google-sheets-dates]

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

Filter by
Sorted by
Tagged with
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,"...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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/...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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? ...
user avatar
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:...
user avatar
  • 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 =...
user avatar
  • 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),...
user avatar
  • 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
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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()){ ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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." ...
user avatar
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 ...
user avatar
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"...
user avatar
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'...
user avatar
  • 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 (...
user avatar
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 ...
user avatar
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 | ...
user avatar
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", "...
user avatar
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 ...
user avatar
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? ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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() { ...
user avatar
  • 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 ...
user avatar
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-...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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....
user avatar
  • 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
user avatar
  • 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/...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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(...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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....
user avatar
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 ...
user avatar
  • 215