CODEWITHSUNDEEP
google-sheetstimeadditionhour
Anthony Petrillo
Anthony Petrillo

Reputation: 505

A formula to add one hour to the cell above in Google Sheets

Often I want to quick fill of a list of time similar to how I put a 1 in A1 and then in A2 I place A1 + 1 to get two and then I copy that down the next 100 cells to get from 1 to 100. I want to do the same thing with time. It also gives me the advantage of change the first cell and updating all the times. I asked this partly because the other answers are more complex and never get to a simple solution for this kind of process that is so often used.

Upvotes: 2

Views: 4992

Answers (2)

player0
player0

Reputation: 1

to populate rows with numbers from 1 to 100 use:

=SEQUENCE(100)

enter image description here

to get time intervals use:

=INDEX(TEXT(SEQUENCE(12, 1, 0, 2)*"1:00:00", "hh:mm:ss"))

=INDEX(TEXT(SEQUENCE(
 12          +N("number of rows"),
 1           +N("number of columns"),
 0           +N("start from midnight"),
 2           +N("interval of increase"))
 *"1:00:00"  +N("period of increase"), 
 "hh:mm:s"   &N("time format")))

enter image description here

enter image description here

enter image description here

Upvotes: 1

Anthony Petrillo
Anthony Petrillo

Reputation: 505

Working through https://spreadsheetpoint.com/add-time-in-google-sheets/ I came up with:

  • Make sure the cells you are working with are in the time format you desire.
  • Place the time you want to start with in the first cell, let's say A1
  • To add an hour to the time in A1, use =A1+60/(24*60), let's say in A2

Now you can copy A2 down as far in column A you desire to get the time.

Notice, the 60 in the formula =A1+60/(24*60) is the number of minutes. Hence, if you want to do a half-hour, you can use 30.

Upvotes: 1

Related Questions