Date formatting, windowing and Y2K
Location:  http://www.mvps.org/dmcritchie/excel/y2k.htm

Subtraction of dates shows  30-12-01  or  12/30/01

>I already tried substracting the two days but it formats
>like this 30-12-01 0:00.    (in US/Canada  Hans would have seen  12/30/01 00:00)

Hi Hans,    (posted and emailed)
You have the correct answer, you must format your difference between the two dates as a NUMBER.   A format of GENERAL will assume that since you worked with dates that you want a format of a date.

Be aware that if you actually  enter 30-12-01 in Europe that you are entering a date of 30-12-2001  not 30-12-1901.    In the default 1900 date system in Excel the first is 730 days and the second is 37255 days.

A display of 30-12-01 0:00   European date or 12/30/00 US/Can represents 730 days, if the date is 1901.   Since the format does not show the complete date it could instead represent  year 2001.

All you have to do is format the cell with the difference as a number instead of general.

In Europe and most of the world with windowing for the date if you entered 30-12-01 the date would actually be 30-12-2001 and the number is 37255 days (from 1900).

In US/Can with windowing for the date if you entered 12/30/01  the date would actually be 12/30/2001 and the number is 37255 days (from 1900).

What does this really mean.  You should be displaying four digit years.   For XL95, I had to create a custom format with the year.  Perhaps later versions have done this for you, but I doubt it.
    dd-mm-yyyy    European
    mm/dd/yyyy    US/Can

You can still enter 30-12  or 30-12-98 or 30-12-1998  as being equivalent in this year 1998.  (Europe)

You can still enter  12/30  or 12/30/98 or 12/30/1998 as being equivalent in this year 1998.  (US/Canada)

There are MS KB articles on windowing.  The window for date entries moves up with different releases.  So say if the window is 1930 inclusive through  2029 then a year of 01 represents 2001 and a year of 31 represents 1931.

You probably already know all about this as Y2K problems.
 

Related Information

On site

Date & Time
Date and Time entries are stored as Date Serials.  Use of =NOW() obtains the system date and time and is updated during recalculation.  Additional date calculation examples.

Other sites

Microsoft Knowledge database (MS KB)

You must be register with MS Support in order to use the MS KB.
You are one of many distinguished visitors who have visited my site here or in a previous location  since this page was created on October 6, 1998. 

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to:  David McRitchie  mailto:DMcRitchie%20%6Dsn.com.