# unexpected date/time change

#### sillslv

##### New Member
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### baitmaster

##### Well-known Member
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

#### sillslv

##### New Member
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:

Replies
5
Views
507
Replies
7
Views
567
Replies
2
Views
1K
Replies
4
Views
423
Replies
23
Views
1K

1,195,582
Messages
6,010,578
Members
441,557
Latest member
Jbest23

### 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.

### Which adblocker are you using?

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

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