91
Google has added a new number format called Duration. Start by formatting the start and end fields to Format -> Number -> DateTime or Format -> Number -> Time and your calculation field to Format -> Number -> Duration
Once you have done that you can subtract the fields to get the difference as noted by Stefano Palazzo in his answer.
37
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 ...
29
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 ...
21
I had been struggling with this for a long time, but finally cracked it:
Use conditional formatting on the column with the dates and type the following as a custom formula:
=or(WEEKDAY(A1)=1,WEEKDAY(A1)=7)
where A1 is the first date in the column.
This will apply the conditional formatting to all weekdays with a value of 1 (Sunday) and 7 (Saturday).
17
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.
15
Easy (but a bit tedious!) in new Google Sheets. For Conditional Formatting a new rule is required for each colour, roughly. (One colour can usually be skipped from rules and instead applied with normal formatting - which would be overidden where any of the conditions for CF apply.) So expect to repeat the basics of the following at least five times.
Say the ...
15
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.
...
13
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
12
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 ...
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".
9
So much simpler: look at this answer from Excel Forum:
B2: 23:00
C2: 1:37
D2: =C2-B2+(B2>C2)
Why it works, time is a fraction of a day, the comparison B2>C2 returns True (1) or False (0), if true 1 day (24 hours) is added.
9
I've done a lot of experimentation. This is the easiest way to calculate a time delta in Google Spreadsheets. Format the cell containing the formula like this:
Format > Number > More Formats > More date and time formats, delete "second" and :. Then, format the End time and Start time cells like this: h:mm am/pm.
Use the formula =abs(end time - start time)...
9
If you add the following formula in D2, then the minutes are calculated automatically:
Formula
=ARRAYFORMULA(IF(ISBLANK(B2:B)=FALSE,((C2:C-B2:B)*24*60),""))
Explained
The difference between the times, as per decimal format, is expressed in days. Therefore multiplying times 24 time 60 will yield minutes
Remark
There is one prerequisite: column D needs ...
answered Jul 21 '13 at 13:03
Jacob Jan Tuinstra
21.3k16 gold badges89 silver badges183 bronze badges
9
The reason is that column is also formatted as a date. Format it as a number and you'll get what you're after.
Google Sheets stores dates as a number of days from 12/31/1899. Your YEAR() is appropriately pulling out "2010" (for example) but is then converting it to a date. 2010 days after 12/31/1899 is 7/2/1905. (The big clue is that the two sample rows you ...
7
For a more robust solution, we uses a custom Function.
1. Adding the custom function
Using the Script Editor (follow instruction in https://developers.google.com/apps-script/execution_custom_functions) - writes:
function toEpoch (indate) {
return indate.getTime();
}
2. Add formular
Then in the cell, writes:
=(toEpoch(C2)-toEpoch(B2)) / 60*1000
Which ...
7
={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"),"...
6
If the dates are in column A, select them, then do: Format > Conditional formatting... > Format cells if... > Custom formula and put
=and(isblank(A:A)=false, or(weekday(A:A)=1, weekday(A:A)=7))
Some explanation:
weekday(A:A) returns 1-7, for day of the week, and
or(weekday(A:A)=1, weekday(A:A)=7) returns true, if it's Sunday (1) or Saturday (7).
This ...
6
The next level would be to use ARRAYFORMULA:
=ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATEVALUE("2018-4-13")&
":A"&DATEVALUE("2018-4-30"))), "dd/mm/yyyy"))
6
I'm guessing you basically want to sum up the expenses for each month, and then find an average amount per month.
Let's first sum up expenses for each month. Each row in your spreadsheet has a date, but that does not immediately link it to other rows within the same month.
So let's introduce a new column Month, derived from the date. There is a formula =...
6
I made a simple version of your problem you can play around with:
Make a copy of the sheet
There are basically two things to do:
1. Get the duration for each visit
For that I added a Column with an arrayforumla (so we don’t have to copy it in every row) that basically looks if we are in a “Leave” row and if so, gives us the time difference between the ...
5
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
21.3k16 gold badges89 silver badges183 bronze badges
5
Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this:
=TEXT(A2, "00")
I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot.
Formula
=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") &...
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)
5
The result was one day ahead for me, so I changed it to =to_date((A1+2209161600)/86400)
5
It is as simple as using the following sequence formula
=SEQUENCE(A1)
Or you even could have something like this:
=SEQUENCE(A2,1,A4,A6)
You could even use the SEQUENCE function for dates
=SEQUENCE(A2,1,A4,A6)
SEQUENCE
Only top voted, non community-wiki answers of a minimum length are eligible
