Time Question

[MsCynic]

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 ...

 

[Excelenator]

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]

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]

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]

Hi MsCynic:

Here is one way ...

Book1
	A	B	C	D	E	F
1				12:07 AM
2			1:07 AM
3			1:17 AM
4			1:27 AM
5			1:37 AM
6			1:47 AM
7
Sheet4

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]

Paddy, that's bloody brilliant - thanks!
Cheers too Yogi - I'm going to try your suggestion next.
You guys are great!