Questions tagged [google-sheets-dates]
Use for questions related to how Google Sheets handle dates, times, durations.
308
questions
0
votes
1
answer
13
views
Fill a column with dates for two days per week
I need to keep track of an employee who works Mondays and Thursdays. I need to fill a column with dates for these days. I know how to do sequential dates and I have tried dragging down from the corner ...
- 12.6k
1
vote
1
answer
20
views
Adding (and updating) time stamp when cell color is changed in Google Sheets
I'm very new to scripts so please bear with me. I want to make a condition where if a cell color is changed, a new timestamp is created. So a cell will start from white (unused) and change to ~3 ...
- 12.6k
2
votes
1
answer
45
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 ...
CommunityBot
- 1
0
votes
1
answer
24
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 ...
CommunityBot
- 1
1
vote
1
answer
149
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 ...
CommunityBot
- 1
1
vote
2
answers
39
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 ...
CommunityBot
- 1
2
votes
1
answer
62
views
Making a bubble chart in Goggle Sheets for year-on-year comparison - ie year versus month?
I'd like to make a multi-series bubble chart from 17 years of sporadic data with Year on the Y axis and Month+Day on the X axis and the bubble size corresponding to each data point's value.
I've ...
0
votes
2
answers
1k
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 ...
CommunityBot
- 1
0
votes
1
answer
437
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 ...
CommunityBot
- 1
0
votes
1
answer
718
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 ...
CommunityBot
- 1
2
votes
1
answer
62
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 ...
CommunityBot
- 1
0
votes
1
answer
34
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 ...
CommunityBot
- 1
2
votes
1
answer
204
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 ...
CommunityBot
- 1
2
votes
2
answers
42
views
Query form data by date to return counts by item and category
I'm a teacher and need help with a Google Form that collects the following info:
Timestamp
Name
Class
Lunch option
All of the data comes into a Google sheet. I'm using successfully using the QUERY ...
- 43.9k
0
votes
1
answer
554
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 ...
CommunityBot
- 1
1
vote
1
answer
268
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 ...
CommunityBot
- 1
1
vote
1
answer
29
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/...
CommunityBot
- 1
1
vote
1
answer
246
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 ...
CommunityBot
- 1
1
vote
1
answer
86
views
How to conditionally format a date column based on before/after another date? [closed]
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 ...
CommunityBot
- 1
1
vote
1
answer
25
views
Need help creating a points-based attendance google sheet [closed]
I'm new to using Google sheet formulas, so I would really appreciate help/advice. I'm trying to set up a sheet that uses a points-based system to track attendance - basically subtracting a point for ...
- 43.9k
1
vote
1
answer
41
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 ...
CommunityBot
- 1
0
votes
1
answer
31
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 ...
CommunityBot
- 1
1
vote
4
answers
503
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 ...
CommunityBot
- 1
1
vote
1
answer
121
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 ...
CommunityBot
- 1
1
vote
1
answer
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 ...
CommunityBot
- 1
2
votes
2
answers
95
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 ...
CommunityBot
- 1
1
vote
1
answer
18
views
How to best deal with rounding errors when comparing dates/times
I have a column that contains a series of date/time values
. I am wanting to determine how many of the time slots fall between 10:00 and 11:00 am, 11:00 am and noon, etc. To pull the time out of my ...
- 12.6k
1
vote
1
answer
58
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 ...
CommunityBot
- 1
0
votes
1
answer
40
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 ...
CommunityBot
- 1
0
votes
1
answer
428
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 ...
CommunityBot
- 1
1
vote
1
answer
201
views
Google Sheets time sorting chart
I have a MailChimp data export in google sheets and I want to create some charts that can show the best time of day/day of the week based on my data for our mail outs when compared to open rate (%). I ...
CommunityBot
- 1
-2
votes
2
answers
3k
views
How to make date automatically display only day number and suffix (1st, 2nd, etc.)?
Title explains it. I need to know how to create a custom number format in which a cell would display only the date and suffix of a full date entered into the cell
example:1/05/2019 -> 5th
I have ...
1
vote
1
answer
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'...
CommunityBot
- 1
1
vote
2
answers
859
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 ...
CommunityBot
- 1
-2
votes
1
answer
28
views
How do I use formulas to find the midpoint and other milestones between two dates?
I am trying to make a Google Sheets document for work that will contain the date someone signed, the date that the wedding is occurring, when the first and second payments are due, and when to send ...
- 1,831
1
vote
1
answer
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-...
CommunityBot
- 1
0
votes
1
answer
108
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 ...
CommunityBot
- 1
2
votes
2
answers
2k
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/...
CommunityBot
- 1
2
votes
1
answer
475
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 ...
CommunityBot
- 1
-1
votes
1
answer
29
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 ...
CommunityBot
- 1
0
votes
1
answer
72
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 ...
CommunityBot
- 1
0
votes
1
answer
95
views
How to get total salary from each month from date?
Given that image below, B is date in format [month/day/year] and salary.
From that date, can we total up salary from specific month in yellow table?
For instance, we want the total number of salary ...
- 2,984
1
vote
1
answer
148
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 ...
CommunityBot
- 1
0
votes
1
answer
138
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 ...
CommunityBot
- 1
0
votes
1
answer
895
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 ...
CommunityBot
- 1
2
votes
1
answer
364
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 ...
CommunityBot
- 1
2
votes
1
answer
35
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 ...
CommunityBot
- 1
0
votes
1
answer
430
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 ...
CommunityBot
- 1
1
vote
1
answer
34
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 ...
CommunityBot
- 1
1
vote
1
answer
30
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, ...
CommunityBot
- 1