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
1answer
8 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 ...
0
votes
0answers
9 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 ...
0
votes
1answer
37 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. ...
0
votes
1answer
38 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/...
0
votes
0answers
31 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. ...
-1
votes
1answer
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 ...
0
votes
2answers
37 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, ...
0
votes
1answer
17 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 ...
0
votes
1answer
13 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 ...
0
votes
1answer
13 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 ...
0
votes
1answer
11 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.
0
votes
0answers
26 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
1answer
23 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 ...
0
votes
0answers
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 ...
0
votes
0answers
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
1answer
16 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 ...
0
votes
1answer
50 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 ...
0
votes
2answers
17 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 ...
0
votes
2answers
133 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 ...
0
votes
1answer
62 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 ...
0
votes
1answer
12 views

Uable to compute difference between pasted datetimes in Google Sheets

I am trying to compute differece, but failing Datetimes are pasted and format is assigned. How to accomplish?
0
votes
2answers
30 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'...
0
votes
2answers
42 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 ...
0
votes
1answer
80 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 ...
0
votes
1answer
61 views

Custom Script Formula Working with Time Inputs Keeps Returning 12/30/1899, How Do I Stop This

I've looked around and have seen a lot of people ask this same question, but all of the answers I get are either confusing or don't seem to fit with my situation. Here's a dummy sheet (first time, ...
2
votes
1answer
51 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
211 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
99 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
33 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
18 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
83 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
11 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
82 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
47 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 ...
1
vote
1answer
181 views

Finding the longest time duration in Google Sheets

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time. =LARGE(...
0
votes
1answer
18 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
58 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
15 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
315 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
340 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
125 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
27 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
31 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
24 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
148 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
46 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
34 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
87 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 ...

1
2 3 4 5