Wilmott Forums - Excel Bugs?

Forum Navigation:

login

FORUMS > Programming and Software Forum

Topic Title: Excel Bugs?
Created On Thu Mar 18, 04 01:53 AM

Topic View:

View thread in raw text format

amkey04
Member

Posts: 61
Joined: Jan 2004

Thu Mar 18, 04 01:53 AM

User is offline

i just discovered another bug in Excel and wish to share it with people in Wilmott forum. Try typing this on excel spreadsheet

date = 20/3/34

date -today()

What I tried to generate here is the number of days between 20/3/04 and today's date. I got some -ve value. Try converting the format to 'number' and you will understand why. It took me half a day to discover this annoying bug!!!

Amkey

Edited: Thu Mar 18, 04 at 04:54 AM by amkey04

Ouyang
Member

Posts: 99
Joined: Jan 2002

Thu Mar 18, 04 04:01 AM

User is offline

Edited: Mon Sep 19, 05 at 02:24 PM by Ouyang

amkey04
Member

Posts: 61
Joined: Jan 2004

Thu Mar 18, 04 04:56 AM

User is offline

ah.. i know why. I am not using American date. But that is not the main problem. The problem is when I input as 20/3/34, the computer don't read it as year 2034. That is why i get a negative answer.

gc
Senior Member

Posts: 592
Joined: Sep 2002

Thu Mar 18, 04 08:45 AM

User is offline

Actually, it might not be a bug at all, but (for once) a good feature of "Windowing" for dates.

Until 1999, when a date was entered as two digits, the century was always defaulted to 19.
So typing 20/3/34 would have meant 1934.

After Y2K, what century should be defaulted? The answer, is : "it depends". Sometimes it still makes sense to default 1900 (e.g. for birthdays). Other times, it would make sense to default "2000" (e.g for a bond maturity date).

Windows allows you to change the windowing for dates going into:
Control panel->Regional and language options->Customise->Date Tab

You will see that the default is that "When a two-digit year is entered, interpret it as a year between 1900 and 2029"
Of course you can change the value to something that might be more suited for your purposes.

gc

-------------------------
-You see, Kyle, we live in a liberal, democratic society. And Democrats make sexual harassment laws. These laws tell us what we can and can't say in the workplace. And what we can and can't do in the workplace.
- Isn't that fascism?

DominicConnor
Senior Member

Posts: 10749
Joined: Jul 2002

Thu Mar 18, 04 09:27 AM

User is offline

After Y2K, what century should be defaulted? The answer, is : "it depends".

I'm afraid I don't quite agree. Date defaults are bugs. If the input is ambiguous then the input is wrong, and should be rejected before it does any more harm.
As gc rightly says, you can do things with Windows configuration. If you work on your own that is fine, but what if you share sheets with others ? Every so it will do obscure and subltly different, but wrong things. You'll report it to the source, but he won't knwo what's going on because he has different defaults. I've had hassles that way, stuff like the way that some countries think that 2/1/2004 is the 2nd of January, others 1st of February.

These are the worst sort of bugs. Something that gives an obviously mad number is quickly traced and fixed, but when pricing a 5 year bond, its easy to miss the effect of month lost in maturity.

A problem with the way that Excel leads to to program is that it is easy to forget that people type in all sorts of crap, we all do, even when we wrote the thing that uses it.

-------------------------
Discussion on the new regulations on bonuses here.

jens
Senior Member

Posts: 231
Joined: Jul 2002

Thu Mar 18, 04 09:48 AM

User is offline

Actually, it's no problem of configuration. A value typed into the spreadsheet is internally stored as the number of days since 1900 (you can configure this as well but...). When you open the sheet on another computer with a different locale setting it will still display correctly, but maybe in a different format. Problems start when you use strings containing dates, eg. in VBA. That should be avoided.

Maybe you should change your personal settings to always show the full year format!?

-------------------------

AVt
Senior Member

Posts: 828
Joined: Dec 2001

Thu Mar 18, 04 09:57 AM

User is offline

I prefer to format the displaying cell to show something like 01 Feb 2004, i.e. showing the month as text.

fire
Senior Member

Posts: 253
Joined: Jul 2003

Thu Mar 25, 04 07:52 AM

User is offline

all these options are customizable ... play around with excel and u'd discover them...

jens
Senior Member

Posts: 231
Joined: Jul 2002

Thu Mar 25, 04 09:33 AM

User is offline

Just wanted to share these:

Description of Excel 2003 Update: March 23, 2004
http://support.microsoft.com/?kbid=834691
NOTE: This update appears to include all 6 previously problematic
statistical functions.

Excel 2003 hotfix package: February 29, 2004
http://support.microsoft.com/?kbid=833855

Issues that are fixed in Excel 2002 by Office XP Service Pack 3
http://support.microsoft.com/?kbid=836031

-------------------------

View thread in raw text format

FORUMS > Programming and Software Forum

Forum Navigation:

© All material, including contents and design, copyright Wilmott Electronic Media Limited - FuseTalk 4.01 © 1999-2010 FuseTalk Inc.