Questions tagged [google-sheets-dates]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
38 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 ...
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 ...
0 votes
0 answers
19 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 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 ...
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 ...
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",...
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 ...
0 votes
1 answer
27 views

Add year (multiple different years) in cells that have only day and month (Google Sheets)

I have a spreadsheet as follows in column B: 10/11 27/10 05/05 01/01 24/12 11/07 03/03 27/11 15/10 The top ones are always from the year that we are, in this example it's 2019, but according to what ...
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/...
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 ...
0 votes
1 answer
45 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 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 ...
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'...
7 votes
2 answers
17k views

How to reduce number of X axis labels?

I have a chart with dates along the X axis, but the chart displays them quite close together, producing a rather ugly graph. How might I reduce the number of visible labels to increase the readability ...
0 votes
1 answer
22 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 ...
0 votes
1 answer
16 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 ...
1 vote
2 answers
387 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 ...
0 votes
1 answer
102 views

SUM weekly values according to date in one column A and values in column B

As shown in the screenshot, I have values I am looking to add for weekly totals to track, however, this is very tedious having to create a new code for every week, especially as the weeks change per ...
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-...
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 ...
1 vote
2 answers
543 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/...
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 ...
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 ...
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 ...
0 votes
1 answer
52 views

Query only returning when 6 or more rows of data are entered

I am using the query function to gather data from other sheets in the same workbook. It only pulls over the data from the "teacher" sheets when at least 6 rows of data are entered. The 6 ...
0 votes
3 answers
140 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 ...
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. ...
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 ...
0 votes
1 answer
463 views

When setting a duration, hide the hours?

What I needed for a spreadsheets is specifically times "mm:ss.ms" for example: 00:30.522 (which is 1823 frames @59.7275fps). If possible, 30.522, but that's not a big deal. I was able to get ...
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 ...
1 vote
1 answer
237 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 ...
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 ...
0 votes
1 answer
23 views

Creating custom month range instead of 1-30 need it as 26-25

For example, if we use the Text function to retrieve a month from a date. It will show the value as per 1-30 but what if we want our month to start from 26th to the 25th of next month and count this a ...
0 votes
1 answer
21 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, ...
0 votes
1 answer
17 views

IMPORTDATA pulls multiple date format even if cell format is specified

I am pulling weather data for a city from a server (visualcrossing.com) and it's giving me the results in CSV format: Name,Date time,Maximum Temperature,Minimum Temperature,Temperature,Wind Chill,...
0 votes
1 answer
184 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, ...
3 votes
3 answers
420 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 ...
0 votes
3 answers
87 views

Google Sheets: keep track of days spent in different countries

I have prepared a Google Sheet that lists all flights I have taken in recent years, alongside the departing and arriving country: Date & time From To Outbound country Inbound country Days in ...
0 votes
1 answer
3k views

Date comparison and DATEVALUE with dd.mm.YYYY. date format

I'm using the custom date format for one of my columns, dd.mm.YYYY.. I want to compare that date to TODAY(), by using +AND($D2 < TODAY()) where column D is the one with the dates in dd.mm.YYYY. ...
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 ...
0 votes
1 answer
178 views

Google sheets query multi conditional date cell ref

I have a doubt regarding a change in this formulas in sheets: =INDEX(QUERY($A$3:$H; "SELECT sum (D) where C='Pub'");2) =INDEX(QUERY($A$3:$K; "SELECT sum (F) where C='Reg'and K='"& K15&"'");...
0 votes
1 answer
27 views

Trying to change Gantt chart in Google Sheets to highlight a cell based on date range and progress percentage

I have been working on a dynamic Gantt chart template in Google Sheets for awhile now. Here's how it currently looks: As you can see, the task bar currently highlights gray dependent on the start and ...
0 votes
1 answer
1k views

search/match a date on Google Sheets using Google script and copy a source range to a destination range OFFSET from that date

UPDATE: I FIGURED IT OUT. I'm struggling with some simple copy/paste scripting in Google Sheets. I'm a complete scripting newbie. Advanced at Excel/Sheets. I have a "Today" data entry sheet. I ...
0 votes
1 answer
43 views

How do I update Google worksheet with data from separate worksheet based on dates

I have a worksheet of "Loads" that contains a growing list of loads done by drivers. This worksheet grows with new rows daily. I am wanting to have a seperate worksheet called "New ...
1 vote
1 answer
50 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 ...
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/...
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 ...
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 ...
0 votes
1 answer
84 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 ...
0 votes
1 answer
176 views

How to give a column a green border if the second row's holds a date = TODAY()?

I'm trying to make a useful schedule sheet to keep track of all of my class assignments: I want to write a script in the script editor that says, for(Column A-Z){ if(A2,B2,etc = TODAY()){ ...

1
2 3 4 5 6