autofill date times

[David in Fort Myers]

Hi:

I am working on creating a shift schedule for paramedics and firemen. They
are on a 24-hour schedule and the shifts rotate around. The first step is to
have all 365 days in a year with each day having 24 hours. In Excel 2003, I
type into the first three cells;
january 1, 2007 00:00:00
january 1, 2007 01:00:00
january 1, 2007 02:00:00
January 1, 2007 03:00:00
January 1, 2007 04:00:00

I highlight these cells and use autofill to fill in the rest of the year
with my 24-hour schedule. Excel understands what I am doing and it begins
incrementing by one hour. Unfortunately, I don't get far. At about january 5,
2007 I notice the date/time stamp starts to look like;
january 5, 2007 07:59:00
january 5, 2007 08:59:00
january 5, 2007 09:59:00
Why does it offset by one minute?
Can anyone tell me how to do this right?
Or alternatively, can anyone recommend a quick fix for what Excel is doing.

 

[Luke M]

Explaination:
Time is stored as a decimal in XL.(time is a fraction of a day) Note that if
you formatted your cells to general, you would see this. When XL Autofills,
it's actually just creating the same delta between numbers. Unforutnately, 1
hr is ~ 0.0416666667
Like any computer, there's a limit to how many decimals it can store, while
humans realize the 6 should repeat forever. After enough cells, that odd
decimal throws things off, thus giving you the 59 minutes.

Solution:
After inputting date in first cell, next row use formula:
=A2+TIME(1,0,0)

Now you're specifically telling XL to always add 1 hr, as opposed to some
decimal.

 

[Luke M]

Argh, forgive me, I gave the wrong formula.

=DATE(YEAR(A1),MONTH(A1),DAY(A1))+TIME(HOUR(A1)+1,0,0)

 

[David in Fort Myers]

Hi Luke:

Thank you for replying to my post. This gives me a good start. However,
there is still a problem. Here is my description of the problem:
1.) type in cell A1 the date January 1, 2008 00:00
2.) Type in cell A2 your formula
=DATE(YEAR(A1),MONTH(A1),DAY(A1))+TIME(HOUR(A1)+1,0,0)
3.) use autofill to drag down the contents to fill in the 24 hour schedule.
4.) the problem begins on January 2, 2008 00:00. Here the autofill reverts
back to january 1, 2008 00:00. The autofill never advances to january 2nd.

How can I make Excel autofill 365 days in a year with 24 hours in each day?

David in Fort Myers

 

[Joel]

Don't use autofill. Instead copy A2 down the worksheet.