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

265 questions
Filter by
Sorted by
Tagged with
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 ...
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 ...
• 3
24 views

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 ...
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/...
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 ...
• 1
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 ""?
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 ...
• 103
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?
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 ...
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()...
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().
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:...
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 ...
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 ...
• 1
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 ...
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 ...
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 ...
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 ...
• 1
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 ...
• 2,486
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 ...
• 3
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 ...
• 101
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 ...
• 171
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, ...
• 101
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
• 455
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....
• 101
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, ...
• 800
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 ...
• 1
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 ...
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 ...
• 1
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. ...
185 views

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/...
• 103
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. ...
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 ...
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, ...
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 ...
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 ...
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 ...
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.
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 ...
1 vote
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 ...
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 ...
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 ...
1 vote
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 ...
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 ...
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 ...
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 ...
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 ...
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'...