The 2024 Developer Survey results are live! See the results

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

326 questions
Filter by
Sorted by
Tagged with
1 vote
43 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 ...
910 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 ...
53 views

### Help with Getting the Difference of two dates or displaying NA in cell if no date entered

I am trying to Fetch the difference between two dates in two cells, or if the first cell is empty display nothing. =DATEDIF(E9,H3, "D")OR(ISBLANK(E9)) Is what i have been playing with but i ...
1 vote
84 views

### Calculate a date in combination with the states of multiple checkboxes

I have data in A2:E2. A2 contains a date. B2 contains a formula. C2:E2 contain checkboxes. I'm trying to create a formula in B2 that returns: A2 + 1 day if C2 is checked; A2 + 2 days if both C2 and ...
312 views

### Creating a rolling date formula to automate spreadsheet

I'm not very experienced with google sheets formulas, and I'm attempting to create a rolling year formula for a scholarship spreadsheet. All I really need it to do (if it's even possible) is to ...
1 vote
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 ...
574 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 ...
580 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 ...
1 vote
110 views

### How to retrieve today and tomorrow's values in a specific tab of a dynamic calendar

Employees are selected in their corresponding shift on each month tab in a dynamic calendar See 092022 tab in my example: I want Pemanence tab will be able to retrieve automatically the values in the ...
55 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 ...
71 views

### Google Sheets - conditional formatting - problem with DATE + 45 days

I am trying to setup a simple (I thought so anyway) formula using conditional formatting. I have one cell (B2 for example) that will have a date in it. Then another cell, lets say K2, we manually ...
583 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 ...
53 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,...
1 vote
1k 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 ...
131 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
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-...
86 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 ...
1 vote
329 views

### Input key in dd/mm/yyyy format in google sheets

How to enter a date using dd/mm/yyyy? For example - I want 25th September, 2022 to be entered in a column, so I have type 09-25. But in Excel, it's possible if I type only 25-09. Is there any way to ...
265 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 ...
1 vote
27 views

### How to isolate the earliest and latest times and eliminate any times in between on base on a date

I'm trying to figure out how to isolate only the first and last times from a specific day in Sheet1 A:A and determine the exact hours between the first "Load Out" and the final "Load In&...
1k 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 ...
23 views

### i'm not sure what function I want for this

I have a column that holds the month number(1....60) I want in another column to indicate what the number "1" is (in this case January 2023) I need a formula to look at a column, if the ...
40 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 ...
1 vote
34 views

### Why does VLOOKUP find a match in a sequence of dates when there is none?

I am working on spreadsheet that works with a school calendar. I have a sequence of dates that is a list of Wednesdays. I am trying to do a VLOOKUP on it to find out what type of day a specific date ...
1 vote
37 views

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, ...
114 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 ...
550 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 ...
4k 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. ...
2k 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 ...
1 vote
48 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 ...
387 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 ...
1k views

### Number of Days Positive and Negative Between Two Dates?

On Google Sheets, I am trying to create a formula within a single cell that calculates the positive or negative number of days between two dates. Specifically I am trying to determine how much buffer ...
84 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 ...
27 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 ...
18k views

### How to increment dates by exactly one month from a given date in Google Sheets

I'm using Google Sheets and want to set a date in say E7, then in Column B I want the first row to be the value from E7 and then all the rows below that to be incremented by exactly one month from the ...
1 vote
218 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 ...
1 vote
84 views

### different results for COUNTIFS on date range when using > vs. >=

I haven't quite been able to wrap my head around this one, but I've got a spreadsheet that is a simple list of dates, formatted thusly: 11/1/2022 0:12:54 11/1/2022 6:45:43 11/1/2022 7:13:15 ... and ...
814 views

### ARRAYFORMULA - Return of Text if cell is not blank and DATE earlier than TODAY

After some unsuccessfull workings hours, I definitely need your support. I am trying to create an ARRAYFORMULA, so that new rows can be added manually with the wished formulas already implemented in ...
1k views

### Best way to change dates in Google Sheets in all the tabs?

I have a sheet that is for accountability in my business, I have each month with it's own tab with five week sections and the dates are done by a Monday thru Sunday sections with the date that is ...
1 vote
30 views

### Importrange Query importing correct OR statements, but not refining by date

I have a formula pulling relevant columns from a different document. It is working well except for the last part regarding the dates. It is seemingly ignoring that part of the formula and returning ...
109 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 ...
17 views

### spreadsheet recognize my "time" input as "date" [duplicate]

This column (green) contain data with time format (HH.MM.SS),but for some reason, any time format with their minute below 12, converted into date format (probably because on date format, max month is ...
50 views

### How do I combine variables of a row and column?

I have dates set up in column A in yearmonthday format (ex. 20210101), types of product in column B (there are 8 that repeat throughout, so the same date is repeated in column A for 8 rows), and hours ...
388 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
2k 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'...
3k 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/...
1 vote
776 views

### Is there a way to add a new sheet tab to a Google Sheet based on the timestamp date?

Students take a weekly survey on Google Forms, and it is transferred to a Google Sheet. I want the Google Sheet to automatically generate a new tab based on the date (timestamp) so that each week has ...
1 vote
352 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 ...
1 vote