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
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 ...
user avatar
  • 1
2 votes
2 answers
37 views

LOOKUP-like behaviour adding up rows down to the found one

I have a table with dates in column A and values in column B. A B 1 2020-12-30 1 2 2020-12-31 2 3 2021-01-04 4 Given a DATE, I can LOOKUP the last value before (including) ...
user avatar
  • 739
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 ...
user avatar
  • 9
0 votes
1 answer
18 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 ...
user avatar
0 votes
1 answer
364 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 ...
user avatar
  • 1
0 votes
1 answer
37 views

When typing a date into cell as 12/24, parse it as Dec 1 2024, instead of Dec 24, 2020. (dd/dd to Month Year rather then Month Day)

Going to a new cell, if I type 07/22, if the column data format is set to a date, it interprets this to July 22, 2020 (where 2020 is the current year). I want it to use the 22 as the year, I don't ...
user avatar
3 votes
2 answers
68 views

How to get a difference of "X years, Y months, Z days" between two dates?

I have two dates: 29/05/2020 and today. How can I get a date diff of these 2 dates, in a meaningful format such as "Z years, X months, Y days" separating these dates? Ideally this should ...
user avatar
0 votes
1 answer
467 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 ...
user avatar
0 votes
2 answers
91 views

How to get DATEVALUE to recognize RSS 2 pubDate (RFC822)?

My formula contains an RFC822 pubDate, e.g. as pulled from Google's RSS2 Feed. =DATEVALUE("Thu, 01 Oct 2020 10:00:00 +0000") but it results in Error DATEVALUE parameter 'Thu, 01 Oct 2020 10:...
user avatar
  • 133
1 vote
1 answer
1k views

How to show Day of Week in Order by Day of Week in Google Sheets

I have the following formula that displays a total by Day of Week (weekday) from a date in J and amount in K Example J contains 1/1/2020 ... until 9/29/2020 K contains numeric value 123 ... 1999 =...
user avatar
  • 75
2 votes
0 answers
424 views

Sheets - display ISO8601 string as localized date

In Google Sheets, is there a way to have a cell display its value as a localized date (and time) but actually still have and accept its value as an ISO8601 date string? The use case is that I have ...
user avatar
  • 121
0 votes
1 answer
64 views

Google Sheet average a row from last 10 days to the current date

I am looking for a formula that would automatically average their own row on C3 to C10 based on the last past 10 days from the current day as seen on their right side. The list doesn't have the ...
user avatar
0 votes
2 answers
815 views

How do I change the date to the coming Monday every Friday at 9:30 pm in Google Sheets?

I would like to write a function that checks what day of the week today is, and whenever it is Friday 9:30 pm it should populate the cell with the date of the coming Monday. So since today is Friday ...
user avatar
0 votes
1 answer
2k views

FedEx Tracking in Google Sheets

I work for a company that sends packages out daily. We track the numbers in a Google sheet that populates via a Google form we use. Recently we have had some packages go missing and I have been tasked ...
user avatar
0 votes
1 answer
14 views

I'm having trouble getting this to export military time

Can you tell me how to modify the date/time format to give me military time? I'm Los Angeles time which is GMT-08:00 It's giving me 07:28 but what I want is 19:28.
user avatar
2 votes
2 answers
384 views

Calculate Total DateDif Average Between 2 Columns (start, end) according to year expressed in days, hours, minutes

I'm having trouble getting a formula to work to calculate the total average time between times (start and end). Sheet: https://docs.google.com/spreadsheets/d/...
user avatar
0 votes
1 answer
76 views

Query or filter the data when using date time format

Example sheet. Sheet2 In cell A2 I have: =QUERY(Sheet1!A1:Q, "Select * where B >= datetime '"&TEXT($A$1,"MM-DD-YYYY HH:mm:ss")&"'") In cell A1 I have: 6/17/...
user avatar
0 votes
1 answer
530 views

How can I use TODAY() as part of a formula checking against a list of dates in Google Sheets?

I'm trying to use the TODAY() formula to check against a long column of date cells, and mark them as 'LIVE' if the given date is less than (or equal to) today. Crucially though, if the date cell is ...
user avatar
  • 1
0 votes
1 answer
33 views

