Questions tagged [google-sheets-dates]

Use for questions related to how Google Sheets handle dates.

Filter by
Sorted by
Tagged with
45
votes
4answers
100k views

How to add days to a date

I'm using a Google Spreadsheet to perform calculations and I'd like to do some simple date math. For example, TODAY() add 7 days. I don't see any functions to do it and some quick stabs in the ...
26
votes
4answers
58k views

Add 3 hours to Date value (DD/MM/YYYY 00:00:00)

I have cells with dates in this format: DD/MM/YYYY 00:00:00 and I want to add 3 hours because I'm in a different GMT zone. Is possible to use dates in a formula? Something like: 30/12/2012 22:15:00 ...
17
votes
9answers
87k views

How to Fill a Column with Sequential Dates in Google Sheets

In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
3
votes
3answers
12k views

Calculate days between now and a future date

I need to calculate how many days are left from today to a certain date in the future in Google Sheets. How can I do that?
3
votes
1answer
29k views

Compare date value to current date in Google Sheets

How can I compare the date in a row to a current date, so that if it's greater than today the next steps in my process need to be executed?
3
votes
1answer
9k views

Entering mm:ss: milliseconds into Google Sheets for time duration (arithmetic)

I would like to enter times mm, seconds, milliseconds into Google Sheets and subtract times to determine duration. I use format> numbers > More formats > more dates and times > and apply mm:ss: ms. I ...
2
votes
1answer
4k views

Dynamic date range query

I'm trying to create two cells that will update my query: A >= date"A1" and A <= date "B1". My goal is to create a search page for the data, where the end user could insert a date or the =today() ...
2
votes
2answers
3k views

Time query in Google Sheets

I've started using Google Sheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the start time ...
2
votes
1answer
14 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
14 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 ...
2
votes
1answer
21 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 ...
2
votes
1answer
40 views

Add missing dates to list of dates with values

Current Data: 9/20/2018 6 9/22/2018 3 9/23/2018 1 Desired Output: 9/20/2018 6 9/21/2018 9/22/2018 3 9/23/2018 1 I would like to have the missing dates added to Column A while the ...
2
votes
1answer
16 views

How do I create multiple time zones by using the NOW formula?

I'm trying to create 5 cells with different time zones: CDT MDT MST PDT EDT I am in MST, so I obviously get my current time by doing NOW() How do I add hours to that? I tried NOW()+(2,0,0) But ...
1
vote
2answers
38 views

How do I add dates for the beginning and end of the week in one cell

I know very little about Google Sheets and all I want to do is create a cell that will display the beginning and end of the week, like this: 7/22-7/28. I'd like to be able to drag this across ...
1
vote
3answers
2k views

Convert Text String into Date/Time Value

I'm fetching data into a Google Sheet. The date column contains multiple cells with the following text string pattern: Mon Mar 11 19:05:32 +0000 2019 The pattern always repeat itself: [day] [month] ...
1
vote
2answers
494 views

Display date of each weekday of current week

I have seven spreadsheets, one for each day of the week. I am trying to get each one to display their date for that week. So for example, this week Monday would display 7/16/2018 and the Tuesday ...
1
vote
1answer
19 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
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
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....
1
vote
1answer
20 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 ...
1
vote
1answer
29 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 ...
1
vote
2answers
112 views

How to return values in a formula, from rows of latest 4 nonconsecutive dates recorded

Using an ARRAY_CONSTRAIN formula we want to capture the latest 9 records (rows) over a period of nonconsecutive dates. =ARRAY_CONSTRAIN(sort(vlookup(query({row(EBPP!D:D),sort(EBPP!A:E)},Q15,0), {...
1
vote
2answers
23 views

Change cell colour based on another cell containing text (day name)

I am new to Google Sheets and trying to create a custom formula in conditional formatting to make the fill colour of a cell change based on another cell containing a specific text. I want to change ...
1
vote
1answer
9 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
30 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
10 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
26 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
12 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
42 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
17 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
21 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
49 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 ...
1
vote
1answer
16 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
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
2answers
41 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
39 views

Query select all values of specific month

A have a table where Column A has dates. I want to get all values of a specific month I have the following query for May for example =query(Database!A1:AD,"Select A,E,F,U,V,W,X,AB,AC where Month(A) =...
1
vote
1answer
10 views

Set certain days of the week on repeat [closed]

I'm making an attendance sheet for my team, how do I make only certain days like Mon Tue Wed appear in the top column?
1
vote
1answer
24 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
175 views

Query(Importrange) or Query with dates

I am trying to show or limit what is seen to just entries made in 2019. I found a few places that referenced this and have tried the following without success. =query(IMPORTRANGE("1bSyor-...
1
vote
1answer
34 views

Subtract 8 hours from time, if time is more 8 hours

I am trying to create a Timesheet using Google Sheet but this formula =IF((F8>8),(F8-8),"0") returns zero even on cells with Total Hours greater than 8 I have set both Formats to Duration
1
vote
4answers
100 views

How to add every Sunday in sequential order?

So I tried using the simple formula: =date(2019,3,10) =date(2019,3,10)+7 =date(2019,3,10)+14 =date(2019,3,10)+21 ... etc. But when I highlight the first top 3-4 cells to drag down and formulate ...
1
vote
1answer
51 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
128 views

Calculate Work Hours

Given work hours for each weekday (let's say 9:00:00-17:00:00 Monday through Friday), a start timestamp (e.g., 1/15/2019 6:15:15) and end timestamp (e.g., 1/21/2019 14:35:10)... How can I get the ...
1
vote
2answers
36 views

Query using date range doesn't work for specific data

I have a spreadsheet (URL below). I've set up a query to make sure I understand how to do a query with a date range. Then I tried to create a similar query on the "real" data. The "Week 1" worksheet ...
1
vote
1answer
74 views

Sum all values with same month and category

So I have this table where I'm writing down my daily expenses. On one cell I choose the date, the next cell I choose the category and in the third, I insert the amount of money spent. On another sheet ...
1
vote
1answer
164 views

MIN and SUM functions in Google Sheets for Time

I'm trying to use the MIN and SUM function but the answer always comes back with either 0, ERROR or !DIV/0. The format I want to have for my times are for example: 0.03, 10.56, 1:13.00. =MIN example:...
1
vote
1answer
852 views

Convert inputted time duration to minutes:seconds [duplicate]

I'm working on this spreadsheet, and I wish to convert the data in columns C, D, and E from hours and minutes to minutes and seconds. For example, an inputted 7:08 turned into 07:08:00.000 (hours and ...
1
vote
1answer
984 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
25 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....