Autofilled time doesnt rollover

[jolyrojr]

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

 

[Ed in Aus]

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]

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]

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]

Some thing with the formatting of the cell is wrong.