Hot answers tagged

7 votes

How to add a line break in a custom date format in Google Spreadsheets?

You can insert the vertical tab unicode character U+000b You can copy and paste it from between these brackets: ] [
ninjagecko's user avatar
6 votes
Accepted

Date Format Lost When Combining Two Cells Using "&" and CONCATENATE in Google Sheets

You can do the formatting on the fly: =text(B2,"m/d/yyyy")&C2 Google Sheets Help: TEXT()
pnuts's user avatar
  • 17.4k
6 votes

How to add a line break in a custom date format in Google Spreadsheets?

Google Sheets will both receive a number format with multiple lines and display cells using it: The problem is that the "Custom number format" input field does not support line breaks/...
metarmask's user avatar
  • 161
6 votes

Advanced date logic in Google Spreadsheets

In case you need more precise times than in the accepted answer, you can use DATEDIF to specify a certain difference between dates. Example: format cell A1 if date in A1 is older than 10 days. ...
alex sichwart's user avatar
5 votes

How to sort Trello cards by date added

You can use Butler for Trello, which is a bot that automates Trello (I'm the author). This command: sort list "X" by time in list sorts a list by the length of time that a card has been in the list. ...
Ludable's user avatar
  • 381
4 votes
Accepted

Display dates in ISO 8601 format in the Microsoft Outlook web interface

You have to click on the setting button (the wheel) right when the page is loading, before your name appears on the top right. If you do so, then the setting menu has many more entries, including ...
Franck Dernoncourt's user avatar
4 votes

Difference between today and a date with negative numbers

Have you tried substracting both values? You could format it as number if it shows like a date in year 1899.
Martín's user avatar
  • 1,233
3 votes
Accepted

Need to identify when a specific amount of time has past given a specific span of time

In cell D3 I owuld put the following formula: =if(EOMONTH($A1+70,0)=EOMONTH(D2,0),"Output","No report due") I assumed the $A1 to make sure that it works for all columns. The formula just compares ...
mhoran_psprep's user avatar
3 votes
Accepted

Plus or minus quarters in Google Sheets using formulas

Using mid() extract the quarter portion of the original date, here after referred to as Q. Similarly extract the year portion, here after referred to as Y. The amount to be added will be referred to ...
Jared Pinkham's user avatar
3 votes

How can I search for Gmail messages on a particular date?

For emails on a particular date (e.g. on 12 July 2017) enter this in search box: after:2017/7/12 before:2017/7/13
ePandit's user avatar
  • 139
3 votes
Accepted

Pre-fill a cell with today's date

As mentioned by @Jacob Jan Tuinstra, TODAY updates automatically (so does NOW) and as I don't see the value of an entire column within which each cell always shows the current date, I interpret the ...
pnuts's user avatar
  • 17.4k
3 votes

How to add dashes to an 8-digit date (yyyymmdd) in Google Spreadsheet?

One approach might be to use one column formatted as plain text to enter the dates in yyyymmdd format, then use an array formula in another column to generate the dates in the correct format. For ...
The God of Biscuits's user avatar
2 votes

How to add a line break in a custom date format in Google Spreadsheets?

You can do it in a fairly ugly way by doing the full date formatting without the line break and then parse it out and add the line break in the formula. CHAR(10) doesn’t seem to work in the custom ...
Tom Woodward's user avatar
  • 1,844
2 votes
Accepted

Search Google with a date/time range

I believe you are asking how to perform a Google search with a time value flag on the search. This can be done by your program building its own search url. You likely already know that the searched ...
Bradley A. Tetreault's user avatar
2 votes

How to edit dates in Google Sheets as ISO 8601?

Partial answer According to https://en.wikipedia.org/wiki/Date_format_by_country, the following locales use YMD date format: Afghanistan, Albania, Bangladesh, Cambodia, Canada, China, People's ...
Rubén's user avatar
  • 44.6k
2 votes

Google Docs date in yyyy/mm/dd format (year first)

I did not realize there was a "Format > Number > More formats > More date n Time formats" where I could specify the exact format I want, since the default date format is not what I am looking for. It ...
Cyril Duchon-Doris's user avatar
2 votes

How to make Google Sheets jump to closest date to today when opened?

I was able to piece together a solution thanks to the help of @Rubén who provided the elements. Perhaps there is a more elegant solution (with a single formula?), but this working for me. This ...
user196554's user avatar
2 votes

How to add every Sunday in sequential order with 3 blank cells under each date?

Solution with a Formula : A1 : =ArrayFormula(if( mod(ROW(A1:A100),4)=1, text((round(ROW(A1:A100)/4)*7)+43730,"dddd dd mmm yy") ...
pjmg's user avatar
  • 1,968
1 vote
Accepted

How to validate a date in Google Forms?

In contrary to Short text, paragraph and checkbox questions, Google Forms doesn't have a way to validate input through date questions. Ref. Set rules for your form There is no way to add client-side ...
Rubén's user avatar
  • 44.6k
1 vote

Format Google Analytics Date

You need to use TEXT, instead of TO_TEXT: =TEXT('90diaspessoas'!A16,"dd-mm-yyyy" ) As long as underlying date number is correct, This will work. OR Set formatting in '90diaspessoas'!A16 ...
TheMaster's user avatar
  • 3,272
1 vote

Look for today() in range D5:AP5 is X in the range D8:AP8

Please try: =IF(SUMPRODUCT(--(D5:AP5=TODAY());--(D8:AP8="X"))=1;"Ok","Not OK") SUMPRODUCT
pnuts's user avatar
  • 17.4k

Only top scored, non community-wiki answers of a minimum length are eligible