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
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 ...
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
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 ...
user avatar
  • 3
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 ...
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
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 ...
user avatar
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
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
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 ...
user avatar
0 votes
1 answer
39 views

What formula can I use in order to calculate Z-Score within a specific rolling range?

Doing some sports statistical analysis for fun. Wonder if any of you guys can help me. This is the sheet I'm working with. I'm sure you can see how I'm already experimenting with this in the Z-Score ...
user avatar
  • 3
0 votes
0 answers
49 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
2 answers
35 views

Converting date to day of the week, if no date leave blank

I am using: =TEXT({K24}, "ddd") But is K24 is blank then the results always equals "Sat" how can I get a blank K24 to result in ""?
user avatar
0 votes
1 answer
60 views

Google Sheets SUMIF with date doesn't sum

I want to sum the amounts column B that have a receipt date in column A in 2018. I tried using this SUMIF formula for that: =SUMIF(A2:A4,YEAR(2018),B2:B4) I expected the result to be Fr. 5 but Google ...
user avatar
  • 103
0 votes
1 answer
105 views

I want date with yyyy/mm/dd format on Google Sheets

I want to convert mm/dd/yyyy to yyyy/mm/dd with function on Google Sheets. I applied TEXT function like the picture but it's not working. How can I convert this?
user avatar
0 votes
0 answers
19 views

Running a script on select tabs in Google Sheets [duplicate]

I am not a coder, but I can normally do enough trial and error to get my script to function in the way I want my google sheet to work. The goal of my script: I want cell A to time stamp anytime that ...
user avatar
0 votes
0 answers
8 views

Date of today when an item is sold [duplicate]

Got a question regarding dates. At the moment i have 2 columns: Order date & sale date. I just fill in order date whenever I order something and then the sale date is: =IF(N66="";;TODAY()...
user avatar
-1 votes
1 answer
53 views

Count all cells with specific data between certain dates

I have a schedule of a team planned on multiple projects on certain dates. I'm looking for a formula that retrieves the amount of days (cells) on PROJECT-A until TODAY().
user avatar
0 votes
1 answer
55 views

Google Sheets – Dynamic Calculation of Data Validation

My use case is to select between two times within a single day. The first cell ("from" time) has list of items data validation set up, where the options are as follows: 12:00 am,1:00 am,2:...
user avatar
0 votes
1 answer
20 views

Can I create an if formula to round decimals with differents statements?

I'm having trouble trying to create the formula to round numbers with an if function. I need to round decimals between 5 different statements: If a number has decimals between 0.1 and 0.5 rounddown ...
user avatar
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 ...
user avatar
  • 1
0 votes
0 answers
13 views

I would like to sum values by month and by category [duplicate]

I have a personal finance expense tracker and want to be able to change the month with my data validation (Cell A9 in first screenshot), and sum by the categories below it. I'm stumped on this one and ...
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
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 ...
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
1 answer
472 views

When "Last modified" date in Google Drive can be different than "Last edit" date in corresponding document?

Up until now I was more than sure that "Last modification" date in Google Drive's column and "Last edit" date in Google Docs for the very same document / file must be exactly the ...
user avatar
  • 2,546
0 votes
3 answers
76 views

Google sheets convert time to decimal

I've got an issue with some time data that is exported from a program like this into cells (each number with text in each cell descending all in column a) 0h 14h 17h 17h 25h 30m 0h 8h 30m 7h I need to ...
user avatar
  • 3
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
94 views

GoogleSheet's Output of IMPORTXML Function Changes Format of Imported ISO Date-Time Text

I have a GoogleSheet that's been working fine using the IMPORTXML function, until recently. It seems that when the function imports XML data that are in ISO date and time format that it changes the ...
user avatar
  • 193
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, ...
user avatar
-1 votes
1 answer
59 views

How do I convert a minute number to human-readable format in google sheet? [duplicate]

Is there a way to format the following number (in minutes) to human readable text: 60 > 01:00:00 60.5 > 01:00:30 24 > 00:24:00 1.5 > 00:01:30 80 > 01:20:00 120 > 02:00:00
user avatar
0 votes
2 answers
23 views

How to stop date reformatting of SPLIT() output?

I have content like "3 4 5; 5 6 7; 8 9 10" in a cell and I would like to split it by the semicolons so that I have "3 4 5", "5 6 7", and "8 9 10" in three cells....
user avatar
  • 101
0 votes
1 answer
33 views

How do I retrieve historical currency exchanges rates (USDEUR) in Google Sheets?

I am not quite sure how to get historical exchange rates from Google Sheets. It is the start date and number of days, daily/weekly that is confusing to me. I was able to get the current exchange rate, ...
user avatar
  • 830
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
66 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
1 answer
185 views

How to freeze Google Sheet columns based on date

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
251 views

Working with dates and times with Google Spreadsheet

I have a series of values and the date and time they were recorded: Date | Time | Value -------------------------- ... | ... | ... 27/10/2021 | 11:42 | 18 27/10/2021 | 21:08 | 24 28/...
user avatar
  • 103
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
-1 votes
1 answer
15 views

Autocalculate Date to match a number in a given cell

I'm trying to create a formula to calculate the date in column D4 based on the 'month & year' provided in B4 and a plain-text number given in C4 so if B4 is 02/2021 and C4 is ' 1 ' then ...
user avatar
0 votes
2 answers
41 views

Compare Two Dates and Display The Earlier One

Cells B2 and B3 each have a date entered in them. I want to compare the two dates and display the earlier date in cell C2. Or, stated another way: if B2 < B3, display B2 in cell C2 if B3 < B2, ...
user avatar
0 votes
1 answer
31 views

Combining year date into autofill google sheet

So I am trying to create a basic daily finances tracker using google sheets. And I want to be able to have it so that yearly tabs reference a template sheet that allows you to change the year on each ...
user avatar

1
2 3 4 5 6