Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [google-sheets-dates]

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

Filter by
Sorted by
Tagged with
-2 votes
1 answer
14 views

More computational capabilities per spreadsheet

I am a pro user of Google Sheets and Google Apps Script. Is there any possibility to get more computing capability per spreadsheet? Some of my spreadsheets become unusable due to a large amount of ...
Piotr Magdziak's user avatar
1 vote
2 answers
26 views

Importrange Query importing correct OR statements, but not refining by date

I have a formula pulling relevant columns from a different document. It is working well except for the last part regarding the dates. It is seemingly ignoring that part of the formula and returning ...
Salt_Tea's user avatar
0 votes
0 answers
17 views

spreadsheet recognize my "time" input as "date" [duplicate]

This column (green) contain data with time format (HH.MM.SS),but for some reason, any time format with their minute below 12, converted into date format (probably because on date format, max month is ...
Vintikon Indonesia Perkasa's user avatar
1 vote
1 answer
25 views

Return the percentage of the month that has elapsed up to today's date

I want a formula to return the percentage of the month that has elapsed up to today's date. For example, if today is April 6, and April has 30 days, then the result would be 20%: 6 / 30 = 20% What ...
peterK88's user avatar
0 votes
2 answers
41 views

Specify the Phase based on the Dates

I have dates listed in Column A and I want to convert them to Phases in Column B; e.g. if the date is >= 5 Feb 2024 is equal to Phase 1 & if the date is >= 1 March 2024 is equal to Phase 2 ...
Deccal's user avatar
  • 3
0 votes
1 answer
20 views

Apply conditional formatting based on dates listed in row

I am trying to track support hours in a shared document and want to have the entire month turn red if the number of support hours allocated in the month exceed the client's allowance. The date of a ...
Christian Guest's user avatar
0 votes
1 answer
40 views

Google Sheets - conditional formatting - problem with DATE + 45 days

I am trying to setup a simple (I thought so anyway) formula using conditional formatting. I have one cell (B2 for example) that will have a date in it. Then another cell, lets say K2, we manually ...
Harold Weaver's user avatar
0 votes
1 answer
41 views

Convert US dates to international format in Google Sheets

I have a Google spreadsheet which I use to track my accounts. I need to add Etsy sales to this, and have added a new sheet which is pasted from the CSV supplied by Etsy. Unfortunately Etsy only ...
dansumption's user avatar
0 votes
1 answer
35 views

Google sheets - Look for a cell linked to a date in a different table and output another cell

I've been strugling with a problem for quite some time. I'm trying to look up the cell C13 in the top table to output the corresponding number in column A of that Table. So for 01/01 monday it would ...
kyra's user avatar
  • 3
2 votes
5 answers
592 views

How to get Sheets to recognize dates that are entered without any separators?

I want to enter dates in yyyymmdd format, but have Google Sheets format the values as yyyy-mm-dd. I don't want to enter any dashes (or slashes, or spaces) because it slows down my data entry, but ...
Alisa's user avatar
  • 129
1 vote
1 answer
369 views

Excluding specific range in Google Sheets

I have a range formula on a Google Sheet that pulls the next future date out from a column containing nothing but dates (column C), starting from the 4th row: =MIN(FILTER(ARRAYFORMULA(C4:C),C4:C>...
Chris Douglas's user avatar
1 vote
1 answer
39 views

Automatic way to select a range for a formula based on the date

I am creating a calculator where the user describes a list of tasks in Activities!B2:B, and in C2:C they rate 0-5. To separate each day, I've used a date for each task in A2:A (stylistically separated ...
Louis S's user avatar
  • 25
0 votes
1 answer
348 views

How to extract date and time in Google Sheets [closed]

I'm trying to extract the time and date from column A. In cell A1 for example is written: October 25, 2023 at 07:23PM In cell A2 for example is written: October 26, 2023 at 01:01AM I'm trying to ...
Dennis 's user avatar
1 vote
0 answers
20 views

Google Sheet Query function [duplicate]

I am just wondering if below code is possible to convert in Google sheet function QUERY(), please see query below: Query: SELECT * FROM tblDate WHERE Date IN ('1/1/2000','1/23/2001','3/1/1996','6/23/...
DodZ's user avatar
  • 11
1 vote
1 answer
57 views

If a cell is left blank, how can I get the formula result be blank

I am currently looking for a little help with Google Sheets. I currently have three columns, B 'DATE' - C 'TIME' - D 'Dropdown list - BUY, Sell, Proforma' So far, I have managed to do what I needed, ...
Steve's user avatar
  • 11
0 votes
1 answer
30 views

Selecting only rows corresponding to Thursday in a column of dates

I've the following dates in a column and I'd like to select only the rows corresponding to Thursday in Google Sheets. Could you tell me how to get this result, please?
Gennaro Arguzzi's user avatar
0 votes
1 answer
33 views

Fill a column with dates for two days per week

I need to keep track of an employee who works Mondays and Thursdays. I need to fill a column with dates for these days. I know how to do sequential dates and I have tried dragging down from the corner ...
user303371's user avatar
1 vote
1 answer
75 views

Adding (and updating) time stamp when cell color is changed in Google Sheets

I'm very new to scripts so please bear with me. I want to make a condition where if a cell color is changed, a new timestamp is created. So a cell will start from white (unused) and change to ~3 ...
David Yang's user avatar
3 votes
1 answer
157 views

Making a bubble chart in Goggle Sheets for year-on-year comparison - ie year versus month?

I'd like to make a multi-series bubble chart from 17 years of sporadic data with Year on the Y axis and Month+Day on the X axis and the bubble size corresponding to each data point's value. I've ...
flyhigher's user avatar
2 votes
2 answers
80 views

Query form data by date to return counts by item and category

I'm a teacher and need help with a Google Form that collects the following info: Timestamp Name Class Lunch option All of the data comes into a Google sheet. I'm using successfully using the QUERY ...
elbennet's user avatar
1 vote
2 answers
81 views

different results for COUNTIFS on date range when using > vs. >=

I haven't quite been able to wrap my head around this one, but I've got a spreadsheet that is a simple list of dates, formatted thusly: 11/1/2022 0:12:54 11/1/2022 6:45:43 11/1/2022 7:13:15 ... and ...
user3908134's user avatar
1 vote
1 answer
119 views

Need help creating a points-based attendance google sheet [closed]

I'm new to using Google sheet formulas, so I would really appreciate help/advice. I'm trying to set up a sheet that uses a points-based system to track attendance - basically subtracting a point for ...
Kelly's user avatar
  • 11
0 votes
1 answer
45 views

How do I combine variables of a row and column?

I have dates set up in column A in yearmonthday format (ex. 20210101), types of product in column B (there are 8 that repeat throughout, so the same date is repeated in column A for 8 rows), and hours ...
Alice's user avatar
  • 9
1 vote
1 answer
27 views

How to best deal with rounding errors when comparing dates/times

I have a column that contains a series of date/time values . I am wanting to determine how many of the time slots fall between 10:00 and 11:00 am, 11:00 am and noon, etc. To pull the time out of my ...
Bapstack's user avatar
-1 votes
1 answer
96 views

How do I use formulas to find the midpoint and other milestones between two dates?

I am trying to make a Google Sheets document for work that will contain the date someone signed, the date that the wedding is occurring, when the first and second payments are due, and when to send ...
logan james's user avatar
0 votes
1 answer
1k views

Merging two timeline charts with different date-sets in Google Sheets

In Google Sheets I have two timeline charts with source data Sheet1!A1:B100 and Sheet2!A1:B100. Dates in Sheet1!A and Sheet2!A mostly do not overlap - Sheet1!A are more or less random (datapoints are ...
dijxtra's user avatar
  • 103
1 vote
1 answer
87 views

SUMIF column by comparing two date columns

Please help with this formula. =ARRAYFORMULA(if(A3:A$900="";"";IF(DATEVALUE(History!C$3:C$900)<D3:D900;SUMIF(History!A$3:A$900;A3:A900;C$3:C$900)*SUMIF(History!A$3:A$900;A3:A900;...
Mateus's user avatar
  • 23
0 votes
1 answer
184 views

adding instances of dates repeating in column A across 11 tabs (sheets) in a sheet

How would I create a formula that shows a sum tally of how many instances each date appears on column A across multiple sheets? I want to make a formula that will display a total tally of how many ...
Spotbeam's user avatar
1 vote
2 answers
782 views

When subtracting times in Google Sheets sometimes it fails and the value is incorrect

I have two fields in my Google Sheets spreadsheet and a third cell that subtracts the difference between them. Most of the time it works fine but sometimes it fails. The way it fails is I believe that ...
1.21 gigawatts's user avatar
1 vote
1 answer
684 views

Google Sheets MATCH/VLOOKUP formula searching Date with AM/PM "cannot find value"

I am trying to return a value based on matching dates that have AM/PM. I have tried Index/Match & ArrayFormula & VLookup Formulas that all should have worked to bring in the value that I'm ...
Chris Dreher's user avatar
2 votes
1 answer
251 views

Difference between today and a date with negative numbers

I need to calculate the difference in days between today and a set date allowing also negative differences. Basically it's the shipping date for each order and how many days we have left till each ...
Katrina Bertacci's user avatar
0 votes
1 answer
30 views

Query that combines the contents of 3 columns, but also filters by a date in a 4th column

This is an example of my data that is located on a sheet called Data: Date Fruit 1 Fruit 2 Fruit 3 1/1/2020 Apple Mango Apple 2/1/2021 Cherry Banana Pear 2/1/2021 Orange 1/1/2022 Apple Cherry ...
Rob Hilken's user avatar
0 votes
1 answer
260 views

Highlight cell based on a specific days - DATE - Need formula

If the sold date is more than 180 days ago (compared to today) conditional format red If the Sold Date is 90 days ago or less (compared to Today) then make green If the sold date is less than 180 ago (...
Aeri's user avatar
  • 23
0 votes
1 answer
187 views

How to get total salary from each month from date?

Given that image below, B is date in format [month/day/year] and salary. From that date, can we total up salary from specific month in yellow table? For instance, we want the total number of salary ...
user1134456's user avatar
1 vote
2 answers
79 views

Calculate a date in combination with the states of multiple checkboxes

I have data in A2:E2. A2 contains a date. B2 contains a formula. C2:E2 contain checkboxes. I'm trying to create a formula in B2 that returns: A2 + 1 day if C2 is checked; A2 + 2 days if both C2 and ...
user avatar
0 votes
1 answer
764 views

ARRAYFORMULA - Return of Text if cell is not blank and DATE earlier than TODAY

After some unsuccessfull workings hours, I definitely need your support. I am trying to create an ARRAYFORMULA, so that new rows can be added manually with the wished formulas already implemented in ...
Pierre's user avatar
  • 1
1 vote
1 answer
554 views

How do I calculate average of all days between 2 date columns in Google Sheets

I'm having trouble creating a formula, which would calculate the average of all days between 2 date columns into one cell. I've tried Average(days(A2:A1000,B2:B1000)) and Average (B2:B1000-A2:A1000), ...
Morrigan's user avatar
2 votes
1 answer
271 views

Creating a rolling date formula to automate spreadsheet

I'm not very experienced with google sheets formulas, and I'm attempting to create a rolling year formula for a scholarship spreadsheet. All I really need it to do (if it's even possible) is to ...
Tatiana's user avatar
  • 21
1 vote
2 answers
173 views

Strange behaviour with iterative calculations on; identical formulas give different results?

So I have a cell with this trick in it to prevent the TODAY() function from updating every day: IF(C1=0,TODAY(),C1) Works perfectly for its purpose. However I have other cells with formulas that refer ...
vkvk's user avatar
  • 13
0 votes
2 answers
273 views

How to calculate the remaining working days in a month

I have =INT(B8-NOW())currently running in a cell to show me the remaining days in the month (B8 being a date) I want to know if there is a way that the output number can calculate only working days, ...
Joshua Rolloos's user avatar
1 vote
2 answers
26 views

Combining TEXT and YEAR returns odd result

In cell A1 I have this date and time: 2022-01-01 16:00 If I do this =Year(A1)-1 the result is as expected 2021 If I do this =TEXT((Year(A1)-1), "YY") the answer is 05 Can anyone explain what ...
lizat's user avatar
  • 13
1 vote
1 answer
266 views

Input key in dd/mm/yyyy format in google sheets

How to enter a date using dd/mm/yyyy? For example - I want 25th September, 2022 to be entered in a column, so I have type 09-25. But in Excel, it's possible if I type only 25-09. Is there any way to ...
satyaban sahoo's user avatar
0 votes
2 answers
1k views

Number of Days Positive and Negative Between Two Dates?

On Google Sheets, I am trying to create a formula within a single cell that calculates the positive or negative number of days between two dates. Specifically I am trying to determine how much buffer ...
Hayden Robel's user avatar
1 vote
1 answer
49 views

Return time formatted based on a custom selection

In Google Sheets, I have 3 columns. Column A contains one of two text strings: 12-Hour or 24-Hour. Column B contains a time that may be in a 12-hour or 24-hour format. eg. 2:00 PM , 14:00 , etc. In ...
Dan Y.'s user avatar
  • 19
1 vote
1 answer
104 views

How to retrieve today and tomorrow's values in a specific tab of a dynamic calendar

Employees are selected in their corresponding shift on each month tab in a dynamic calendar See 092022 tab in my example: I want Pemanence tab will be able to retrieve automatically the values in the ...
Bazu's user avatar
  • 53
1 vote
1 answer
119 views

Google sheets - Conditional formating between 2 dates in function of real time

Is it possible to make a cell automatically highlighted during a time interval relative to the instant T, in real time. The cell should only be highlighted when the time comes and it becomes ...
Bazu's user avatar
  • 53
1 vote
1 answer
44 views

is Google sheet too basic?

Basically earlier I wanted to extract time from a full "date time" record, and after googling it I literally found nothing you can check for yourself, I found a split option and another ...
Ahmed Ben Ammar's user avatar
1 vote
1 answer
21 views

i'm not sure what function I want for this

I have a column that holds the month number(1....60) I want in another column to indicate what the number "1" is (in this case January 2023) I need a formula to look at a column, if the ...
Michael Feiock's user avatar
1 vote
0 answers
30 views

Add more conditions to the formula

=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")}, "select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
Anastasia's user avatar
1 vote
1 answer
29 views

Show most recent date a theatre is visited Google Sheets

I visit 4 theatres regularly, sometimes multiple theatres on the same day but never the same theatre on the same day. I have columns A, B and C in the picture attached. Column A records the date a ...
CellbyDate's user avatar

1
2 3 4 5
7