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
6 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
33 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
24 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
22 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
13 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
33 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
43 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
30 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
17 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
7 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
47 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
41 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
16 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
12 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
38 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
2 answers
70 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
14 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
185 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,486
0 votes
3 answers
68 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
16 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
49 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
  • 171
0 votes
1 answer
52 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
45 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
21 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
26 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
  • 800
0 votes
0 answers
15 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
34 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
104 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
185 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
38 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
39 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
28 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
0 votes
1 answer
62 views

Google Sheet to auto fill weekday dates

I would like to create a Google Sheet whereby the dates of the data will change weekly, and showing only the Weekdays if possible. For example: This week the columns will show 11th Oct, 12th Oct, 13th ...
user avatar
0 votes
1 answer
20 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
1 answer
13 views

Adding Text before a Date in a Cell

How can I achieve "Current Date: <Today's Date>" in a cell in Google Sheets? The "Today's Date" part must update dynamically every day.
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
1 vote
1 answer
26 views

Sorting consecutive dates into groups

I have a list of dates. The list has chronological gaps in it, but I'm looking to bring it all into a single cell of date ranges separated by commas, organized by consecutive dates. Example of the ...
user avatar
0 votes
0 answers
12 views

How to have an example cell duration entry of 12:45 format as 00:12:45 instead of 12:45:00?

Inside of a spreadsheet I have, there is a duration formatted column where I do hand entered data a few times per day. This works well for my purposes, but an annoying problem that I face is that ...
user avatar
0 votes
0 answers
10 views

Date computation dependent on another cell that has a data validation option

I am working on a lead generation tracking spreadsheet in Google Sheets with the hopes of having a follow-up date auto-populate. There are different amounts of time when we want to follow up with ...
user avatar
  • 1
1 vote
1 answer
18 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
0 votes
1 answer
118 views

How do I sum the hours worked, per employee, between specified dates in Google Sheets?

I have been working on a Google Sheet that I am able to import my Google Calendar into and it automatically breaks it down. I have one more formula to figure out before its usable but I can't seem to ...
user avatar
0 votes
2 answers
19 views

How to extract the decimal fraction of a number for a formula?

For a column where years and months are entered as 1.02 for one year and two months, or 3.04 meaning three years and four months, how is that converted to a date? Or just expressed so that it's base ...
user avatar
0 votes
2 answers
429 views

Conditional Formatting for cells that are within 1 hour of the date and time posted within a cell

I have a Auction that I am updating and posting bids to on a Google Sheet that bidders can view. The condition to the auction is that the item will be sold to a bidder if no one has bid on said item ...
user avatar
0 votes
1 answer
99 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 ...
user avatar
0 votes
2 answers
62 views

How to add a date ,from a cell, into a sentence of another cell

Hopefully the title isn't too confusing, but basically I have two cells containing dates and I want to write a sentence in another cell using those dates. I know that to do this with things that aren'...
user avatar
0 votes
3 answers
95 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 ...
user avatar
  • 195
0 votes
1 answer
243 views

App script sheets to update google form dropdown

I'm trying to create a registration form that can be imbedded into our club website for users to sign up to our sessions with. I need the form to be updated via the back end database which i'll add ...
user avatar

1
2 3 4 5 6