Questions tagged [google-sheets-dates]

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

Filter by
Sorted by
Tagged with
2
votes
1answer
12 views

Using Vlookup with Dates

Here's the set up to the problem I'm having. I have a column of dates and a column of truck numbers. I'm trying to use VLOOKUP to find what truck numbers correspond to the dates. There are multiple ...
1
vote
1answer
35 views

How to set a locale to use comma as decimal separator and Euro as currency symbol by default

Here is my issue : We work in English We are a French company : we want Euro as default currency... ... and the "French formatting" for dates (dd/mm/yyyy) and numbers (1 thousand = 1 000,00 ...
0
votes
1answer
12 views

How to get a YTD dynamically in Google Sheets (Google Finance)?

I want to display the YTD of a stock listed in C. =(GOOGLEFINANCE(C5,"price")/INDEX(GOOGLEFINANCE(C5,"close","01/01/2021", today()),2,2))-1 I do not want to change "...
0
votes
1answer
19 views

Google Sheets VLookup Help

So, I am trying to use the VLookup function to find the value of a cell that corresponds with todays date and return that cell information. However, if there is no date given for todays date, then I ...
0
votes
1answer
16 views

Creating custom month range instead of 1-30 need it as 26-25

For example, if we use the Text function to retrieve a month from a date. It will show the value as per 1-30 but what if we want our month to start from 26th to the 25th of next month and count this a ...
0
votes
2answers
22 views

Can't format hardkey date data as a date in Google sheets?

I've got some date data in a Google drive sheet which is imported from a CSV. The date data is formatted as either dd/mm/yy or dd.mm.yy as hard key data (eg. not formatted as a date) I got to format &...
0
votes
1answer
10 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,...
0
votes
1answer
30 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 ...
0
votes
2answers
31 views

Why doesn't “format A 'yyyy-mm' options no_values” strip the day from the output?

I have some input that contains a set of dates. I want to strip the day from the input and sort by just the month and year. I know there are other ways to do this, but why can't I set the format to ...
0
votes
1answer
17 views

Multiply on a Specific Date (investment simulation for students)

I am trying to create an investment simulation for my high school students in Google Sheets. They already have a bank account that they use (in google sheets) for reward for specific behaviors. I ...
0
votes
1answer
57 views

Count of weekdays and the time duration per day

I have a column that has 34 records of Week Day, Month/Day, and Times. I am looking for two formulas that I can use in a table that will give me the count of weekdays and the time duration per day. ...
1
vote
0answers
14 views

How to add date criteria to query function? [duplicate]

I am using query function to copy all data from sheet1!A2:B10 to Sheet2 if B column is equal to specific data/time. Lets say A1 = 5 April 2021 A2:A10 names B2:B10 has Timestamp And I used this formula ...
-1
votes
1answer
52 views

Delta of two DATETIME in Google Spreadsheet [duplicate]

As show in this image: I have those 2 cells with DATETIME and I need to do the delta of those in order to get Day - HH:mm:ss. How can I do that? If i try to use DATETIME_DIFF I get error. Any tip? ...
0
votes
2answers
68 views

How to normalize different date formats when importing in google sheets

Background The problem I'm solving is mentioned here in detail. Another glitch is that the spreadsheet I'm reading from has random changes to the date format like so: 10/3/2021 11/3/2021 12/3/2021 ...
0
votes
1answer
57 views

Google Sheets - duration between two specific times

I'm having a little trouble writing a formula to measure duration between two specific times within a time range. i.e. Time range: 1300-2330 Total hours=10:30 Hours between 2200-0000=1:30 So far I've ...
1
vote
0answers
21 views

How can I use a dropdown menu in Google Sheets filter out data and populate cells for a an expense tracker?

I have created a personal expense tracker (budget) that uses google form responses and categorizes them by fortnights and expenditure categories. This has been successful on a sheet called 'Yearly ...
1
vote
2answers
28 views

How to create a more accurate calculation of months with Google spreadsheets?

I'm using this formula =DATEDIF(B2; C2; "M") on Googles spreadsheet to calculate months. I expected when entering a begindate B2 22-4-2013 and enddate C2 31-8-2014 to get an exact result: 16,...
1
vote
0answers
17 views

How do I specify a consistent interval for a date-range axis in a Google Sheet Chart

I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or ...
1
vote
2answers
39 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 ...
2
votes
1answer
43 views

Counting rows based on the date it has and the current end of the week

Problem to solve: Given 2 rows, containing the author's name and a date, you have to count how many rows that author has. The tricky part here is that I have to use the date row and check how many ...
1
vote
1answer
23 views

How to automatically fill, sort and conditionally format 1st day of the week according to given year and month

How can I fill-in column A, make it start with the day's name according to the year in A2 and month in A1 and conditionally format them? https://docs.google.com/spreadsheets/d/...
0
votes
2answers
57 views

How to sort a column with date that is impeded within Texts?

I have a list of files' names that I would like to sort based on the date that's impeded within the file name text (Column A folder). When sorting them, the number 10 comes after 1 instead of 1,2,3,4,...
0
votes
1answer
47 views

Look up rate for specific person in effect on or after a date

In this Google Sheet, I have one sheet, "Transactions", with a rate entry that should be auto populated from a table in the "Rates" sheet. The formula should look up the rate for ...
2
votes
1answer
46 views

Drag to fill/autocomplete dates only increases year, not day

Please see screenshot below. When I dragged on the blue square in highlighted cell, I only get the result below. I have set correct region in sheet settings. I have set the correct date format in the ...
0
votes
0answers
12 views

Array formula-ing this formula breaks it…any ideas why?

I have a spreadsheet with SheetA and SheetB. I'm trying to pull into SheetA some of the columns of each row in SheetB where the date in Column I of SheetB is earlier than today. This formula on a ...
1
vote
0answers
12 views

Restrict editors to only able fill today's date in data validation date - Google Sheet

I have used the data validation date > 'valid date' so that in the range of cells I want, will already show the calendar drop down. However, would like to ask for guidance on how I can restrict the ...
1
vote
1answer
38 views

Can a formula cell reference be a variable cell reference?

Assuming I insert every day some rows into Sheet1, this formula stops working and I have to manually edit every day --- can the reference to Cell A2 and Cell E2 be a variable that remains hardcoded as ...
0
votes
0answers
25 views

How to lock a range of cells in Google Sheets after a certain date?

I have this Google Sheet where I track the metrics of the different teams in my office. I have as many as 19 people using the sheet to enter their respective data. But off late I see that people go ...
1
vote
0answers
28 views

How to make Google sheets refresh date filtered pivot tables when a day passes?

Google Sheets has a nice advantage over Excel - their pivot table automatically refresh, while Excel's pivot tables rely on clicking a manual UI refresh button (or - if the right pivot setting is ...
1
vote
2answers
33 views

How can I display Google Sheets dates as Month-Day with the underlying data in UTC format (YYYY-MM-DD)?

I want to display a date in Google Sheets as Month-Day (e.g. Jan-10) with the underlying data as: 2021-01-10 However in order to show Jan-10, Google Sheets defaults the underlying data to 01/10/2021 ...
0
votes
1answer
21 views

How can I calculate the difference between two time fields, being the second greatest than the first one?

I'm having trouble getting negative hour as a result. For instance: G H 08:30 06:00 I would like to get a column with the difference between H and G. So I would be filled with -02:30. ...
0
votes
1answer
17 views

Transpose Time Google Sheets

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, ...
1
vote
1answer
14 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 ...
1
vote
1answer
26 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 ...
0
votes
1answer
18 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 ...
2
votes
2answers
33 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) ...
0
votes
1answer
36 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 ...
0
votes
1answer
15 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 ...
0
votes
1answer
27 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 ...
0
votes
1answer
32 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 ...
3
votes
2answers
57 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 ...
0
votes
1answer
123 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 ...
0
votes
2answers
69 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:...
1
vote
1answer
517 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 =...
1
vote
0answers
181 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 ...
0
votes
1answer
32 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 ...
0
votes
2answers
299 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 ...
0
votes
1answer
893 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 ...
0
votes
1answer
12 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.
0
votes
0answers
18 views

Can't get chart to display weeks from specified day

I can't for the life of me get my chart to display every Monday (starting from 13/07, instead it displays every Sunday (starting with 12/07).The chart also starts with 6 data points (minor lines) and ...

1
2 3 4 5