Questions tagged [google-sheets-dates]

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

80 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3 votes
3 answers
421 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 ...
user avatar
2 votes
0 answers
96 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 ...
user avatar
2 votes
1 answer
152 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 ...
user avatar
  • 21
2 votes
0 answers
424 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 ...
user avatar
  • 121
2 votes
1 answer
97 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 ...
user avatar
  • 43
2 votes
2 answers
77 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 ...
user avatar
  • 216
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 ...
user avatar
  • 21
1 vote
1 answer
43 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 ...
user avatar
  • 11
1 vote
1 answer
21 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 ...
user avatar
1 vote
0 answers
31 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 ...
user avatar
1 vote
2 answers
390 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 ...
user avatar
1 vote
0 answers
25 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 ...
user avatar
  • 11
1 vote
0 answers
126 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 ...
user avatar
  • 1,008
1 vote
1 answer
52 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 ...
user avatar
  • 123
1 vote
1 answer
35 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 ...
user avatar
1 vote
2 answers
544 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/...
user avatar
  • 11
1 vote
0 answers
33 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 ...
user avatar
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 ...
user avatar
1 vote
0 answers
23 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:...
user avatar
  • 11
1 vote
1 answer
1k 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'...
user avatar
  • 11
1 vote
1 answer
158 views

Google Sheets time sorting chart

I have a MailChimp data export in google sheets and I want to create some charts that can show the best time of day/day of the week based on my data for our mail outs when compared to open rate (%). I ...
user avatar
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-...
user avatar
1 vote
1 answer
25 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 ...
user avatar
1 vote
1 answer
240 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 ...
user avatar
1 vote
1 answer
232 views

How to calculate years left in Google Sheets

This is the function I used and it work =YEAR(A1)-YEAR(B1), A1 is today B1 is the date in the future or past. The problem is that I want to round up year and months. This is the exaple: https://imgur....
user avatar
0 votes
0 answers
20 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 ...
user avatar
  • 1
0 votes
0 answers
14 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",...
user avatar
0 votes
1 answer
12 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/...
user avatar
  • 1
0 votes
1 answer
18 views

How to conditionally format a date column based on before/after another date?

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 ...
user avatar
0 votes
0 answers
14 views

Conditional formatting managed from an absence date table in appsheet

I'm trying to create an absence App in AppSheet using PTO Tracker template, this template generate a table with the following columns: Unique ID Employee Name Start Date End Date My actual version ...
user avatar
  • 1
0 votes
1 answer
20 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 ...
user avatar
  • 1
0 votes
0 answers
13 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. ...
user avatar
0 votes
0 answers
26 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 ...
user avatar
  • 1
0 votes
0 answers
25 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 ...
user avatar
  • 1
0 votes
1 answer
22 views

How to create a linked sheet that checks a box for a day if found and inserts text from another column

I'm trying to create a sheet in Google Sheets for my team that shows me what runners have run on what day of the week. Their activities are automatically imported via Zapier with dates from Strava ...
user avatar
0 votes
0 answers
25 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 ...
user avatar
0 votes
0 answers
53 views

Please help me create a "stopper" for this timer that I made in Google Sheets

After much research and finding and utilizing solutions I've found for creating a long term "stopwatch timer" in Google Sheets, I've done it. It works the way I want it to and it displays ...
user avatar
  • 1
0 votes
1 answer
24 views

How to add month in Sheets cell that corresponds with formula in below adjacent cell?

I am making a cash flow projection worksheet in Google Sheets. I am using this formula to give me sequential weeks (greatly relieving the tedious entry) =ARRAYFORMULA(JOIN("-",TEXT({"5/...
user avatar
0 votes
1 answer
15 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 ...
user avatar
  • 1
0 votes
1 answer
46 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 ...
user avatar
  • 1
0 votes
1 answer
39 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 ...
user avatar
0 votes
0 answers
42 views

QUERY function with date range not working and shows wrong results

I have been working on creating a personal finance spreadsheet and from my experience of using QUERY previously to create dashboards, I thought I would have no issues with this but I was wrong! The ...
user avatar
  • 1
0 votes
0 answers
18 views

Google sheets, scale Axis to Date, not to item

So i have a simple line chart, it plots a value for each entry into the table it is made from. Each only has 2 columns: DateTime, and Value The value is cumulative, so you expect and upward trending ...
user avatar
0 votes
1 answer
189 views

How can I horizontally space data points on a chart when the dates are arbitrary?

Say I have events that occur on sporadic dates, like this: Date Qty 2022-01-01 100 2022-02-08 200 2022-06-17 150 2022-08-03 700 2023-01-27 300 I have rows for arbitrary days in Jan, Feb, June, ...
user avatar
0 votes
0 answers
16 views

Highlight 3 cells based on date in one of the cells to be highlighted

To start off I am using google sheets. I am looking for some help on this one as have been trying and searching the internet for different conditional formattings which aren't working. I have 3 cells ...
user avatar
  • 1
0 votes
1 answer
68 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 ...
user avatar
  • 1
0 votes
0 answers
12 views

How to add a certain amount of months depending on the starting date?

I'm making an inventory for my houseplants. I want to add a formula that can tell me when I have to give fertilizer to my plant, depending on the date I last gave it some. Thing is: when I gave ...
user avatar
  • 1
0 votes
0 answers
40 views

Locking Google Sheet Cells Monthly

I am using Google Sheets, and my columns are set up for each month (Jan, Feb, March, etc). I have several rows that are pulling data from other tabs in the sheet to create an at a glance master list. ...
user avatar
0 votes
1 answer
33 views

Display Google Sheet Default Date Value as N/A

I have a cell (D1) in which a user can enter a date. I have a second cell (B1) that takes the date from cell D1 and adds 10 days to it and displays that date. The user may not need to enter a date in ...
user avatar
0 votes
0 answers
28 views

Referencing three or more ranges and combining the dates that result

I have this formula that I made that takes a range from a cell (in this case, 10/5 - 10/11), subtracts the dates from that range from a larger list of all remaining days in the year, giving me a list ...
user avatar