Questions tagged [google-sheets-dates]
Use for questions related to how Google Sheets handle dates, times, durations.
316
questions
0
votes
0
answers
22
views
Getting just month and year from a date [closed]
I have a data sheet in Google Sheets that has a date time column that I need to isolate just the month and year from in a mm/yyyy format. The original column looks like this:
27/09/2021 10:13
I used ...
- 1
0
votes
1
answer
19
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>...
0
votes
1
answer
15
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 ...
- 15
0
votes
1
answer
42
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 ...
- 3
1
vote
0
answers
14
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
56
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
28
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?
- 202
0
votes
1
answer
22
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
43
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
88
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
49
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
65
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
41
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
38
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
21
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
39
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
485
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
66
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
134
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
331
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
332
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
218
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
27
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
151
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 (...
- 15
0
votes
0
answers
16
views
Sorting by dates [duplicate]
I'm trying to sort dates in Google Sheets and ran into a problem.
I have many dates consisting of a day and month. DD/MM
When sorting sheets does this:
01/01
01/02
03/02
04/01
07/01
09/02
15/01
And ...
- 1
0
votes
1
answer
128
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
0
votes
1
answer
50
views
Multiple IF Functions with Tick Boxes and Dates in Google Sheets
I have 5 columns, first two have dates, the other three are tick boxes.
I would like to create a formula, where if:
C2 box is checked (true), Google Sheets will add 1 day to the date written in A2 ...
user306472
0
votes
1
answer
541
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
375
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
1
vote
1
answer
161
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 ...
- 11
1
vote
2
answers
114
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
56
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
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 ...
- 13
1
vote
1
answer
180
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
1
answer
562
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
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 ...
- 19
1
vote
1
answer
74
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
93
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
41
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
18
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
27
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
28
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
0
votes
1
answer
523
views
VLOOKUP between two dates in Google Sheets
I'm having trouble with a vlookup between two dates.
This the case:
In my first tab I have the following data:
You can see that person "Stephen Chesson" has another profile from 01/08/2022.
...
- 139
0
votes
1
answer
33
views
How to isolate the next due date from a list of dates in Google Sheets?
I have a spreadsheet with a list of tasks and due dates in Google Sheets (Column E). I have an adjacent column which tracks the status of the task as either "Pending", "In Progress"...
0
votes
0
answers
214
views
Importrange changes the format only for a couple cells
I am using importrange and for some cells google sheets changes the format of only a couple cells. I have attached a screenshot. The original worksheet has the correct dates M/DD/YYYY. So I am ...
- 1
0
votes
0
answers
217
views
Using Query & Import Range function to import data range with a date filter
I have been able to use the the Query and Import Range function perfectly.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/13eoPsqGik4ZWFGPURrC9RYdlOuxdbCbQWXzWA9LC1L4/edit#gid=0",...
1
vote
1
answer
47
views
Google sheets update values based on current month
I need help with Google Sheets.
Basically, i want to detect the currfent month and display its name and corresponding value on a cell.
this is the link: https://docs.google.com/spreadsheets/d/...
- 11
1
vote
1
answer
164
views
How to conditionally format a date column based on before/after another date? [closed]
I have a spreadsheet where each row represents a case, and one column (Column E) is a manually-entered date, whereas numerous columns following are based on Column E plus or minus various spans of ...
- 111
0
votes
1
answer
208
views
Countifs between 2 dates from an import range
Looking for some help on a Countifs formula from an import range (if this is the right thing to use(?))
I have a sheet tracking rejected bookings on certain dates, and I want to pull this into another ...
- 3
0
votes
1
answer
30
views
Trying to Count 2 days from an original date to flag a late response
I need a formula that will provide this conditional formatting in Google Sheets. Please help.
Column H = Date the request was sent
Column I = Date the response was received
I need to ensure I have a ...
- 1