Auto Fill Series of Dates & Times

  • Thread starter Thread starter christine
  • Start date Start date
C

christine

I am a slightly experienced Excel user. I have been trying to autofill
a series of days/dates/times in a spreadsheet with no luck. I want to
autofill a column with a series of two cells per day with times of
06:30 am and 14:30 pm. How do I do this? For example, I want it to
start at Friday, 03/05/04, 06:30 and Friday, 03/05/04, 14:30 and then
proceed to Saturday, 03/06/05, 06:30, and so on. I have tried several
different things but can't get it exactly how I need it.
Many thanks to you expert Excel users,
Christine
 
Hi Christine!

A1
5-Mar-2004 06:30
Format dddd, hh:mm AM/PM
A2
5-Mar-2004 14:30
Format dddd, hh:mm AM/PM
A3
=A1+1
Copy down

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi
try the following
- enter the starting date + time in cell A1
- in A2 enter the following formula
=A1+IF(A1-INT(A1)<TIME(14,30,0),TIME(8,0,0),TIME(16,0,0))
format this cell as date/time and copy this down
 
XL's autofill isn't quite smart enough to figure this out - it looks at
the difference between two values and applies that difference in
subsequent entries. If you wanted 8-hour spacings (e.g., including
22:30), it could do that, but not skipping one.

You can use a formula tho'. One among many:

A1: 3/5/2004 06:30
A2: =A1 + IF(HOUR(A1)=6,1,2)/3

drag A2 down as far as necessary.
 
Back
Top