39
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 ...
- 576
31
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(...
- 326
18
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 ...
- 52.9k
14
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 ...
- 141
12
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"))
- 27.1k
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 ...
- 52.9k
10
votes
Accepted
How to increment dates by exactly one month from a given date in Google Sheets
The function to increment a date in months is EDATE:
=EDATE(start_date, months)
To increment a value in E7 by 1 month the formula is:
=EDATE(E7,1)
If you have a value in E7 that you want to ...
- 13.1k
8
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 ...
- 196
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), ...
- 27.1k
7
votes
How to increment dates by exactly one month from a given date in Google Sheets
In B1 type your start date.
Type in C1 the formula:
=EDATE(B1,1)
This would also do the trick and then drag it over the range you want and it will auto increment the month.
- 71
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 ...
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, ...
- 17.4k
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 ...
- 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)
- 171
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 ...
- 61
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.
5
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 ...
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 ...
- 17.8k
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)
- 51
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
=...
- 13.1k
4
votes
Change Google Sheets default date format with other locale
To Default your locale:
Go to Google Drive
Select (Click on) your Profile Pic
Select "Manage your Google Account"
Select Data & Personalisation
Near the bottom is Language:
Select ...
- 171
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.
- 59
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 ...
- 41
4
votes
Accepted
Add missing dates to list of dates with values
paste this into C1 cell:
=ARRAYFORMULA({TO_DATE(ROW(
INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), IFERROR(VLOOKUP(TO_DATE(ROW(
INDIRECT("A"&...
- 27.1k
4
votes
Accepted
Google Sheets' MONTH function on an empty cell returns 12
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 ...
- 27.1k
4
votes
Accepted
How to reduce number of X axis labels?
Answer:
... -> Edit chart -> Customize -> Gridlines -> Horizontal Axis (in
drop down) -> Major gridline count
Under the Major gridline count you can select or type however many dates you want to ...
- 867
Only top scored, non community-wiki answers of a minimum length are eligible