Data fill with time/date.

[ACtrafficengr]

I want to generate a series of times and dates, incremented hourly. Say I put in the first cell, then drag teh doohicky down. It increments daily. If I put "5/1/2007 15:00" in the second cell, it alternates

5/1/2007 15:00
5/1/2007 14:00
5/1/2007 15:00
5/1/2007 14:00
...

How do I get it to fill like this:

5/1/2007 14:00
5/1/2007 15:00
5/1/2007 16:00?


"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928

"I'm searching for the questions, so my answers will make sense." - Stephen Brust

 

[GTstartup]

When I do it I get:

5/1/07 15:00
5/1/07 16:00
5/1/07 17:00
5/1/07 18:00
5/1/07 19:00
5/1/07 20:00
5/1/07 21:00
5/1/07 22:00
5/1/07 23:00

I think you're doing something wrong but I don't know wha. Plus your example has dates decremented, not incremented

 

[GTstartup]

Ok I think it's how you format it. Put the 5/1/07 15:00
in the first cell and copy it to the second, then change the 15:00 to 16:00 in the second cell and it should work

 

[IRstuff]

Note that there will be a slight error on the order of 3.8 ppm per increment, due to the binary representation error of 1 hr being 1/24th of a day, which is the timestamp representation used in Excel.

IF you need that to be more accurate, then the best thing to do is to use an index that cycles every 24 counts and divide that by 24 and add to the whole day count.

TTFN

FAQ731-376

 

[cummings54]

You are probably getting odd results because the dates are entered as text and not dates. As soon as the date is entered in the cell using the date format you will be able to increment by 1 hour by dragging. You can also use the Edit:Fill:Series menu item. Choose 0.0416666666666667 as your increment

 

[yogia]

Hi ACtrafficengr:

Foloowing is a solution using EDIT|Fill|Series ...

5/1/2007 14:00
5/1/2007 15:00
5/1/2007 16:00
5/1/2007 17:00
5/1/2007 18:00
5/1/2007 19:00
5/1/2007 20:00
5/1/2007 21:00
5/1/2007 22:00
5/1/2007 23:00
5/2/2007 0:00

I did it using the following steps ...

1. I keyd-in 5/1/2007 14:00 in cell A1
2. I selected column A
3. I invoked EDIT|Fill|Series ...
   a) then in [b]Series_in[/b] box I selected the option Columns
   b) in the [b]Type[/b] box, I selected Date
   c) in Step_size I keyd-in 0 1/24
   d) in Stop_value I keyd-in 5/2/2007
   e) in the [b]Date_unit[/b] box I selected Day
4. Then I clicked OK

and got the result as shown above.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com