43
All date/time values in spreadsheets are internally handled as floating point values. To add 3 hours to a date/time just add (3/24) to the original date/time.
=F3+(3/24)
This also works in MS Excel.
19
I prefer a formula that is more transparent, with fewer magic numbers. It makes it easier to see what's going on and also avoids the likelihood of having a bug (like the current top answer to this question which is off by 1 day):
=A1/60/60/24 + DATE(1970,1,1)
And if you want to shift it from UTC to a timestamp that is offset by some number of hours, for ...
18
As it was already pointed out in other answers, you have to select at least two cells containing successive dates.
However, this didn't work for me at first because my Google Sheet had the wrong locale and the cells had the wrong formatting.
Set the locale for your Google Sheet:
File menu → Spreadsheet settings... → Set the correct locale
Configure the ...
16
See also: Convert a date in Google Spreadsheet?
=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.
13
The key is to enter at least the first two values to get auto fill to work with a pattern
11
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.
...
9
I use this formula to convert the date time values IFTTT spits out to a useful format:
=VALUE(SUBSTITUTE(A1, " at ", " "))
Where A1 is the cell containing the date string
This gives you a decimal number that you can format as "Date Time".
6
Here's another way to add days to a date that builds on the previous answer.
Let's say you have this in Cell A1
DATE(2010, 12, 01)
You can add 5 days to it in Cell A2 using the DATEVALUE command like this
DATEVALUE(A1) +5
6
Short answer
Assuming that you want to compare a date in the cell A1 with the current date, use the following formula:
=A1>TODAY()
The above will return TRUE if the value of the cell A1 is the greater than the current date.
Explanation
In Google Sheets the comparison functions and operators could be used with dates if they are properly formatted.
By ...
5
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)
3
The result was one day ahead for me, so I changed it to =to_date((A1+2209161600)/86400)
3
The way I do it is:
place the date in row A1, in row A2 using the formula do = A1 + [number of Days]
Eg.:
A1 [11/12/2018]
A2 [=A1+3]
A2 [November/15/2018]
(I format the date with an easy to interpret format (select row > Format > Number > More Formats > More date and time formats)
3
My problem was that Google Sheets did not recognise what I was typing in as a date (24.05.2017) so when I changed it to 24/05/2017 it dragged consecutive dates immediately without having to highlight any other cells.
3
Please try:
=Query(Data!A6:L1397,"Select A,B,C,D,E,F,G,H,I,J,K,L where A>=date
'"&TEXT(A1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(B1,"yyyy-mm-dd")&"'")
Note Column references for the source data set and that the likes of date '"&TEXT(A1,"yyyy-mm-dd")&"' are to accommodate the possible use of =today().
May be worth mention ...
3
There are two possible options for you to consider.
Formula
"28/06/2015"-TODAY())
DATEDIF(TODAY(), "28/06/2015", "D")
Todays date is: 28/10/2014. The result will be an integer (243).
Note
Make sure to format the outcome as a number and not a date. If you choose the NOW() formula, you introduce a time component. This will break up the calculation and ...
answered Oct 28 '14 at 18:53
Jacob Jan Tuinstra
19.1k13 gold badges78 silver badges164 bronze badges
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 ...
3
A1:
=ARRAYFORMULA(JOIN("-",TEXT({"7/22","7/28"}+(COLUMN(A1)-1)*7,"M/DD")))
Drag to right.
Two dates are initialized and a column number*7 is added per each column.
TEXT to format dates " m/dd"
JOIN the 2 dates by -
3
Instead of
date 'TODAY()'
use
date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'"
NOTES:
The last " should be placed in a proper place in order to have a text value as the second argument of QUERY.
'TODAY()' doesn't returns Today's date, it returns a string with the word TODAY followed by parenthesis.
Try this
=ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)},
"...
2
Found the answer, this formula will do it:
=round(abs("2014-01-08"-NOW()),0)
Where "2014-01-08" is the date in the future.
2
The following custom function will transform the string date notation into a date notation:
Code
function myDate(range) {
var output = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var rSplit = range[i][0].split(" "), corr = 0;
if(rSplit[4].substring(5,7) == "PM") {
corr = 10;
}
var month = parseInt(new Date(Date....
answered Sep 15 '13 at 13:31
Jacob Jan Tuinstra
19.1k13 gold badges78 silver badges164 bronze badges
2
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 also handles auto-formatting of further inputs
2
Monday Sheet:
=WORKDAY.INTL(TODAY()-1,1,"0111111")
String method: weekends can be specified using seven 0’s and 1’s, where the first number in the set represents Monday and the last number is for Sunday. A zero means that the day is a work day, a 1 means that the day is a weekend. For example, “0000011” would mean Saturday and Sunday are weekends.
Only top voted, non community-wiki answers of a minimum length are eligible
