Questions tagged [date]
Reference to a time interval of one day during a month and year. Not to be used for the 'social engagement' meaning.
74
questions
0
votes
1
answer
163
views
How can I Convert date from 'Mon Jun 05 2023 00:00:00 GMT+0100 (British Summer Time) to 'DD-MM-YYYY' in google APP Script
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('TRIGGER');
menu.addItem('create new doc' , 'createNewGoogleDocs');
menu.addToUi();
}
function createNewGoogleDocs(...
0
votes
1
answer
139
views
adding instances of dates repeating in column A across 11 tabs (sheets) in a sheet
How would I create a formula that shows a sum tally of how many instances each date appears on column A across multiple sheets?
I want to make a formula that will display a total tally of how many ...
- 21
2
votes
1
answer
218
views
Difference between today and a date with negative numbers
I need to calculate the difference in days between today and a set date allowing also negative differences.
Basically it's the shipping date for each order and how many days we have left till each ...
1
vote
1
answer
381
views
How do I calculate average of all days between 2 date columns in Google Sheets
I'm having trouble creating a formula, which would calculate the average of all days between 2 date columns into one cell.
I've tried Average(days(A2:A1000,B2:B1000)) and Average (B2:B1000-A2:A1000), ...
- 13
0
votes
1
answer
183
views
Looking for email notifications on trello
I would like to know if there is a way to set email Notifications,
Specifically in checklist I set a date and would like an email when it hits that date.
Ex, I have X arriving on (date) and would like ...
- 1
1
vote
1
answer
28
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 ...
- 25
1
vote
0
answers
16
views
VLOOKUPS with a date condition
So I have a table with clients' names. As my team expands, some clients are shifted from one team member to another one. My table looks like this
Starting date
End date
Client name
Team member name
...
- 11
1
vote
3
answers
79
views
Conditional formatting dependent on date and content in other cell
I have a date field, where I would like to do some conditional formatting.
So, if the date is more than 2 weeks ago AND the neighboring cells contains the words "Open" or Reminder" I ...
- 11
0
votes
0
answers
30
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 ...
1
vote
1
answer
26
views
I am trying to figure out how to change a column of many dates so that any date that is "less than" 1/1/2021 will automatically change to 1/1/2021 [closed]
I have the conditional formatting set to change the color of the cell. But I need the actual value to change to 1/1/2021. But only if it's less than. If it's more than that, then I don't want it to ...
- 11
1
vote
1
answer
671
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/...
- 113
0
votes
2
answers
120
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 ...
1
vote
2
answers
64
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 ...
- 11
0
votes
1
answer
245
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.
...
- 3
2
votes
1
answer
53
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 ...
- 21
1
vote
1
answer
48
views
Submission time removal
I am looking for a way to remove the submission date/time from the items seen when looking at entries in the Cognito forms. I have multiple employees that I am ok with them working after hours but the ...
- 11
1
vote
2
answers
62
views
I wrongly set the date of 2000 cells. I want to add 20 years of those cells. How?
My first thought was to
add a duplicate cell and show the formula as general number.
add another cell that adds 20 years worth of days.
change the third cell formula back to date.
But I feel like ...
- 19
0
votes
1
answer
975
views
How to open a specific tab based on today's date (Google Sheets) [duplicate]
I've made a 'Google Sheets' with 31 tabs for the dates of the month (1-31)
How do I write a script that makes the spreadsheet open to that days tab?
e.g. open tab 25 if today is Aug 25th
I imagine (...
1
vote
1
answer
44
views
Sum depending on value of Column B and Date in Column C
Column A: Name
Column B: Date (mm/dd/yy)
Column C: Value
I want to sum column C every time column A is a specific name & the corresponding date is before 2020
Small sample/Example of what I'm ...
- 11
1
vote
1
answer
328
views
Time until Specified Date
Looking to add an auto-updated amount of time countdown timer into Google Slides with the working script from this question.
Getting Setup
Go to Google Slides
Create a text box, right click to find ...
2
votes
1
answer
123
views
How to add every Sunday in sequential order with 3 blank cells under each date?
How to add every Sunday in sequential order with 3 blank cells under each date?
For example:
March 22
Blank cell
Blank cell
Blank cell
March 29
etc etc
- 21
2
votes
1
answer
108
views
Date highlighting of a cell, separated by weeks, given only a starting date and a cell range
I am trying to build a "life calendar" similar to this - https://labs.coruscantconsulting.co.uk/life/weeks/ - in Google Sheets.
I have the manually designed sheet finished, but want to add the ...
- 23
0
votes
1
answer
32
views
Changing cells formatting based on another cells date
I've been looking and can't quite find the answer I'm looking for.
I have a spreadsheet with columns Monday through Saturday with data lining up underneath. I would like the current day to highlight ...
2
votes
1
answer
61
views
Difficulty appending to cell in Google Sheets
My goal is to automate making a calendar on Google Sheets. I used =DATE(C1,A1,B1) to make a date and then each following cell, I added 1 to the previous cell.
However, now I want to also automate ...
0
votes
1
answer
1k
views
Query not working unless I change the Date format to Automatic [duplicate]
Query works fine if I change Hudson F to Automatic, if I change it to date it stops working. I need to view F as date and have the query work
=QUERY(Hudson2000!1:6634, "SELECT A, B, C, D, F, G WHERE ...
1
vote
1
answer
5k
views
How to make Google Sheets jump to closest date to today when opened?
I have a Google sheet containing a list of events, with event dates listed in one column. I'd like to have my Google Sheets spreadsheet jump to the closest date to today's date on opening (i.e., ...
- 31
1
vote
1
answer
3k
views
Google Docs date in yyyy/mm/dd format (year first)
My question is similar to Google Docs date in “dd/MM/yy” format and How can I change the input format for dates in Google Docs' spreadsheet?
BUT
unlike those answers, I'm trying to get the ...
0
votes
1
answer
491
views
Plus or minus quarters in Google Sheets using formulas
I have a date format like this
1Q16
2Q16
3Q16
4Q16
Is there a way to get...
input: 1Q16 + 4 output: 1Q17 and...
input: 1Q16 - 3 output: 2Q15
... using formulas only and not code?
- 1,014
1
vote
2
answers
13k
views
How to validate a date in Google Forms?
Google Forms include the possibility to perform some sort of validation on user input for checkboxes and text; the functionality is available very easily both via the UI and the API. Unfortunately, it ...
- 123
1
vote
1
answer
61
views
Format Google Analytics Date
I'm using the following formula to retrieve week day and day from a Google Analytics report in Google Spreadsheet:
=CONCATENATE(CHOOSE(WEEKDAY('90diaspessoas'!A16),"Domingo","Segunda","Terça","Quarta"...
- 123
5
votes
1
answer
461
views
Display dates in ISO 8601 format in the Microsoft Outlook web interface
Is it possible to display the dates in ISO 8601 format in the Microsoft Outlook web interface?
I read on https://support.office.com/en-us/article/Time-zone-and-language-settings-in-Outlook-Web-App-...
- 17.3k
2
votes
1
answer
2k
views
Date Format Lost When Combining Two Cells Using "&" and CONCATENATE in Google Sheets
I'm trying to figure out "how to" maintain the date format when I concatenate a date formatted cell with a nondate formatted cell.
Result: The Date Format Is Lost
Formulas Used
Desired Result: I ...
- 2,857
-1
votes
1
answer
63
views
Look for today() in range D5:AP5 is X in the range D8:AP8
I used this formula: - Look for "X" in range D8:AP8 = found/not found, which is fine. Formula:
=IF(ISERROR(MATCH("X";NW_Calendar!D8:AP8;0));"Not found";"Found")
but I would like to have it like ...
- 1
1
vote
1
answer
325
views
Formula to compute the cardinality of the intersection of two sets of dates
Given:
Two dates, the bounds of a date range;
A list of dates (given in-extenso in a line or a column);
Is there a way, in Google Spreadsheet, to get the number of dates (days) in the date range ...
- 113
5
votes
2
answers
3k
views
How to sort Trello cards by date added
Trello is rad. But one thing I haven't figured out:
How to sort cards in a list by the date that the cards were added to the list (or to the board)?
Also, is it possible to show (as a custom field?) ...
- 5,429
1
vote
2
answers
79
views
Need to identify when a specific amount of time has past given a specific span of time
I have a start date and a payout date which is 70 days from the start date. Throughout the year I need to know when each time 70 days has past and when it does is that date between the start and ...
- 13
7
votes
1
answer
28k
views
Trying to subtract X days from a defined date in Google Sheets
I'm trying to figure out "how to" subtract days from a date in Google Sheets.
EXAMPLE
Col A (original date) Col D (date - 5 days)
08-08-16 08-03-16
A1 will display the ...
- 2,857
2
votes
1
answer
222
views
Google sheets recently changed American datetime format, how do I change it back?
Google sheets recently changed American datetime format, how do I change it back?
It used to default to using "MM/dd/yyyy" but as of a couple weeks ago now when I enter a date it defaults to "MM/dd" ...
- 21
7
votes
2
answers
27k
views
Automatic date in Google Docs
Is there a way in Google Docs to automatically update the date in a header of the page, so that whenever I print the document it will have today's date?
- 1,495
-1
votes
1
answer
2k
views
An "Updated on" field that records the last time that a particular column changed [duplicate]
Is it possible to create a "last updated" field under Google Sheets?
I have a status column, where the status progresses something like:
New -> Pending -> Started -> Done
I would like to add a "...
- 113
6
votes
1
answer
1k
views
How to edit dates in Google Sheets as ISO 8601?
I know how to make Google Sheets display dates in ISO 8601 format (YYYY-MM-DD). Now, how do I make them not only display, but edit in the same format?
See the edit line below:
Moreover, if that is ...
- 256
4
votes
2
answers
7k
views
Google sheets MM-DD-YYYY to DD-MM-YYYY
I downloaded CSV data from my Etsy shop to calculate taxes. I'd like to have the date "copied" properly.
The CSV date I get from Etsy is formatted as "02/09/16"
This is "2 September 2016" in Dutch ...
- 41
1
vote
1
answer
855
views
How can I get the halfway date of two dates in Google Sheets?
I have two date columns in mm-yy format and I need to know the halfway date of these two. What formula do I use? I tried simple subtraction of the two dates and then divided it by two but it didn't ...
- 101
1
vote
1
answer
2k
views
How to convert text of the form Month-Year to date format?
How do I convert the text "Oct-14" to date, so that it's being read as "Oct 01, 2014. I tried to go to Format > Date and selected the format: Oct-2014, but google sheets is interpreting it as "Oct 14, ...
- 101
5
votes
1
answer
7k
views
Insert current date to cell based on content in other cell
Background:
I am trying to create a sheet which tracks my costs on daily basis. Instead of entering the current date everyday, I want the sheet to store the current day based on the content I enter ...
- 153
3
votes
1
answer
3k
views
Why does Google Spreadsheet use days as unit of time when entering a number?
When using Google Spreadsheet today, I noticed that when entering a date as a simple number sequence (e.g., 151127), and choosing date as format, it ended up being a completely different date than the ...
- 133
1
vote
1
answer
2k
views
Search Google with a date/time range
I want to restrict Google search to a specific date/time range. How can this be done?
- 133
2
votes
2
answers
103
views
Date automation down columns
I know how to get Google Spreadsheets to automate dates down a column. Write a date, left click on the crosshair in the bottom right hand corner, drag down and let go.
The thing I'm hoping to do ...
- 21
2
votes
1
answer
767
views
How to hide date on Tumblr pages?
The documentation on Tumblr (https://www.tumblr.com/docs/en/custom_themes#dates) says
{block:Date} {/block:Date}
Rendered for all posts.
Always wrap dates in this block so they will be ...
- 23
17
votes
4
answers
9k
views
How to add a line break in a custom date format in Google Spreadsheets?
Google Spreadsheets give us a tool to create and use a customized data format for date values.
While the tool allows us to use arbitrary characters, like slashes and commas between the date fields (...
- 769