Product Planner at YouTube asks: How can I automatically increase times in a column by 10 minutes each?
Transcript of the video:
Learn Excel from MrExcel podcast episode 2351.
How do you increment a column by 10 minutes?
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today's question from ProductPlacer at YouTube.
How do you automatically increment a column by hours and minutes like this?
The example is 11:12, 11:22, 11:32, 11:42, and so on. I have several different methods to use to do this.
First though. Just some simple math. How many hours in a day? 24 hours.
How many 10 minute periods in an hour? There are six of those. 24 times 6 is 144.
So that means that 1 divided by 144 is the equivalent of a 10 minute period.
So what I am going to do here.
I am going to do Ctrl+Shift+Colon to put in the current time.
Then up here i'm going to do equal 1 divided by 144. But I am not going to press Enter.
Instead, I am going to press the F9 key.
That calculates the number and puts it in the selection. So Ctrl+C to copy. And then Escape.
Then we come here to our first time. On the Ribbon, choose Home.
Out here on the far right-hand side, choose Fill Series.
It always defaults to Rows. Change it to columns.
For the Step value. Get rid of the 1.
Press Ctrl+V to paste. That puts the 1/144 fraction.
That is 1 divided by 144. How far do we want to go?
Let's just go to the end of the day. The end of the day is the number one.
Click okay and we get 10 minutes all the way down.
That's one way to go. Another way: Ctrl+Shift+Colon.
Then type the next value. So 11:23 like that.
Select them both and then grab the Fill Handle. Drag however far you want to go.
It will fill 10 minute increments all the way down.
Now if you have dynamic arrays in Microsoft 365.
Then one cool way to go would be =SEQUENCE(.
How many rows? Let's do the next eight hours.
So eight times six is 48. 48 rows. One column.
The start time is 11:13. The Step is 1 divided by 144.
Alright. That is the right answer but the wrong format. Select the whole range.
Then come up here and choose Time. That is 11:13, 11:23, 11:33.
Another really easy way to go. Put your start time in there with Ctrl+Shift+Colon.
Then equal the cell above me plus TIME( 0 hours, 10 minutes, 0 seconds). Drag the Fill Handle and copy that down.
Several different ways to increment by 10 minutes.
If you had some other number like 15 minutes it would be 24 hours times 4.
That is 1 divided by 96. You get the idea. It is a good way to go.
Down below the video, please click Subscribe.
I am trying to get 100,000 subscribers so I can replace this merchandise shelf with better merchandise. My new book MrExcel 2020.
Seeing Excel Clearly. There is an "i" in the top right hand corner.
Click that "i" to get to more information about the book I want to thank ProductPlanner for sending that question in.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫ Theme music [ ♫ ]
How do you increment a column by 10 minutes?
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today's question from ProductPlacer at YouTube.
How do you automatically increment a column by hours and minutes like this?
The example is 11:12, 11:22, 11:32, 11:42, and so on. I have several different methods to use to do this.
First though. Just some simple math. How many hours in a day? 24 hours.
How many 10 minute periods in an hour? There are six of those. 24 times 6 is 144.
So that means that 1 divided by 144 is the equivalent of a 10 minute period.
So what I am going to do here.
I am going to do Ctrl+Shift+Colon to put in the current time.
Then up here i'm going to do equal 1 divided by 144. But I am not going to press Enter.
Instead, I am going to press the F9 key.
That calculates the number and puts it in the selection. So Ctrl+C to copy. And then Escape.
Then we come here to our first time. On the Ribbon, choose Home.
Out here on the far right-hand side, choose Fill Series.
It always defaults to Rows. Change it to columns.
For the Step value. Get rid of the 1.
Press Ctrl+V to paste. That puts the 1/144 fraction.
That is 1 divided by 144. How far do we want to go?
Let's just go to the end of the day. The end of the day is the number one.
Click okay and we get 10 minutes all the way down.
That's one way to go. Another way: Ctrl+Shift+Colon.
Then type the next value. So 11:23 like that.
Select them both and then grab the Fill Handle. Drag however far you want to go.
It will fill 10 minute increments all the way down.
Now if you have dynamic arrays in Microsoft 365.
Then one cool way to go would be =SEQUENCE(.
How many rows? Let's do the next eight hours.
So eight times six is 48. 48 rows. One column.
The start time is 11:13. The Step is 1 divided by 144.
Alright. That is the right answer but the wrong format. Select the whole range.
Then come up here and choose Time. That is 11:13, 11:23, 11:33.
Another really easy way to go. Put your start time in there with Ctrl+Shift+Colon.
Then equal the cell above me plus TIME( 0 hours, 10 minutes, 0 seconds). Drag the Fill Handle and copy that down.
Several different ways to increment by 10 minutes.
If you had some other number like 15 minutes it would be 24 hours times 4.
That is 1 divided by 96. You get the idea. It is a good way to go.
Down below the video, please click Subscribe.
I am trying to get 100,000 subscribers so I can replace this merchandise shelf with better merchandise. My new book MrExcel 2020.
Seeing Excel Clearly. There is an "i" in the top right hand corner.
Click that "i" to get to more information about the book I want to thank ProductPlanner for sending that question in.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫ Theme music [ ♫ ]
