Custom date field and Fill

[Matt]

I have a customer that is using a date/time fill that
isn't calculating the right time. It can be duplicated on
a new spreadsheet. Start with "A1" and enter
in "1/29/2004 7:30:50 AM" and format the column with this
custom type: m/d/yyyy h:mm:ss AM/PM. the fill series
should decrease by 20 seconds every cell. So I manually
put in the first 5 cells as follows:
1/29/2004 7:30:50 AM
1/29/2004 7:30:30 AM
1/29/2004 7:30:10 AM
1/29/2004 7:29:50 AM
1/29/2004 7:29:30 AM
Highlight the series and drag it out past A110. In cell
A103 I have lost a second. the value should be 1/29/2004
6:56:50 AM, but instead is as follows.

A101 1/29/2004 6:57:30 AM
A102 1/29/2004 6:57:10 AM
A103 1/29/2004 6:56:51 AM
A104 1/29/2004 6:56:31 AM

If you run the series out even further, you lose a second
every 100 cells or so. Can anyone explain why this is
happening.

 

[Jason Morin]

Without testing, my guess is that is due to rounding.
Dates and times are just numbers in Excel, so although it
is calculating properly (every 20 sec = 0.000231481), the
display is rounding your answer to the closest second.
Just insert the first time in A1 and use this in A2:

=A1-"00:00:20"

Then fill A2 down.

HTH
Jason
Atlanta, GA

 

[Jonathan Rynd]

the fill series
should decrease by 20 seconds every cell.

How about this:
Put 0 in B1, -20 in B2, -40 in B3, etc.
In cell A1 put the starting datetime.
In cell A2 put
=$A$1+B2/(60*60*24)
and fill down.

That way you don't lose accuracy. (No error after 2100 rows)

 

[Jonathan Rynd]

How about this:
Put 0 in B1, -20 in B2, -40 in B3, etc.
In cell A1 put the starting datetime.
In cell A2 put
=$A$1+B2/(60*60*24)
and fill down.

Or, for a slight improvement, put 0, -1, -2, ... in column B and then put

=$A$1+B2/(3*60*24)
in A2, then fill down from there.