40 votes
Accepted

How to convert Unix Timestamp to human readable?

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 ...
Dave L.'s user avatar
  • 586
32 votes

How to Fill a Column with Sequential Dates in Google Sheets

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 ...
drowned in coffee's user avatar
30 votes
Accepted

Conditionally format by day of the week

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(...
aej's user avatar
  • 316
17 votes
Accepted

Change date format [YYYYMMDD] to [MM/DD/YYYY]

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 ...
ale's user avatar
  • 52.3k
16 votes

Compare date value to current date in Google Sheets

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 ...
Rubén's user avatar
  • 44.6k
13 votes

How to add days to a date

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 ...
Mike's user avatar
  • 131
12 votes

How do I calculate a time delta in my Google Spreadsheets?

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 ...
Tom Elwood's user avatar
12 votes
Accepted

Year function in Google Sheets gives unexpected results

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 ...
ale's user avatar
  • 52.3k
11 votes

How to Fill a Column with Sequential Dates in Google Sheets

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"))
user0's user avatar
  • 26.2k
7 votes
Accepted

Convert Text String into Date/Time Value

={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), ...
user0's user avatar
  • 26.2k
6 votes

How do I calculate a time delta in my Google Spreadsheets?

Disclaimer: I am posting this answer that doesn't exactly answer the asker's question because this is the answer I needed when this question was the first result on Google, and I want to help the next ...
Lex's user avatar
  • 79
6 votes

Conditionally format by day of the week

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 ...
Emerson's user avatar
  • 61
6 votes

How do I calculate a time delta in my Google Spreadsheets?

If you want your time delta to be measured in days, use =DAYS(end_date, start_date)
e18r's user avatar
  • 171
6 votes

Using Google Sheets, how can I calculate the average per month based on multiple rows?

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, ...
Vidar S. Ramdal's user avatar
6 votes
Accepted

Time tracking with Google Spreadsheets

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 ...
Marcus Blättermann's user avatar
6 votes
Accepted

How to convert datetime string to datetime value in Google Sheets?

I could find no magic function in google sheet. However, you may use the following. Before applying this function, you need to add year value like change "13 Jan, 02:21 am" // google will ...
John's user avatar
  • 176
5 votes

How can I format time as 12-hour but without the period indicator?

It's not possible without a formula. Separate time storing from time displaying by using one cell to store the time and another to display it in the desired format by using a formula like the ...
Rubén's user avatar
  • 44.6k
5 votes

How do I calculate a time delta in my Google Spreadsheets?

You may try TIMEVALUE() as well. In the above case, the solution would be: (TIMEVALUE(End Time) - TIMEVALUE(Start Time))*24*60 will give you the time difference in MINUTES.
Sujeeth Ravindran's user avatar
5 votes

Change date format [YYYYMMDD] to [MM/DD/YYYY]

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 ...
pnuts's user avatar
  • 17.4k
5 votes

How to convert Unix Timestamp to human readable?

The result was one day ahead for me, so I changed it to =to_date((A1+2209161600)/86400)
mbican's user avatar
  • 51
5 votes
Accepted

How to increment dates by exactly one month from a given date in Google Sheets

The formula you can use to increment dates by exactly one month from a given date is: =ArrayFormula(EDATE(E7,SEQUENCE(14,1,1,1))) Functions used: ArrayFormula EDATE SEQUENCE
marikamitsos's user avatar
  • 12.6k
5 votes
Accepted

Print incremental numbers in Google Sheets

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 =...
marikamitsos's user avatar
  • 12.6k
4 votes

How do I calculate a time delta in my Google Spreadsheets?

In Google Sheets, I used the formula as below =Round((hour(A2-A1)*60 + minute(A2-A1))/60,2) to give me the difference in decimal hours.
Vibhu's user avatar
  • 59
4 votes

How to add days to a date

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 ...
Andre Leon Rangel's user avatar
4 votes

How to Fill a Column with Sequential Dates in Google Sheets

Logic: In first column we can put current date using =Today() this will give current date. In order to get next date we must increase the date by 1. To get next date we can add =Today()+1 If you ...
Xitij Thool's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible