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.
Select cell A1
Go to Format/Conditional Formatting
Below "Format cells if..." choose "Custom formula is"
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. You can also reverse it:
sort list "X" by time in list descending
You can apply the command at certain times, e.g.:
every day at 6am, sort list "X" by time ...
This can be achieved with the built-in developer tools of the browser. They can be used to input any value as a custom number format, including text with newlines and tabs.
Start adding a custom number format.
Press F12 to open the developer tools.
Select the console tab.
Paste the following text into the console:
I believe that the only way to achieve this is to set a locale in which this date format is a default one. For example I have it as default because I have Polish locale.
Link to settings: https://drive.google.com/settings
Use the formula =IF([cell on same row in D column] <> "", today()-[cell on same row in D column], "")
So in D2 (for example):
=IF(D2 <> "", TODAY()-D2, "")
TODAY() returns the current date. The current date minus the specified date equals the days passed between the dates.
The code above means "If D2 is not empty set current cells value to ...
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 options:
Then you can change the date format:
At the bottom of most Blogspot posts you will find a link like the following:
Subscribe to: Post Comments (Atom)
When you click on that it will show you a code dump. It is the RSS feed for that particular post, including all the comments.
All you need to then do is look for the comment you're interested in and then go to the first <published> that ...
The accepted answer mentions colours that are not mentioned in the question and only looks back. Also "Date is before" "in the past week" does not highlight dates within seven days of today. I suggest a Conditional formatting Custom formula is of the type:
where six days before today, today and six days after today ...
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 requirement to be for a non-volatile solution. Hence suggest:
in the cell starting a new row. For such cells to be populated with the same result ...
For todays date, you can use the following formula.
The TODAY() formula provides the current date with no time component. It's better to use that, than to use the NOW() formula. This will update on every edit made to the sheet, see reference.
Please realize that the value returned by the TODAY() formula isn't permanent. ...
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 the end of the month in the second row with the end of the month for the date in cell a1 + 70 days.
If the date is 1/1/2017 then 70 days later is 3/12/2017, so ...
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 as D.
To get the new quarter add D to Q and modulo by 4. This will be correct except it will equal 0 when it should be 4. Wrap this whole portion in an if() to ...
I put together a redirect for searching just 'back n days' that does the simple (and for me, most common use case)
Writing a search for ‘inbox NOT including today’ is slightly counterintuitive and somewhat of a pain so I put together a custom redirect of my own that brings up the stuff from yesterday:
That redirects you ...
The solution is to set the correct Local under the Spreadsheet's settings ("This affects formatting defaults, such as currency."). So if you want US.locale style (MM-DD-YYYY), then choose US.Locale. If you are in a different locale and want formatting
This has now been resolved by Google Spreadsheets. Pasting in the date:
And clicking Format > Number > Date time now immediately turns the cell into a date time cell (you can see this as it adds seconds).
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 string appears after the q= flag in the url. For time based searches you add the tbs=qdr: flag followed by the time period value you wish to use. Some of the ...
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 formatting nor does \n.
Assuming your date is in cell A1 and set to read Saturday, October 31, 2015 (which is one of the defaults)
You can break it down and ...