Questions tagged [google-sheets-dates]

Use for questions related to how Google Sheets handle dates.

Filter by
Sorted by
Tagged with
1
vote
1answer
49 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
28 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
36 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
44 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
61 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
24 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
21 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
23 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
40 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
29 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
37 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 ...
-1
votes
1answer
23 views

Add Two Weeks (Weekdays) on Data Validation

I am needing to add two weeks on to date in a separate column. In one column (A) I have the "Date Requested" and in the next column (B) I have "Due Date" I need the due date column to have the "Date ...
0
votes
2answers
25 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
33 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
44 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
20 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
35 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
27 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
29 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
11 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
27 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
27 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
32 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
97 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
13 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
26 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
14 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
44 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
21 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
143 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
43 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
35 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
35 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
180 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
24 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
19 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(...
1
vote
1answer
93 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 ...
1
vote
1answer
16 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....
0
votes
0answers
19 views

Issue with SUMIFS pulling data from QUERY across multiple sheets

I would like to sum values from a QUERY generated list in the sheet I/S/N/W to the sheet BUDGET for determining a value based on the date in BUDGET!B8. The function in BUDGET!B8 is =MONTH(TODAY()). ...
0
votes
1answer
24 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....
1
vote
1answer
26 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 ...
0
votes
0answers
19 views

Looking to open a timesheet in Google sheets and auto seek the current date [duplicate]

My employer has recently transitioned from excel to Google sheets as our timesheet platform, and we're finding that every time the doc is opened or recovered from suspension, it resumes at the top. ...
1
vote
1answer
20 views

Link to a date cell in QUERY criteria

I have the following formula and it works fine =unique(query(ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY( {Database!A1:AB, (Database!N1:N)-(Database!M1:M)}, "where Col9 <> '' AND Col1 >= date'2019-...
1
vote
1answer
32 views

Time not formatting as expected in Google Sheets

I imported a report to Google Sheets and the duration column shows 7 h 37 m or 14 h 5 m a similar format. When I replace h with : it still does not properly format to time duration and so I cannot ...
0
votes
1answer
20 views

Google Sheets Pivot Table, Summary by Day

I have a sheet with 2500 dates & times of when customers have placed their order. I want to do a heatmap or a bar graph to show which days of the week are the busiest. I rounded the exact times ...