Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Microsoft Excel - The Date Bug

02 Jun 2014   #1
Windows Sniper

Windows 8.1
 
 
Microsoft Excel - The Date Bug

Hey all,

Let me just start of by saying, this may be old news but it is still highly relevant in today's market. But there is a large bug in Office's suite all of them in fact (2000 - 2014), concerning the way it handles time / dates.

History
A brief bit of history for you all, MS decided that they wanted to base all their software solutions with the assumption that 1900 (which is the baseline date for MS) was a leap year. To keep their software compatible, they left it that way, so that other systems such as Lotus and later versions would continue to work with other calculative programs.

Excel 2000 incorrectly assumes that the year 1900 is a leap year

The Problem

Now this is not necessarily an issue, I mean sure, it thinks that 1900 is a leap year, MS started the calendar on 01/01/1900, where is the problem?

Well, MS set the date to 01/01/1900, knowing and setting it so that it isn't a leap year.

The outcome?

Excel actually works one day in the past, because there is supposed to be an extra day in a leap year, right?


Here let me show you,




So this image shows a base formula, which is used to determine the number of days in a month. The month specified happens to be february... Now i am almost 100% sure that there is 28.25 days in February..

Why would this be used? Well for daily figures, average daily income over a month, finding out how many days in a month there actually are....



A lot of things could potentially use this, here try it yourself,

=DAY(DATE(YEAR(C4),MONTH(C4),))

Set C4 to any date in Feb, then March..



It calculates this by going to the start of the month, minusing the amount of days it believes there are, but wait, there is that extra day, this means that the Excel start date is actually 00/01/1900...

Here is another way to check this is actually working.

Say we use a static date, 01/02/2014. I want to find the month's start date, but in it's serial number form ( Excel reads the number as a serial and converts it into the friendly date we understand). Which we can then convert straight back into a friendly date.




Again, what?

Apparently each time we do this formula, we will go to the last day of the previous month!

(Below just shows the pretty formatting)





So, just to finally show exactly what happens with the whole start of the calendar business.




As you can see, there were apparently no days in January in the year 1900, it also shows that 0, as the baseline, is the start of the calendar, not 1. Which means that the first date that can actually be read by excel is 00/01/1900. This then starts a chain of issues, which need to be fixed but apparently won't be because it would make it incompatible!



How did i come by this bug?

So i work for a company, a rather large one at that. It was my responsibility to create a reporting tool that would show the average amount of processed exchanges per day, grouped by month.

This is all good and well, except that I used the above formula to determine the number of days in the month automatically, and then continue with the formula.

When i created this in January, I did a quick formula check and everything checked out (31 days in December remember). So everything was good and well until the end of March came around, and the numbers seemed very off.

After a quick check, I realised this huge error, however the damage was done, with the miscalculation of Feb/Mar I had created over 9 Million errors in exchanges. When a client is charged by number of processed exchanges per day, this is a substantial amount.

After a couple of hours pulling my hair out, I finally found this issue, and the ensuing pandemonium was awful.


The end

So there it is, MS has managed to create a system which doesn't believe itself, and cannot interact with it's own date system.

I apologise for taking your time to read this post, and it could probably be rewritten into something more legible, however i told MS about this about a month ago, and nothing has be declared. So i am telling you all now.

Be careful when crafting dates in MS Excel, it believes that you are working one day in the future.

Oh and as a side note, if you're ever billed by someone using excel to generate the dates, you may want to check when you were actually charged...


Thanks for taking the time to read guys, I encourage you to check any and all calculations within Excel manually. It may save money and your job!


My System SpecsSystem Spec
.
02 Jun 2014   #2
andrew129260

Windows 10 Pro
 
 

Wow! That is a huge bug!!

Did you post it on the microsoft forums?

I bet you will get an answer like reinstall office. lol.
My System SpecsSystem Spec
03 Jun 2014   #3
Windows Sniper

Windows 8.1
 
 

Quote   Quote: Originally Posted by andrew129260 View Post
Wow! That is a huge bug!!

Did you post it on the microsoft forums?

I bet you will get an answer like reinstall office. lol.

