40
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.
17
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.
12
The key is to enter at least the first two values to get auto fill to work with a pattern
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 ...
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
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
18.2k12 gold badges72 silver badges159 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
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.
2
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)
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
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.
2
Set your start and end fields to time format (Format -> Number -> Time) and your time difference calculation field to duration format (Format -> Number -> Duration).
Once you have done that you can just add and subtract times like normal number to get the time difference. However, you will get a negative time if one event stretches past midnight....
1
A custom formula in the Conditional Formatting interface can do this for you. It looks like your dates in Column C are formatted as dates, and not just text. So I'll demonstrate a formula that will work in that case. (If your Column C is just text, you can adjust the formula to look for the strings "Saturday" or "Sunday".)
Select Cell D13. Click on the Fill ...
1
Your string is space-delimited, so we can cut it up using SPLIT, then grab the parts we need by number using INDEX.
The month needs a bit of coercing because it is a word, so I suggest asking the MONTH function for the date of that month's 1st, but then just the month-number of that month. It is able to convert the string "Mar1" to a date, for example, and ...
1
The short answer is yes, but I consider more convenient to use a custom function.
Google Sheets handle dates as serialized numbers. The timezone is based on the spreadsheet timezone settings. Unfortunately there isn't a built-in spreadsheet function that converts text as the one shown on the question directly, your formula should extract the parts and apply ...
1
Looking at you example spreadsheet the calculation in seconds is done with the following formula:
=86400*(if(D4-C4<0,1,0)+D4-C4)
This is the breakdown of the formula:
time and dates in spreadsheets are floating point numbers.
D4-C4 is subtracting the start time from the end time.
The if statement is cheeking if the subtraction is negative, that ...
1
Walter. Let me suggest an entirely different (and shorter) approach:
=IF((TIMEVALUE(H2)*24*60)>5,0,CHOOSE(CEILING(((TIMEVALUE(H2)-TIMEVALUE("00:00:30"))*24*60),0.5)/0.5,150,100,75,50,40,35,30,25,25,0))
or displayed differently:
=IF(
(TIMEVALUE(H2)*24*60)>5,0,
CHOOSE(
CEILING(((TIMEVALUE(H2)-TIMEVALUE("00:00:30"))*24*60),0.5)/0.5, 150,100,75,50,40,35,...
1
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
Only top voted, non community-wiki answers of a minimum length are eligible
