Questions tagged [google-sheets-dates]

Use for questions related to how Google Sheets handle dates.

Filter by
Sorted by
Tagged with
1
vote
1answer
11 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-...
3
votes
1answer
37 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,"...
0
votes
1answer
14 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 ...
0
votes
1answer
11 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
vote
2answers
42 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 ...
2
votes
1answer
48 views

Google Sheets - 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 ...
0
votes
1answer
10 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 ...
0
votes
1answer
58 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 ...
0
votes
2answers
15 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
0answers
11 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:...
1
vote
1answer
53 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
vote
1answer
36 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),...
2
votes
1answer
39 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
0
votes
1answer
55 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 ...
1
vote
1answer
85 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 ...
2
votes
1answer
28 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 ...
0
votes
1answer
24 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()){ ...
2
votes
1answer
26 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 ...
0
votes
3answers
68 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
1answer
31 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
1answer
53 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 ...
0
votes
2answers
30 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 ...
0
votes
2answers
36 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
1answer
159 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 ...
0
votes
1answer
21 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"...
1
vote
0answers
52 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'...
1
vote
1answer
28 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 (...
1
vote
1answer
14 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 ...
1
vote
1answer
30 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 | ...
0
votes
0answers
12 views

In Google Sheets script how do you email a date in a cell and control how it looks in email?

I'm building a Sheets Script add-on for sending invoices. In my sheet, I have a chart with the dates written along a row. When I use the MailApp.sendEmail() the date is printed in the email ...
0
votes
1answer
29 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
1answer
30 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 ...
0
votes
0answers
42 views

IMPORTFEED Convert date format

I have a Google sheet that imports an RSS feed using IMPORTFEED and also the date of each entry using "items created" =IMPORTFEED("url", "items created") The date is shown as Sat, 13 Jul 2019 03:17:...
1
vote
1answer
228 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? ...
1
vote
1answer
16 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 ...
0
votes
0answers
35 views

Google Sheets. Adding timestamps for multiple sheets in a workbook

Here is a copy of my current workbook https://docs.google.com/spreadsheets/d/1S7V4Gkgr_1DiP5sMQ3EWYffvRi4S0b-C7G0O6MGejY8/edit#gid=442994829 Right now i am using a script that performs a timestamp ...
2
votes
1answer
16 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 ...
1
vote
1answer
16 views

Output data if value is less than today's date

I have a spreadsheet. I want to create a formula that checks the date of one cell and if the value is lower than today's date, it to output the word required into another cell. How would I do this?
0
votes
1answer
61 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() { ...
0
votes
1answer
33 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
vote
1answer
457 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
1answer
14 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 ...
1
vote
1answer
13 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 ...
1
vote
2answers
47 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 ...
1
vote
1answer
55 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....
1
vote
1answer
12 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
1
vote
1answer
51 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/...
1
vote
1answer
313 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 ...
2
votes
1answer
25 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 ...
1
vote
1answer
20 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(...