No actually, I directly called the MS service desk and after 40 minutes of explaining the method above the young chap finally saw the numbers; Took my name and number and that was it.


As for reinstalling office, I highly doubt it considering it happens on every version of MS Office, and the chap (also his manager) tried to prove me wrong for some reason.
My System SpecsSystem Spec
.

10 Jun 2014   #4
mangoh

Windows 7 Professional 64bit
 
 

Quote   Quote: Originally Posted by Windows Sniper View Post
Quote   Quote: Originally Posted by andrew129260 View Post
Wow! That is a huge bug!!

Did you post it on the microsoft forums?

I bet you will get an answer like reinstall office. lol.

No actually, I directly called the MS service desk and after 40 minutes of explaining the method above the young chap finally saw the numbers; Took my name and number and that was it.


As for reinstalling office, I highly doubt it considering it happens on every version of MS Office, and the chap (also his manager) tried to prove me wrong for some reason.
WOW!

Nice thread.


Erm did you lose your job?

Have you posted this on Reddit?
My System SpecsSystem Spec
12 Jun 2014   #5
Windows Sniper

Windows 8.1
 
 

Quote   Quote: Originally Posted by mangoh View Post
Quote   Quote: Originally Posted by Windows Sniper View Post
Quote   Quote: Originally Posted by andrew129260 View Post
Wow! That is a huge bug!!

Did you post it on the microsoft forums?

I bet you will get an answer like reinstall office. lol.

No actually, I directly called the MS service desk and after 40 minutes of explaining the method above the young chap finally saw the numbers; Took my name and number and that was it.


As for reinstalling office, I highly doubt it considering it happens on every version of MS Office, and the chap (also his manager) tried to prove me wrong for some reason.
WOW!

Nice thread.


Erm did you lose your job?

Have you posted this on Reddit?

Cheers,

Luckily no I didn't, because I explained to my boss exactly what had occurred and took full blame when it technically wasn't my fault. I did have to write a company wide memo however to inform all users of this information.


Again no, I have no idea which /r/ this would go into...
My System SpecsSystem Spec
Reply

 Microsoft Excel - The Date Bug




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
Excel 2007 Date Factor
I have a spreadsheet of inventory items and the dates are in various formats Europe , USA , and Eastern. Example below. What I need to do is select all the dates and covert into one recognizable formula Month (1) Day (1) Year (****). Let me know how to do this. Thanks! 7/12/00 28/12/00...
Microsoft Office
Separating date and time in Excel Office 2007
I have Office 2007 running on a Windows 7 64 bit Toshiba laptop. I am trying to separate the data and time in an Excel file in one column and put the date in one column and the time in another. I need a "how to" procedure to do that.
Microsoft Office
Excel 2010 question: Date 00-00-0000 (08-03-2012)(dd-mm-yyyy)
Yo folks, Annoying little thing in excel, I want to show the date exactly like this: 00-00-0000 (08-03-2012)(dd-mm-yyyy) Instead excel removes the zero's like this: 0-0-0000 (8-3-2012) (d-m-yyyy) Is it possible to keep the zero's? Thanks in advanced,
Microsoft Office
What is Excel Date Playing At?
I wanted to put a date into a field, but did not want it formatted. In fact it could just represent any 6 digit number; not a date at all. So I put 050811 (today's date the British way DD MM YY) And Excel in its infinite wisdom changed it to: 10/02/2039 I despair! :confused: :eek:
Microsoft Office
Excel 2007: a question about date and time manipulation
I have pasted data in to Excel that is in two columns Col A contains a list of dates, dd/mm/yy; col B times hh/mm/yy. How do I set about calculating and displaying the period in dd/hh/mm/ss that elapsed between col A row B with col B row B, and col A row C with col B row C ?
Software
Date Problem - Excel and Quickbooks
Hi For some reason, both my excel and my quickbooks will only output dates as dd-mm-yy I know that in excel, i can go to format cell>custom> mm/dd/yy. But, if i enter into the next cell, 1/30/2010 the computer does NOT recognize it as a date.. it sees it as 1st day of the 30th month in year...
Software


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 09:09.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App