The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [google-sheets-dates]

Use for questions related to how Google Sheets handle dates.

39 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
3answers
53 views

Conditional formatting for columns, date & time specific?

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this: 2018-11-01T20:16:52 I have tried multiple approaches to sorting ...
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 ...
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 ...
1
vote
1answer
30 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),...
1
vote
0answers
36 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
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 ...
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?
1
vote
1answer
158 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
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
2answers
286 views

How do you get current date to be added to a cell when a check box is checked in another cell

I want to get the current date added into a cell when a tick box is checked in another cell. I currently have another formula in the date cell which changes its colour when the tick box is checked, ...
1
vote
1answer
31 views

Insert a weekday in Google Forms' date selector

We have created a Google form that works perfectly, there is only one issue, and that's the DATE selector, it sends the DATE, but we would like to receive the DATE and the WEEKDAY in the form we ...
1
vote
1answer
115 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 ...
1
vote
1answer
2k views

How to set Monday as start day of the week in Google Sheets date picker

How to set Monday as start day of the week in Google Sheets date picker? I have changed the locale, changed start day of the week in Google Calendar and can't find a way to solve this.
1
vote
1answer
61 views

How to calculate years left in Google Sheets

This is the function I used and it work =YEAR(A1)-YEAR(B1), A1 is today B1 is the date in the future or past. The problem is that I want to round up year and months. This is the exaple: https://imgur....
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 ...
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()){ ...
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", "...
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:...
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 ...
0
votes
1answer
23 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 ...
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....
0
votes
0answers
12 views

sSheet DateTime format

I am using AppSheet to create a Timesheet App and on the App, I have to use the DateTime format to be able to calculate overtime and regular hours correctly. My gSheet DateTime format returns a start ...
0
votes
1answer
19 views

INDEX + MATCH to return the greatest of all matching values

I'm looking for the most recent date corresponding to a particular name. Dates are not necessarily ordered. I can't sort the target, I need it to work in any order. Example: When was [Roberto]'s most ...
0
votes
1answer
10 views

Creating a formula based on TODAY's day

I have column A (list of stock prices at close of day) A1=4/1/2019 | stock price A2=4/2/2019 | stock price A3=4/3/2019 | stock price each day will be added to the sheet automatically I want to take ...
0
votes
1answer
113 views

Google Sheets adding weird number in 11th decimal place

Can anyone explain this? I have 18 hardcoded numbers - that is, typed in, not as a result of a formula. [B1 thru B18] I have a cell [D1] that sums those 18 numbers. I have eleven other hardcoded ...
0
votes
0answers
37 views

Auto date cell when opposite cell is populated - problem with script

This question has been asked here and I'm posting a new post because I couldn't figure out how to reply to that one. I installed the script and saved it but no date appears in my sheet when I insert ...
0
votes
0answers
215 views

How to calculate DATE DIFFERENCE in GOOGLE Sheets?

I have a google sheet with NHL schedules (hockey). I want to calculate the difference between two games of a specific team. Example: Nashville played on 2018-11-12 and last game before was 2018-11-10 ...
0
votes
1answer
935 views

Date comparison and DATEVALUE with dd.mm.YYYY. date format

I'm using the custom date format for one of my columns, dd.mm.YYYY.. I want to compare that date to TODAY(), by using +AND($D2 < TODAY()) where column D is the one with the dates in dd.mm.YYYY. ...
0
votes
1answer
77 views

Google sheets query multi conditional date cell ref

I have a doubt regarding a change in this formulas in sheets: =INDEX(QUERY($A$3:$H; "SELECT sum (D) where C='Pub'");2) =INDEX(QUERY($A$3:$K; "SELECT sum (F) where C='Reg'and K='"& K15&"'");...
0
votes
1answer
45 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
2answers
237 views

How to make date automatically display only day number and suffix (1st, 2nd, etc.)?

Title explains it. I need to know how to create a custom number format in which a cell would display only the date and suffix of a full date entered into the cell example:1/05/2019 -> 5th I have ...
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 ...
-1
votes
1answer
1k views

In Google sheets, how to calculate elapsed duration between two cells, each containing date and time and display as days and hours

There is a built-in duration format, but Elapsed days is not an option. I want to display "2 days, 3 hours"
-1
votes
1answer
14 views

DAY function does not work as expected in google sheet

I have a issue with Day function; I am extracting date from text string and storing it in a column; say Sep 30, or Sep 23; I want to see DAY of this date and put it in next column; something like ...
-1
votes
1answer
1k views

How to use Now() to subtract from column of date/time to get hour difference

I have searched a bit and can't figure this out. I've tried using Now() in the formula along with putting it in a cell and running the formula against it. I have a column of date/times. I would ...
-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 ...