66
You can use the before and after keywords in your search, for example:
after:2012/4/26 before:2012/5/3
To search for emails between April 26, 2012 and May 3, 2012.
65
It’s possible to insert todays date through a macro.
Open your Google Document and under Tools select Script editor. This opens Google's script editor where it’s possible to create macros for Google Documents.
Paste this script and save it as Date Macro or something: (also available here)
/**
* The onOpen function runs automatically when the Google ...
27
In the right hand side of the gmail search bar there is a drop-down arrow- click it.
The drop-down includes a date range option, and you should be all set from there
14
Similar to this question, you'll want to use conditional formatting.
Select the cells and select Format > Conditional Formatting
Select "Date is" "today" and select a Background colour
13
Yes.
Use Conditional Formatting with three rules: (Format -> Conditional formatting)
"Date is before" "in the past week" -> red
"Date is after" in the past week" -> green
"Date is" "in the past week" -> orange
This will colour all dates more than a week away in green, all dates coming in the next week orange and the remainder of the dates in red. Empty ...
8
Go to Settings (Cog wheel on the upper-right side)
Search for Date format
Switch to 31/12/2012
Click Save at to the or bottom of the settings page
8
Assuming your string is in cell A1, this formula will convert it to a date. You can then format the date however you prefer.
=date(left(A1,4),mid(A1,5,2),right(A1,2))
Or, take the leftmost four characters as the year, the rightmost two characters as the day, and two characters in the middle starting at position 5 as the month, and convert it to a date.
...
6
I think I've got the solution for you. It's not done all automatically, but it will result in your proposal:
In your file the date is written as MM-DD-YYYY. I made the assumption
that the date range covers december and NOT random dates throughout
the year. Therefore I had to re-arrange the date format to
DD-MM-YYYY.
As a guidance I used the following ...
answered Dec 21 '12 at 22:50
Jacob Jan Tuinstra
18.2k12 gold badges72 silver badges159 bronze badges
6
You can do the formatting on the fly:
=text(B2,"m/d/yyyy")&C2
Google Sheets Help: TEXT()
5
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 ...
4
In MS Excel try this:
=A1+TIME(2,0,0)
Same with Google:
=A1+Time(3,0,0)
Adds 3 'Hours', 0 'Minutes', 0 'Seconds' to the date/time value of A1
Additionally if only you use the main sheet you can go into the sheet settings and change it's timezone accordingly.
4
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 ...
4
If you want a proper date (recommended) please try:
=date(left(C2,4),mid(C2,5,2),right(C2,2))
Custom Format as Number mm/dd/yyyy to show leading 0s.
If you want a text string to include leading 0s please try:
=mid(C2,5,2)&"/"&right(C2,2)&"/"&left(C2,4)
4
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:
3
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
3
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 ...
answered Jul 19 '14 at 4:02
Eight Days of Malaise
14.5k6 gold badges50 silver badges82 bronze badges
3
Formatting is not the answer, because it doesn't affect the underlying values. You need to make the values updatable instead.
One way is to make a reference-data place somewhere in one of your sheets (or maybe add a new sheet just for that), and put the year value in there.
Then in the first cell where you want the date values to appear, use this ...
3
Use this to add the dates:
Code
function onEdit(e) {
var aCell = e.source.getActiveCell(), col = aCell.getColumn();
if(col == 2 || col == 4 || col == 6) {
var adjacentCell = aCell.offset(0, 1);
var newDate = Utilities.formatDate(new Date(),
"GMT+1", "dd/MM/yyyy kk:mm:ss");
adjacentCell.setValue(newDate);
}
}
Example
I've ...
3
I tried Ctrl+; and it shows the local date (and time with Ctrl+Shift+;) so the issue appears to be fixed.
3
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.
Instruction:
Select cell A1
Go to Format/Conditional Formatting
Below "Format cells if..." choose "Custom formula is"
type: =DATEDIF(A1,TODAY(),"D")>10
3
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 ...
3
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 ...
2
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:
=and(A1>today()-7,A1<today()+7)
where six days before today, today and six days after today ...
2
I don't know if there's a way to do that, except by writing some scripts (which can get pretty complex and probably not so reliable)
There's a simpler workaround, though. You could:
add a new column to the table, next to the date
store the difference (in days) between that date and today (for example, if the date cell in the first row is C2, you could ...
2
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:
Ctrl+;
in the cell starting a new row. For such cells to be populated with the same result ...
2
For todays date, you can use the following formula.
Formula
=TODAY()
Explained
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.
Note
Please realize that the value returned by the TODAY() formula isn't permanent. ...
answered Oct 28 '14 at 14:50
Jacob Jan Tuinstra
18.2k12 gold badges72 silver badges159 bronze badges
2
This has now been resolved by Google Spreadsheets. Pasting in the date:
30/09/2013 21:15
And clicking Format > Number > Date time now immediately turns the cell into a date time cell (you can see this as it adds seconds).
Only top voted, non community-wiki answers of a minimum length are eligible
