unexpected date/time change

sillslv

New Member
Joined
Aug 16, 2005
Messages
36
Windows 10, Office 2016
A1 enter 9/1/2016 0:00 (could be beginning of any month, but must be 12:00 midnight)
A2 enter 9/1/2016 1:00 (advancing one hour at a time)
highlight A1 and A2, click and drag the handle down
the date/time advances one hour at a time through 4 days, but on the 5th day 3:00 turns into 3:59, not 4:00
this happens consistently when begun at 0:00

I know there are alternative ways to create those lists of dates/times, I'm just curious why this is happening
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
probably floating point error. Google the term, but as a basic explanation:

Underneath any date you see in Excel, is a whole number, which Excel then formats as a date - remove formatting and you get the number, e.g. today is actually 42711. A decimal part attached to it relates to time, so 42711.5 would be noon today

Your problem comes because binary is not very good at storing decimals - there are a finite number of possible binary combinations but an infinite number of decimals; this is compounded because times are awkward numbers, e.g. 1 minute = 1/1440 = 0.000694444444444444, 1 hour = 1/24 = 0.0416666666666667

Once you start adding together decimals like this, or forcing Excel to add them by dragging as you are, eventually you confuse the poor thing and it adds stuff up wrong. The best approach is to use non-decimals where possible, then divide the results as required, to limit the number of times detailed decimals get added together
 
Upvote 0
Thank you. This was enlightening. I guess I had assumed that Excel would be able to pull down exactly the same amount, but this makes all kinds of sense.
Thanks.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top