Questions tagged [google-sheets-dates]
Use for questions related to how Google Sheets handle dates, times, durations.
74
questions with no upvoted or accepted answers
3
votes
0
answers
603
views
How do I specify a consistent interval for a date-range axis in a Google Sheet Chart
I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or ...
- 31
3
votes
3
answers
524
views
Conditional formatting for columns, date & time specific?
I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this:
2018-11-01T20:16:52
I have tried multiple approaches to sorting ...
- 31
2
votes
1
answer
276
views
Filter date, and add blank rows between different time slots
I'm trying to build a reservations spreadsheet for my friend's restaurant where she can input on monthly sheets as resos come in, then see a filtered, sorted summary of any selected day.
Here is a ...
- 21
2
votes
1
answer
82
views
Round up duration calculation to the next multiple of five minutes
I currently have this formula:
=text(D2 - C2, "hh:mm:ss")
I need this formula to round up to the nearest 5 minutes.
I have tried mround(), ceiling() and roundup(), but I'm not getting the ...
2
votes
1
answer
286
views
Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell
Let's say I have a number of dates and whether they occupy full or half a day:
DATE | WHAT | HALF?
...
2021-4-1 | Something Something | false
2021-4-2 | Something Something | false
...
- 133
2
votes
1
answer
54
views
Add '/' character in between a number (which should be formatted as a date) and turn it to a date
I have received a spreadsheet which has the dates formatted with only numbers without separators and I would like to transform them into a date format. For example, I would like to change the number ...
- 21
2
votes
0
answers
654
views
Sheets - display ISO8601 string as localized date
In Google Sheets, is there a way to have a cell display its value as a localized date (and time) but actually still have and accept its value as an ISO8601 date string?
The use case is that I have ...
- 121
2
votes
2
answers
2k
views
Conditionally format if date is same/greater or smaller than current date
I'm trying to get my Google sheet to automatically colour a cell red if the current date is the same or greater than the date in the cell.
Example: today is 21/02/2020.
The date in the cell is 25/...
- 21
2
votes
1
answer
111
views
How to make a cell autopopulate the date dependent on another cell
I'm trying to make a recruitment sheet for an online community I help run. What I'm currently attempting to do is that when we record a person has joined the Discord to column B, it'll automatically ...
- 43
2
votes
1
answer
37
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 ...
2
votes
2
answers
178
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 ...
- 246
2
votes
2
answers
406
views
How do I prevent or omit a negative value when calculating between times?
I've researched through the site to see if this has been answered but with no luck after reading:
How do I calculate a time delta in my Google Spreadsheets?
Calculating time difference
Calculating ...
- 153
2
votes
1
answer
555
views
Drag to fill/autocomplete dates only increases year, not day
Please see screenshot below. When I dragged on the blue square in highlighted cell, I only get the result below.
I have set correct region in sheet settings. I have set the correct date format in the ...
- 21
2
votes
1
answer
2k
views
How to sum amount with a criteria separately by day in Google Sheets?
I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month.
The file for monthly account contains a couple of sheets - each ...
- 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
168
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
1
answer
182
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 ...
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
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
57
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
306
views
Google sheets import columns based on date value
Google sheets for simplicity's sake say I have two tabs, one with master data and one that is a search function to display a certain part of the master data. The issue is I only want import certain ...
- 11
1
vote
1
answer
290
views
Conditional formatting with dates in Google Sheets
I'm trying to add a little functionality in my register to help me catch incorrectly entered dates. I've got a column, A, for dates where no date entered should be earlier than the previously entered ...
1
vote
4
answers
585
views
IFNA() doesn't work with date format
I have this table :
column F is my data and column H is to check if column F has N/A error. In H1 i use this formula :
ifna(F1,"error")
Seems to work fine. But in the 2nd row when it check ...
- 315
1
vote
0
answers
48
views
How can I use a dropdown menu in Google Sheets filter out data and populate cells for a an expense tracker?
I have created a personal expense tracker (budget) that uses google form responses and categorizes them by fortnights and expenditure categories. This has been successful on a sheet called 'Yearly ...
- 11
1
vote
2
answers
981
views
How to autofill date but skip Friday of every week?
I was trying to skip Friday of every week when I am trying to autofill the date in Google sheet.
There are formulas to skip weekends but I couldn't find any formula to skip any specific day of the ...
- 11
1
vote
0
answers
55
views
Restrict editors to only able fill today's date in data validation date - Google Sheet
I have used the data validation date > 'valid date' so that in the range of cells I want, will already show the calendar drop down. However, would like to ask for guidance on how I can restrict the ...
- 11
1
vote
0
answers
285
views
How to make Google sheets refresh date filtered pivot tables when a day passes?
Google Sheets has a nice advantage over Excel - their pivot table automatically refresh, while Excel's pivot tables rely on clicking a manual UI refresh button (or - if the right pivot setting is ...
- 1,216
1
vote
1
answer
33
views
Transpose Time Google Sheets
I love the transpose date function above. Can you do the same thing with time in hour increments? If someone checks in at 2pm and checks out at 7pm, the cells to the right would count 2pm, 3pm, 4pm, ...
- 11
1
vote
1
answer
178
views
How can I highlight a column of cells (H) if the date in the cell is more than a week old, and the cell next to it (I) is blank?
I'm trying to make a spread sheet to track open purchase orders. One PO per row. One column has the order date in it, and the next column has the receive date for the order. I'd like the cell with the ...
- 11
1
vote
0
answers
36
views
Google Sheets Area Graph(s)
I need (want) to create a Google Sheets Area Graph (Chart).
The Graph/Chart will represent "Date"s.
I need the Xaxis to be the dates (as days) between these start and end dates:
01/27/2020, 4/25/2020 ...
1
vote
1
answer
42
views
Sheets date formula help
Im trying to find a way to input a due date associated with a task with a column next to it with check marks that can clicked once the task is complete, if it doesnt get checked by the due date I'd ...
- 11
1
vote
1
answer
2k
views
How do you interpolate data in a horizontal range that's non-linear?
I've been told what I'm hoping to do is called 'spline interpolation'.
I've imported monthly youtube channel viewership figures from google API. And I've layed out the dates and views in a horizontal ...
- 11
1
vote
0
answers
34
views
Using a query on an importrange, not pulling information
I am trying to do a query on importrange but it is only showing me the headers not any of the data. I am really stuck.
=QUERY(IMPORTRANGE("19Nx2EtMktFW-xwNMjY4IiQpN33QHrXIwcqDsvKqCiHo", "MASTER!A1:...
- 11
1
vote
1
answer
2k
views
Google Sheets Calendar Monday start of week
I don't want my weekday to start on a Sunday, who does? I want it to start on a Monday like most normal people, but impossible to find a way to do this. It's Ok on Google Calendar, you go to 'settings'...
- 11
1
vote
1
answer
3k
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-...
0
votes
1
answer
39
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
0
votes
1
answer
54
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
552
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
0
votes
1
answer
574
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 ...
0
votes
0
answers
217
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
221
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",...
0
votes
1
answer
37
views
How to edit/append a cell value based on date range?
I have a list of dates (including previous, current, and future weeks) and there is a number associated with each date. Example:
I want to add 'x' in front of all the values belonging to dates from ...
- 1
0
votes
0
answers
104
views
Google Sheets, SumIf date in cell matches another date
I have two sheets one with date/times, and names in columns that gets populated automatically from a form submission, and a second one that has dates only across the columns with names down the rows.
...
0
votes
0
answers
34
views
Trying to merge two data sets by matching two fields (Date and Name), where one data set might not have an entry with matching Date
I have two tables:
Table#1 is Store Name, Date, Hours.
Table#2 is Store Name, Date, Sales.
Need to merge the two based on Name and Date, but there are entries on Table#2 that doesn't exist in Table#1 ...
- 1
0
votes
0
answers
50
views
Condensing Data
I have roughly 1000 rows of data, and each day has about 50-60 rows for it. Is there a way I can condense it to where it will just say "May 20" and if you click it, you can get all of the ...
- 1
0
votes
0
answers
30
views
How to edit/format/change date forat sequence of a existing data into another date format sequence?
I got a bank account statement xls sheet with date in format of DD/MM/YYYY i.e. 06/01/2019 in the original form, but when I'm trying to format it into DD-MON-YY it is taking the value as MM/DD/YYYY ...
0
votes
1
answer
24
views
I have two time columns in Google Sheets. Need to know if the time in column 1 is <=24 hours prior to the time in column 2 or > than 24 hours
Please help me get this IF statement right, my brain is fried.
I have several different columns to describe aspects of my Shift IDs which is Column A.
One of these columns is cancellation time of a ...
- 1
0
votes
1
answer
481
views
Conditional Formatting in Google Sheets doesn't work in certain cells with custom formula
Custom Formula doesn't work on certain cells.
A little background, I am making a timezone sheet for a discord where someone input the time they wanted and checks who is and is not available at that ...
- 1
0
votes
1
answer
76
views
Trying to create a cooldown check for a promotion log
SUMMARY
I have a cell (K16) that references to another sheet for when someone has been promoted, and another cell (M16) that works out if the person is still on their 1 day cooldown by checking it ...
