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.

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"David in Fort Myers" wrote:

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.

Argh, forgive me, I gave the wrong formula.

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

Luke M
*Remember to click "yes" if this post helped you!*


"David in Fort Myers" wrote:

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.

Time is a fraction of a day. You are getting a rounding error because 1
minute is 1/(24*60) which is a repeating decimal like 1/3 =
..3333333333333333333.

You could use a formula to get better results and then copy and paste value
to remove the formula

Put in cell A1
january 1, 2007 00:00:00
Put in cell A2 and copy down column
=A$1+(ROW()-1)*TIME(1,0,0)



"David in Fort Myers" wrote:

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.

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

"Luke M" wrote:

Argh, forgive me, I gave the wrong formula.

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

Luke M
*Remember to click "yes" if this post helped you!*


"David in Fort Myers" wrote:

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.

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

"David in Fort Myers" wrote:

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

"Luke M" wrote:

Argh, forgive me, I gave the wrong formula.

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

Luke M
*Remember to click "yes" if this post helped you!*


"David in Fort Myers" wrote:

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.