Auto fill 4 years of time in 30 minute increments [SOLVED]

[eibi]

Friends,

I need to tabulate a large volume of data -- which is essentially a set of utility meter readings for every half hour increment over a 4 year period.

As I set up the sheets, I want to auto fill the left column with a date/time stamp for each half hour increment, like so:

1/1/10 0:00
1/1/10 0:30
1/1/10 1:00
1/1/10 1:30
1/1/10 2:00
1/1/10 2:30
1/1/10 3:00

However, I need to auto-fill this onto ~92,000 rows...

Excel assigns a numerical fraction to each half hour, so A3-A2= 0.0208332754628....

As I drag to auto-fill, this fraction begins to create a problem. When I get to the fourth day, I lose 1 whole second.

1/4/10 8:30
1/4/10 9:00
1/4/10 9:30
1/4/10 9:59
1/4/10 10:29
1/4/10 10:59

Over the 4 year period, the cumulative effect is a loss of 9 minutes, which is very problematic.

Any suggestions for autofilling the half hour increments more accurately? It won't be possible for me to manually populate 92,000 rows.

Also -- I can't use a 'text' format in the cell -- it has to be a date/time format.

Thanks!

 

[eibi]

Well, I found a solution that is imperfect but adequate:

=$A$2+(ROW()-2)/48

It's volatile, so I'll have to Copy/Paste Special Values Only when I'm done; and it still has fractional discrepancies, but I can drag it 106,000 rows without cumulative loss of time. (I just have to remember that my lookup and match functions won't match precisely because of the fractional gap...)

Nod to http://answers.microsoft.com for the suggestion.

 

[Marc L]

Hi !

No issue under 2003 version with cell C1 =A2-A1

and from cell A3 formula =A2+$C$1 …
​

 

[NARAYANK991]

Hi ,

Can you not use the Fill feature ?

Enter 1/1/2010 0:0 in A1.

Click on Fill , Series , Columns , Linear and use 0:30 for the Step Value. Enter 12/31/2014 23:30 for the Stop Value.

Narayan

 

[Chihiro]

Since it's solved, just as FYI. Non formula solution.

Use fill feature.

Edit: Narayan beat me to it.