Questions tagged [google-sheets-dates]
Use for questions related to how Google Sheets handle dates, times, durations.
327 questions
1
vote
1
answer
30
views
How to isolate the earliest and latest times and eliminate any times in between on base on a date
I'm trying to figure out how to isolate only the first and last times from a specific day in Sheet1 A:A and determine the exact hours between the first "Load Out" and the final "Load In&...
- 11
1
vote
2
answers
40
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 ...
- 31
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 ...
1
vote
1
answer
110
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 ...
- 115
0
votes
2
answers
43
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 ...
- 3
0
votes
1
answer
26
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 ...
0
votes
1
answer
143
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 ...
0
votes
1
answer
74
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 ...
0
votes
1
answer
37
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 ...
- 3
2
votes
5
answers
1k
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 ...
- 129
1
vote
1
answer
721
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>...
1
vote
1
answer
50
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 ...
- 25
0
votes
1
answer
421
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 ...
- 13
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/...
- 11
1
vote
1
answer
59
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, ...
- 11
0
votes
1
answer
32
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?
- 252
0
votes
1
answer
83
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 ...
1
vote
1
answer
92
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 ...
- 11
3
votes
1
answer
178
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 ...
- 31
2
votes
2
answers
87
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 ...
- 21
1
vote
2
answers
92
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 ...
- 11
1
vote
1
answer
283
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 ...
- 11
0
votes
1
answer
56
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 ...
- 9
1
vote
1
answer
54
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 ...
- 11
-1
votes
1
answer
194
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 ...
0
votes
1
answer
2k
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 ...
- 103
1
vote
1
answer
95
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;...
- 23
0
votes
1
answer
210
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 ...
- 21
1
vote
2
answers
1k
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 ...
- 177
1
vote
1
answer
959
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 ...
- 15
2
votes
1
answer
278
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 ...
0
votes
1
answer
34
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
...
0
votes
1
answer
368
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 (...
- 23
0
votes
1
answer
200
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 ...
- 101
1
vote
2
answers
86
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 ...
user306472
0
votes
1
answer
880
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 ...
- 1
1
vote
1
answer
664
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), ...
- 13
2
votes
1
answer
389
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 ...
- 21
1
vote
2
answers
228
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 ...
- 13
0
votes
2
answers
505
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, ...
1
vote
2
answers
28
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 ...
- 13
1
vote
1
answer
393
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 ...
0
votes
2
answers
2k
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 ...
1
vote
1
answer
50
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 ...
- 19
1
vote
1
answer
115
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 ...
- 53
1
vote
1
answer
142
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 ...
- 53
1
vote
1
answer
45
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 ...
1
vote
1
answer
27
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 ...
1
vote
0
answers
31
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, ...
- 11
1
vote
1
answer
36
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 ...
- 25