Questions tagged [google-sheets-dates]

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

Filter by
Sorted by
Tagged with
1 vote
1 answer
16 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 ...
0 votes
1 answer
16 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 ...
0 votes
1 answer
4 views

Google Sheets - Time format based on "12" or "24" drop down

I have a "Date Format" cell that allows values "12-Hour" or "24-Hour". I have a "Start Time" cell that contains a time of day (like 2:00pm or 14:00) I would ...
  • 1
0 votes
0 answers
19 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 ...
  • 21
1 vote
1 answer
27 views

Google sheets - Conditional formating between 2 dates in function of real time

Is it possible to make a cell automatically highlighted during a time interval relative to the instant T, in real time. The cell should only be highlighted when the time comes and it becomes ...
  • 21
1 vote
1 answer
39 views

is Google sheet too basic?

Basically earlier I wanted to extract time from a full "date time" record, and after googling it I literally found nothing you can check for yourself, I found a split option and another ...
1 vote
1 answer
13 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 ...
1 vote
0 answers
10 views

Add more conditions to the formula

=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")}, "select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
1 vote
1 answer
19 views

Show most recent date a theatre is visited Google Sheets

I visit 4 theatres regularly, sometimes multiple theatres on the same day but never the same theatre on the same day. I have columns A, B and C in the picture attached. Column A records the date a ...
0 votes
1 answer
22 views

VLOOKUP between two dates in Google Sheets

I'm having trouble with a vlookup between two dates. This the case: In my first tab I have the following data: You can see that person "Stephen Chesson" has another profile from 01/08/2022. ...
  • 139
0 votes
1 answer
19 views

How to isolate the next due date from a list of dates in Google Sheets?

I have a spreadsheet with a list of tasks and due dates in Google Sheets (Column E). I have an adjacent column which tracks the status of the task as either "Pending", "In Progress"...
0 votes
0 answers
26 views

Importrange changes the format only for a couple cells

I am using importrange and for some cells google sheets changes the format of only a couple cells. I have attached a screenshot. The original worksheet has the correct dates M/DD/YYYY. So I am ...
  • 1
0 votes
0 answers
24 views

Using Query & Import Range function to import data range with a date filter

I have been able to use the the Query and Import Range function perfectly. =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/13eoPsqGik4ZWFGPURrC9RYdlOuxdbCbQWXzWA9LC1L4/edit#gid=0",...
1 vote
1 answer
16 views

Google sheets update values based on current month

I need help with Google Sheets. Basically, i want to detect the currfent month and display its name and corresponding value on a cell. this is the link: https://docs.google.com/spreadsheets/d/...
  • 11
0 votes
1 answer
21 views

How to conditionally format a date column based on before/after another date?

I have a spreadsheet where each row represents a case, and one column (Column E) is a manually-entered date, whereas numerous columns following are based on Column E plus or minus various spans of ...
0 votes
1 answer
33 views

Countifs between 2 dates from an import range

Looking for some help on a Countifs formula from an import range (if this is the right thing to use(?)) I have a sheet tracking rejected bookings on certain dates, and I want to pull this into another ...
  • 3
0 votes
1 answer
22 views

Trying to Count 2 days from an original date to flag a late response

I need a formula that will provide this conditional formatting in Google Sheets. Please help. Column H = Date the request was sent Column I = Date the response was received I need to ensure I have a ...
0 votes
0 answers
19 views

Conditional formatting managed from an absence date table in appsheet

I'm trying to create an absence App in AppSheet using PTO Tracker template, this template generate a table with the following columns: Unique ID Employee Name Start Date End Date My actual version ...
  • 1
0 votes
1 answer
20 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 ...
  • 1
0 votes
1 answer
52 views

Query only returning when 6 or more rows of data are entered

I am using the query function to gather data from other sheets in the same workbook. It only pulls over the data from the "teacher" sheets when at least 6 rows of data are entered. The 6 ...
0 votes
0 answers
14 views

Google Sheets, SumIf date in cell matches another date

I have two sheets one with date/times, and names in columns that gets populated automatically from a form submission, and a second one that has dates only across the columns with names down the rows. ...
0 votes
0 answers
26 views

Trying to merge two data sets by matching two fields (Date and Name), where one data set might not have an entry with matching Date

I have two tables: Table#1 is Store Name, Date, Hours. Table#2 is Store Name, Date, Sales. Need to merge the two based on Name and Date, but there are entries on Table#2 that doesn't exist in Table#1 ...
  • 1
0 votes
0 answers
25 views

Condensing Data

I have roughly 1000 rows of data, and each day has about 50-60 rows for it. Is there a way I can condense it to where it will just say "May 20" and if you click it, you can get all of the ...
  • 1
0 votes
1 answer
23 views

How to create a linked sheet that checks a box for a day if found and inserts text from another column

I'm trying to create a sheet in Google Sheets for my team that shows me what runners have run on what day of the week. Their activities are automatically imported via Zapier with dates from Strava ...
0 votes
0 answers
26 views

How to edit/format/change date forat sequence of a existing data into another date format sequence?

I got a bank account statement xls sheet with date in format of DD/MM/YYYY i.e. 06/01/2019 in the original form, but when I'm trying to format it into DD-MON-YY it is taking the value as MM/DD/YYYY ...
2 votes
1 answer
58 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 ...
  • 21
0 votes
1 answer
33 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 ...
0 votes
1 answer
39 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
0 votes
0 answers
73 views

Please help me create a "stopper" for this timer that I made in Google Sheets

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 ...
  • 1
0 votes
1 answer
24 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/...
0 votes
1 answer
15 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
0 votes
2 answers
35 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 ""?
0 votes
1 answer
79 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
0 votes
1 answer
180 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?
0 votes
0 answers
19 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 ...
0 votes
0 answers
8 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()...
-1 votes
1 answer
56 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().
0 votes
1 answer
62 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:...
0 votes
1 answer
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 ...
0 votes
1 answer
65 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
0 votes
0 answers
13 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 ...
  • 1
0 votes
1 answer
40 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 ...
0 votes
3 answers
98 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 ...
0 votes
0 answers
67 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
0 votes
1 answer
736 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,568
0 votes
3 answers
79 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
0 votes
0 answers
20 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 ...
0 votes
1 answer
118 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 ...
  • 193
0 votes
1 answer
337 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, ...
-1 votes
1 answer
77 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

1
2 3 4 5 6