Time Question

MsCynic

Board Regular
Joined
May 21, 2006
Messages
122
Hi again, I have been using this formula to add a certain amount of time to a cell: =C2 + TIME(0,7,0) - in this case, 7 minutes. However, I want to add the same amount of time to all cells in a particular column. Thus far I have been C+P-ing this formula into a cell, then overtyping 2 with 3, 4 etc until I get all the cells filled in.

Is there a quicker way to populate all cells in a column with this formula so that it steps up and I don't have to manually type in each one?

I'm new to formulas and am having a great time with this project, but I really don't want to spend hours on it ...
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Excelenator

Board Regular
Joined
Jul 30, 2006
Messages
154
Are you doing this with VBA or just within Excel? Within Excel, as long as the cell reference is not Absolute (ie $C$2) the cell reference will change as you copy and paste the cell with the formula in it to other cells.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
another trick...

say you have a column of data from a1 to a100, and a formula in b1. select b1 - you'll see that the cell is outlined, and the bottom right corner is a little square - it's known as the 'fill handle'. Double click on the fill handle & the formula will populate down to b100. (this only works if there are no gaps in your data).

review the excel help file for 'About cell and range references'
 

MsCynic

Board Regular
Joined
May 21, 2006
Messages
122
Hi, thanks for replying.

I haven't even started looking at VBA yet so this is most definitely within Excel.

Where would I instert the $ in this formula so it auto-increases the cell number when I paste it?
=C8 + TIME(0,7,0)
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi MsCynic:

Here is one way ...

Let us say you started off with value in cell C2 as 1:00, and 1:10 in C3, 1:20 in C4, 1:30 in C5 and so on. Then to add a constant value to the cells C2, C3, C4, C5, etc.

Let us key-in

=Time(0,7,0) in cell D1

Then select cell D1, and do EDIT|Copy,

Then select cells C2:C6 and do EDIT|PasteSpecial|Operation -- Add

That should do it.
 

MsCynic

Board Regular
Joined
May 21, 2006
Messages
122
Paddy, that's bloody brilliant - thanks!
Cheers too Yogi - I'm going to try your suggestion next.
You guys are great!
 

Forum statistics

Threads
1,141,730
Messages
5,708,147
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top