How to convert a numeric value into a time format inside a formula that also contains a string

I have a "mixed" formula (containing numbers and text) and I need to convert the numeric values into a time format. Here is my formula: =count(J8:J11)*2/24&" / "&sum(K8:K11)...
user avatar
  • 618
0 votes
1 answer
47 views

Create Columns via Formula for two dates

How do you do this with columns instead of rows? =ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATEVALUE("2018-4-13")& ":A"&DATEVALUE(&...
user avatar
  • 1
0 votes
1 answer
33 views

How to create a bill checker with Google Sheet

What I'm trying to do is to create a Google Sheet to check my internet bill, but something doesn't seem to work. FROM TO TOT DAYS PRICE CALCULATED PRICE OF BILL 5/3/2020 ...
user avatar
1 vote
1 answer
364 views

Convert string to real date time value?

Here is my current input: 14.05.20 12.37.01 And yes, as it shows, there is a line break in the cell... Here is example sheet: https://docs.google.com/spreadsheets/d/...
user avatar
  • 618
0 votes
0 answers
33 views

Count mismatches between rows in google sheet

Need some help on this cause I'm getting an issue I've this three columns (Time, R1 and R2) and I'm trying to count the mismatches between R1 and R2 but for each month (on the time column) I already ...
user avatar
2 votes
1 answer
42 views

How to autofill specific cell when any next cell is filled

+------------+---------+---------+ | Date | Col1 | Col2 | +------------+---------+---------+ | 2018-01-01 | Value 1 | Value 1 | | 2018-01-01 | Value 2 | Value 2 | | 2018-01-01 | Value 3 | ...
user avatar
1 vote
1 answer
124 views

Google Sheets formula for difference from prior day

I have a monthly sheet w/ dated tabs. I am looking for the formula for the difference between today's data (in the cell) and data from previous day's tab. Today is the 30th so what I am currently ...
user avatar
1 vote
1 answer
289 views

Query Google Sheets Using Week Num to order dates

I was finishing ordering timestamps and dates, and I can't quite get it to work: =QUERY(Sheet3!A:I, " SELECT D, SUM(I) GROUP BY WEEKNUM(D) ORDER BY D DESC LABEL SUM(I) 'Balance Semanal' ...
user avatar
  • 13
1 vote
1 answer
42 views

Editing cell value changes its formatting

Enter the value 18/04/2020 08:10:00 in a sell. Set the formatting of the sell to Hour(1):Minute(01) Actual value 18/04/2020 08:10:00 Display value: 8:10 Edit the value of the sell to become 18/04/...
user avatar
1 vote
1 answer
36 views

Sum category column in one sheet based on a dates column in another sheet [duplicate]

I have a sheet, Transactions, that looks like this: +--------------+-------------+-------------+-----------+ | Date | Payee | Category | Amount | +--------------+-------------+----...
user avatar
2 votes
1 answer
68 views

Query a column based on current date

I'm trying to query a column based on the current day. Examples: If today is 1st day of the month I need to: =QUERY(table,"SELECT A WHERE B CONTAINS 'this text'") If today is 12th day of the month ...
user avatar
4 votes
2 answers
135 views

How to get a new date when calculating from today plus an integer

I use Google Sheets to calculate my word count & rate for daily writing sessions. I'm trying to come up with a formula that will return the estimated date of hitting a goal, based on my current ...
user avatar
1 vote
1 answer
28 views

get value left of max value

I have a sheet that looks like: Date Value Highest Lowest ---------------------------------------------------------------- 1-1-2020 23 26 20 2-1-2020 20 3-...
user avatar
3 votes
1 answer
361 views

Print incremental numbers in Google Sheets

Cell A1 holds a numerical value (e.g. 10), and I'd like to output the list of numbers from 1 to that number in B1: What's the formula I need for B1? It seems trivial, and I know it probably requires ...
user avatar
0 votes
1 answer
67 views

Prevent a Google Script from Modifying Date

I have a Google Sheet that I am applying an onEdit script to. That script (borrowed from WebApps:Convert all text to UPPERCASE in a Google Spreadsheet) capitalizes all new entries in every field of ...
user avatar
1 vote
2 answers
544 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/...
user avatar
  • 11
1 vote
1 answer
372 views

How to extract time out of text in Google Sheets

I have the following issue and I would appreciate your help. I need to get the time values out of this string: SomeText 02/02/2020 9:00 AM-02/02/2020 9:15 AM;"Text" 02/02/2020 10:45 AM-02/02/2020 11:...
user avatar
  • 15
1 vote
2 answers
2k views

How can I calculate a future date in google sheets when I know the number of months?

I have a date (TODAY()), and I have a number of months in a google sheet. How can I get a future date from this? I tried =DATE(TODAY()+MONTH(B63)). But the problem is that the months in B63 are not in ...
user avatar
  • 13
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 ...
user avatar
  • 43
2 votes
1 answer
7k views

Using dates as criterion for COUNTIFS in Google Sheets

So I'm currently making a recruiting data sheet for an online community. I'm trying to make it as automated as possible without actually making a bot to do it. How can I make a COUNTIF(or similar) ...
user avatar
  • 43
1 vote
2 answers
96 views

Weekday Formula Error

I'm trying to use the Weekday formula. However, instead of getting a 0-7 number returned, I am getting 12/31/1899. I have watched several videos, tried on different browsers and devises. What can I do?...
user avatar
  • 11
1 vote
0 answers
33 views

Google Sheets Area Graph(s)

I need (want) to create a Google Sheets Area Graph (Chart). The Graph/Chart will represent "Date"s. I need the Xaxis to be the dates (as days) between these start and end dates: 01/27/2020, 4/25/2020 ...
user avatar
3 votes
2 answers
6k 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 E:7, then in Column B I want the first row to be the value E:7 and then all the rows below that to be incremented by exactly one month from the ...
user avatar
  • 147
0 votes
0 answers
42 views

How to calculate the difference between two times accurately across midnight? [duplicate]

I've seen this answer on a related question which is an excellent simple solution to calculating the difference between two times. However I've run into an issue when trying to calculate the ...
user avatar
  • 215
5 votes
4 answers
13k views

How to convert datetime string to datetime value in Google Sheets?

Let's say cells A1 and A2 have these values: 13 Jan, 02:21 am 12 Jan, 10:15 pm And many more values like those below in that column. What formula could I use so that the B column would look like ...
user avatar
  • 612
0 votes
2 answers
556 views

Google Sheets interpreting dates from CSV in a weird way

I'm opening a csv report in Google Sheets. The Google Drive preview shows the dates like this: When I open this file in Google Sheets the dates now look like this: And if I try and convert the date ...
user avatar
1 vote
1 answer
37 views

Formula Error: =IF( SEARCH() ); Visual Reference to Question

I know I'm very close with my formula: =IF(SEARCH("Wednesday", (A3:A)), "workday", 0), But I must be missing something here...: I can't get this formula to display the value "-" when I run it on a ...
user avatar
-1 votes
1 answer
83 views

Change a date reference as text to Google Sheets date format

Need to change "December 23, 2019 at 08:31PM" to a date Google Sheets recognize.
user avatar
1 vote
1 answer
313 views

Query/Filter function finding the closest date that is less than the order date for an array of dates using a given variable

Overview I need to get the cost of something by comparing item dates against an order date for one or more of the same item with different item dates, and making sure to select the item date that is ...
user avatar
  • 25
0 votes
1 answer
2k views

Google Sheets: Fill out dates and replace blank cells with zeroes

I'm trying to make a timeline graph of seed sales, and right now it's almost completely unuseable: I think there are two reasons for this: Not all dates have values associated with them, and ...
user avatar
-1 votes
1 answer
51 views

Google Sheets - If? ISBLANK? Not sure how to pull this one off

I am trying to find a formula for the following: If cell A1 has a date in it, copy that date to cell F1. If A1 does not have a date in it, copy the date from cell B1 into cell F1. I have a receipt ...
user avatar
2 votes
1 answer
319 views

How to access the "unformatted" date in Google Sheets?

I am working with a spreadsheet that includes a row where each cell contains the date of the first day in each month in a series of years: 1-Jan-18, 1-Feb-18, 1-Mar-18, ... , 1-Dec-18, 1-Jan-19, 1-Feb-...
user avatar
  • 173