# Autofilled time doesnt rollover

#### jolyrojr

##### New Member
Hello,

I am trying to make collumns of times, around 21k lines long. Some times I need them to go by 1 or 3 or 10 seconds, so I have been trying to use the autofill. I create 2 consecutive cells, 06:06:00 and 06:06:03 for example, select them and drag down. I notice though that most of the tme the seconds do not roll over, no matter which cell formatting I use (so I get seconds over 59).

To make things extra complicated for me, I am required to have a a space and to keep the leading 0 if there is one. I figured I could make a custom cell format by entering _hh:mm:ss which works like once but then doesnt roll over the seconds either, even though it keeps the space and the leading 0.

Any suggestions would be GREAT as I have to have this particular report in tomorrow

Thanks soo much

Last edited:

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Ed in Aus

##### Well-known Member
Ok got something that is close to working...

format cell as custom [\$-409]hh:mm:ss

now in a1 put 06:06:00
in a2 =A1+0.00003 you will need to adjust this figure just took a stab in the dark but it is close at least.

copy formula down

#### Weaver

##### Well-known Member
Cell A2: =A1+1/86400 (1 second), = A1+1/28800 (3 seconds), = A1+1/8640 (10 seconds)

If all the formulae slow your sheet down, you can always copy/paste values once you've created the datasets you require.

#### jolyrojr

##### New Member
Thank you for your speedy replies!

Ok, so I did the custome how you have it, and then I enter a2 i get #VALUE! with a flag for "A value used in the formula is of the wrong data type.

So am I doing something wrong?

#### Ed in Aus

##### Well-known Member
Some thing with the formatting of the cell is wrong.

Replies
3
Views
300
Replies
4
Views
351
Replies
3
Views
533
Replies
0
Views
99
Replies
0
Views
165

1,191,686
Messages
5,988,068
Members
440,125
Latest member
vincentchu2369

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

### Which adblocker are you using?

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

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