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
4
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
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
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 ...
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 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
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....
2
In fact, yes it is expected behavior. All of the time/date based formulas default to lowest/highest value when there is no real reference.
To prevent it, you are supposed to perform a simple logic check for real data:
=IF(LEN(A1), MONTH(A1), )
=IF(A1<>"", MONTH(A1), )
Also, Google Documentation is way too empty!
1
=ARRAYFORMULA(IF(LEN(A2:A),
TEXT(MONTH(A2:A)+(DAY(A2:A)/DAY(EOMONTH(A2:A, 0))), "0.00")*1, ))
1
=ARRAYFORMULA(TEXT(SUBSTITUTE(SUBSTITUTE(A2:A, " h ", ":"), " m", ":00"), "[h]:mm"))
1
={A1:E1; QUERY(SORT(VLOOKUP(QUERY({ROW(D:D), SORT(A:E)}, K1, 0),
{ROW(D:D), SORT(A:E)}, {2, 3, 4, 5, 6}, 0), 4, 0),
"where Col4 <="&INDEX(QUERY(SORT(VLOOKUP(QUERY({ROW(D:D), SORT(A:E)}, K1, 0),
{ROW(D:D), SORT(A:E)}, {2, 3, 4, 5, 6}, 0), 4, 0),
"select Col4
order by Col4 desc"), 2, 1)&"
and Col4 >="&INDEX(UNIQUE(QUERY(SORT(...
1
white: cell is not empty
green: =IF((TODAY()=$C2+1)+(TODAY()=$C2),1)
yellow: =IF(TODAY()=$C2+2,1)
red: =IF(TODAY()=$C2+3,1)
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
={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), REGEXEXTRACT(A1, "\d+")),
TEXT(INDEX(SPLIT(A1, " "), 1, 4), "hh:mm:ss")}
with timezone in account:
={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), REGEXEXTRACT(A1, "\d+")),
TEXT(INDEX(SPLIT(A1, " "), 1, 4)+IFERROR(
TIMEVALUE(JOIN(":", SPLIT(REGEXREPLACE(REGEXEXTRACT(A1, "\+(\d+)"),
"(.{2})", "/$1"),"...
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
TOTAL:
=INT((SUM(ARRAYFORMULA(M1:M14-L1:L14)))/365)&" Years, "&
INT(MOD((SUM(ARRAYFORMULA(M1:M14-L1:L14))),365)/30)&" Months, "&
ROUND(MOD((MOD((SUM(ARRAYFORMULA(M1:M14-L1:L14))),365)),30),0)&" Days"
AVERAGE:
=INT((AVERAGE(ARRAYFORMULA(M1:M14-L1:L14)))/365)&" Years, "&
INT(MOD((AVERAGE(ARRAYFORMULA(M1:M14-L1:L14))),365)/30)&...
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
with one more column:
cell A1: =TEXT(DATE(2018,7,15)+B1,"m/d")&"-"&TEXT(DATE(2018,7,21)+B1,"m/d")
cell B1: 7
cell B2: =B1+7
and then drag down A1 and B2
https://docs.google.com/spreadsheets/d/
1
sheet Monday:
A1: =DATE(2018;7;16) // =DATE(2018,7,16)
sheet Tuesday:
A1: ='sheet Monday'!A1+1
sheet Wensday:
A1: ='sheet Monday'!A1+2
sheet Thursday:
A1: ='sheet Monday'!A1+3
etc.
if you need something more complex (like auto-updating), you can use old pure brute force:
european syntax - sheet Monday > A1:
=IF(AND(DAYS360("1.1.2018";TODAY())>=...
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
1
=DAYS("01/01/2050", TODAY())
=DAYS(A2, TODAY())
1
Internally both Google and Excel store timestamps as a day + fraction.
So Jan 3 2018 + 3 = Jan 6 2018.
You can see this if you display a date as a number.
Timestamps are stored as a decimal fraction of a day. 6 a.m. is .25 etc.
Formatting changes the way a number is interpreted. In Sheets there are a raft of general building blocks for constructing ...
Only top voted, non community-wiki answers of a minimum length are eligible
