All Questions
44 questions
1
vote
1
answer
102
views
Generating date range based on allowed weekdays, repeat times and times of day in Google Sheets
Recently, I’ve been dealing with a lot of medical-related things. To make it more manageable, I decided to create a schedule that I could print out, follow, and update as needed.
Input Data
Time of ...
- 13
1
vote
1
answer
138
views
How do I use a query to get a date from an array formula?
On sheets, I'm using this formula to sum hours to a specific month from a drop-down menu.
=QUERY('Form responses 1'!H2:L,
"Select L, Sum(K)
WHERE L is not NULL
AND L >= date ""&...
- 25
0
votes
1
answer
18
views
How can I use two columns to create date forcasts - e.g use the alternative column if the main column is blank? (automated ideally)
I'm trying to create a spreadsheet which automates dates based on two columns. I would like it to mainly work of 'Confirmed Delivery (column P)' but if the date is blank can I automate a formula to ...
- 1
2
votes
1
answer
5k
views
Google Sheets FILTER formula greater than TODAY()
I want to filter the responses to a Google Form, so as to view only the rows with dates greater than Today() in Column I.
I'm using:
=filter(dates!A:J; dates!I:I > TODAY())
But no rows are being ...
- 37
1
vote
1
answer
727
views
Cannot properly array googlefinance formula with dates
I am trying to convert money amounts in USD to EUR, but I want to do it in relation to date of transaction taking place due to dyncamic exchange rates.
Idea is to use googlefinance function with index ...
- 77
2
votes
2
answers
90
views
Similar arrayformula working, non-array formula working, why not this arrayformula?
This arrayformula works in my spreadsheet:
=arrayformula(IF(A3:A<>"", DATEDIF(H3:H,"01/01/21","m"), ""))
This non-array is working:
=DATEDIF(MAX(datevalue(H3), "1/1/2021"),"01/01/22","m")
So ...
- 23
0
votes
3
answers
68
views
Function With False returned from Empty Cells
I'm trying to make a function that will say True if a cell date has been a year or greater since the current date.
The function I used to get this to work is =IF(DAYS360(H8,TODAY()) >= 258, TRUE)....
- 23
0
votes
1
answer
620
views
COUNTIFS Greater/Less Than Logic Always Returns 0
We're trying to create a sort of filtering system that allows us to filter spreadsheet entries that include a start and end date. We'd like to filter these entries by date (start and end) so we can ...
- 3
1
vote
1
answer
244
views
Search for a particular date in sheet 1 with FROM and TO dates in 2 columns and then paste data from a column in same row in sheet 1 into sheet 2
I am trying to get this formula to work but can't
here is link:
https://docs.google.com/spreadsheets/d/1PmUqg-G-TEZhTFj_viqy3XY0KSiJJiVVGDcN8VV57uI/edit?usp=sharing
Array Formula to Lookup Date ...
- 11
1
vote
1
answer
37
views
How to find values in certain row numbers, 100th, 200th ... 1500th etc
I have dates in column A; I have an API that writes into my google sheet.
I have 1500 such rows with various dates. I want to know the dates in the 100th, 200th,.. rows, so that I can subtract those ...
- 13
1
vote
1
answer
139
views
Multi Condition Array With DateValue
I'm looking to create an Array Formula in Google Sheets that checks if two dates are within certain time periods.
Currently, I am using:
=ARRAYFORMULA(IFERROR(IF(DATEVALUE($B3:$B)-DATEVALUE($A3:$A)&...
1
vote
1
answer
91
views
Stuck with Google Sheet formula
I'm stuck!! Please help me with the following:
Mm/dd/yyyy
01/01/2020
In this date, I can extract the month by
=Month(01/01/2020)
I will get = January
In this date, I can extract the year by
=Year(...
- 11
2
votes
2
answers
682
views
Select "Time Description" based on current date and time in Google Sheets (VLookup, Query)
I am trying to use VLookup or QUERY to extract a day and time description based on the current date and time. I thought QUERY may be the best here, but VLOOKUP seems to be the best solution perhaps.
...
- 956
1
vote
3
answers
36
views
Replacing cell specific seperator in Google Sheets - 21.06.2019 to 21/06/2019
can someone help me in replacing periods with forwarding slashes in sheets?
I have a data set with over 100 thousand rows in sheets,
I have a date-time formatted as such D.M.Y I need to change it to ...
- 129
1
vote
5
answers
2k
views
Find the next Wednesday
I'm trying to display the date of the first upcoming Wednesday, to show the start time of a weekly event.
If it's Sunday 15/12/2019 then it would return Wednesday 18/12/2019.
If it's Monday 16/12/...
- 11
1
vote
2
answers
58
views
Sum values in a range of dates with a timezone in it
I have a long list of dates with this format:
2019-11-28 10:12:48 -0800
The problem is that the formula I use to sum values in a range of dates do not recognize the cell as a Date. I have to ...
- 77
1
vote
1
answer
210
views
Array of start and end dates where the proceeding start date = the previous end date +1 WORKDAY
Is it possible to make two columns of dates (start and end) with only the initial start date and number of days for each task using a single formula? I can drag formulas down, but I'd rather have the ...
- 860
1
vote
3
answers
434
views
Group by month using cell above filtered cells
I have a spreadsheet that looks like this:
https://docs.google.com/spreadsheets/d/1b29gyEgCDwor_KJ6ACP2rxdvauOzacDI9FL2K-jgg5E/edit?usp=sharing
I have two columns I'm interested in, Date and Count. ...
- 117
1
vote
3
answers
7k
views
How to get last Sunday's date?
I need to show last Sunday's date in a cell for a weekly report that I'm creating on google sheets. I've been googling to find a solution and the closest I found is this:
=TODAY()+(7-WEEKDAY(TODAY(),...
- 845
0
votes
2
answers
60
views
how to convert date and time to just date in Sheets
I use Google Forms to import data from a signup form to Google Sheets, and it gives time and date stamp, I just want it to give a date and not the time.
- 151
0
votes
2
answers
61
views
Totaling multiple lines into once cell with different date formats
I want to search through a column of dates in the format YYYY-MM-DD (column G - in a random order) and sum up all corresponding cost values for all dates in the same month.
So, for example, the total ...
- 458
0
votes
1
answer
32
views
Need to pull data from October using MONTH(TODAY())
I need to pull data from October, and not for the current month.
I've tried to replace "TODAY" with October, but it isn't working.
Below is the formula used
=ARRAYFORMULA( COUNTIFS(JIRA!A:A, E2, ...
1
vote
1
answer
611
views
Repeat values using SEQUENCE function in row of dates
I've written a formula to create an automatic sequence of working days, based upon a date specified as the start of the month (this value would be in C4). I'm trying to create a burn vs. actuals ...
- 318
0
votes
1
answer
280
views
How can I increment repeated values in Google Sheets?
I have a sales forecast spreadsheet, which I'm looking to generate cash flow data off of.
My sales forecast looks like so
Base Revenue | Contract Date | Years Subscribed
€45,000 | 30/06/2020 ...
1
vote
2
answers
3k
views
How to extract only date from Google Sheets cell
I'm trying to extract only the date from this cell which says: Current day (1 Nov 2019)
Using:
=regexextract(concatenate(FullReport!B5),".*Current day(.*)")
Can't seem to figure out how to remove ...
- 139
1
vote
1
answer
55
views
How to convert dates pulled from IMPORTXML to actual dates
I'm importing a 45-day weather forecast from
https://weather.interia.com/long-term-forecast-chicago,cId,49700
The dates are listing like so:
1.11
2.11
3.11
4.11
5.11
6.11
7.11
8.11
9.11
10.11
11.11
...
- 139
1
vote
1
answer
30
views
How to change month automatically in an entire column without changing respective days?
I´m doing a macro in Google spreadshets and I need a command to advance one month keeping the same day of the month. The format doesn't matter.
For instance, in the first column I have all the days of ...
- 107
0
votes
1
answer
60
views
Google Sheet Formulas
I have a spreadsheet setup where I have a column with some dates in each month. I have to count the number of dates that occur in the month of Jan, Feb, etc.
The column looks like below:
1/24/2019
2/...
- 409
4
votes
3
answers
4k
views
Generate array of dates with 1 month interval in Google Sheets
I'm trying to generate an array of dates in Google Sheets, where I know the first date, the last one and I want the other dates to be at 1-month interval, with the date always being the last day of ...
- 1,168
0
votes
1
answer
617
views
How to auto-fill the row's column based on the row above?
I have a Google sheet where the 1st row is the date and it will autofill every day. So does the row "Value 1" & "Value 2". Now, I've added manually a row name "SUM" with sum formula.
My question ...
- 734
0
votes
2
answers
103
views
I want to write a formula in Google Sheets that counts the appearance of a value in a range, but the range is the result of another formula
I'm trying to count the number of times "Tuesday" (or any day of the week) appears in a range. However, my range is another formula, that is, the "Tuesday" is the result of a formula from another cell ...
- 111
0
votes
2
answers
397
views
How do sum if the date is greater than 10 days from and including the upcoming Sunday?
I have 2 rows. One row has dates and the other row has numbers.
I want to build a google sheet formula that allows me to sum 10 days after the next Saturday. So if today is Monday 7/22/2019, I want ...
- 1
2
votes
1
answer
901
views
How to get date the same as week number and day of week of previous year using Google Sheets?
I'm looking for a way to get the date the same as week number and day of the week of the previous year in Google Sheets.
For instance,
Input : 12/22/2018 Saturday of week 51.
Output : 12/23/2017 ...
- 23
1
vote
2
answers
1k
views
Get the name of "named data range" from a cell in Google Sheets
I have a range of data that I want to use vlookup on. I named the range as "june_salaries". The goal is to generate payslips for each employee based on the data range (june_salaries).
My query is ...
- 331
3
votes
2
answers
7k
views
Google Sheets LOOKUP unable to find lookup value even if there is
=lookup(A4,'LTO Renewal Record'!B2:B20,'LTO Renewal Record'!C2:C20)
Above code is suppose to return a date value. However, it gives me an error saying "Did not find value "my value" in LOOKUP ...
- 107
1
vote
2
answers
44
views
Subtract two dates and times, and find if the later value exceeds 2 hours
Writing a Google sheet, and need the formula to evaluate two dates and times, and determine if the later value exceeds 2 hours and a day.
IE,
In - Out
1/1/19 2:00 - 1/1/19 3:59
1/1/19 2:00 - 1/1/19 ...
1
vote
1
answer
823
views
Why is my formula returning a number instead of a date?
When trying to add days to a date in another column the value returns as a number, not a date.
I have tried to set the column format as the date for both columns B and C. I also tried using the ...
- 163
6
votes
1
answer
2k
views
List of months between two dates as month & year
How do I get a dynamic list of months down a column between two dates in the month year format in Google Sheets?
example:
Start date: 5/18/2016
End date: 5/24/2019
Jan 2016
Feb 2016
Mar 2016
..
...
...
- 81
6
votes
7
answers
8k
views
Difference between two dates expressed as years, months, days (in one column) [duplicate]
I have a set of columns set up in Google Sheets that display the difference between two dates using the DATEDIF function:
=DATEDIF (AS2, TODAY(), "D")
(number of days passed today since a certain ...
- 85
0
votes
2
answers
6k
views
Google Sheets: Moving daily, weekly, monthly, and yearly averages
I have a spreadsheet where I'm collecting data, and entering a date the data is collected. I would like to get the moving averages of collected data in my spreadsheet on a daily, weekly, monthly, and ...
- 557
0
votes
1
answer
194
views
Calculating preparation dates and adjusting for weekends
I am working on a Google Sheets that calculates prep time needed for my events.
Spreadsheet
My events have a 'Ship Date' that is used in calculating my 'Prep Start' and 'Prep End' days. I am ...
- 11
1
vote
2
answers
330
views
SUMIF cell has positive number, IF cell contains '0' return result of another cell
I have a Google Sheets that is calculating date values. I would like to enter a formula that decreases the date pulled from a given cell if the cell contains a positive number. However, if the cell ...
- 11
4
votes
3
answers
18k
views
Set column default value in Google Sheets
I have a finance tracking worksheet that has a number of formulas for each row. What I'd like to add is have these formulas replicate whenever a new row is inserted (fill it with a "default value").
...
- 596
0
votes
1
answer
85
views
How to simplify this spreadsheet formula?
I am working on my personal financial spreadsheet, and I have one table that lists columns like: "Month","Income",Expense","Notes", and I am building a 2nd table to use to build a new chart so I can ...
- 73