Java Date and Excel Date

[aaronfude]

Hi,

In Excel, the date represents the number of days since Jan/1/1990, with
the number 1 representing that date.

May/1/2006 is represented by .

So the following ought to convert that number to a java date:

GregorianCalendar gc = new GregorianCalendar(1900, Calendar.JANUARY,
1);
gc.setTimeInMillis(gc.getTimeInMillis()+ (38838L-1)*24*60*60*1000);
System.out.println(gc.getTime());

But I get

Tue May 02 01:00:00 EDT 2006


Am I doing something wrong or does Excel count days wrong?

Many thanks in advance!

Aaron Fude

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

In Excel, the date represents the number of days since Jan/1/1990, with
the number 1 representing that date.

May/1/2006 is represented by .

I think you lost 38838 here.

So the following ought to convert that number to a java date:

GregorianCalendar gc = new GregorianCalendar(1900, Calendar.JANUARY,
1);
gc.setTimeInMillis(gc.getTimeInMillis()+ (38838L-1)*24*60*60*1000);
System.out.println(gc.getTime());

But I get

Tue May 02 01:00:00 EDT 2006

Am I doing something wrong or does Excel count days wrong?

The 00:00:00 versus 01:00:00 is EDT versus EST.

I would suggest replacing:

gc.setTimeInMillis(gc.getTimeInMillis()+ (38838L-1)*24*60*60*1000);

with:

gc.add(Calendar.DATE, 38838-1);

The May 02 versus May 01 is a bug in Excel.

Try look at the date 60 in Excel.

It is 29-Feb-1900.

That date does not exist in real life.

(remember: only if multipla of 4, but if multipla of 100 only if
mulipla of 400)

Arne

 

[Babu Kalakrishnan]

In Excel, the date represents the number of days since Jan/1/1990, with
the number 1 representing that date.

May/1/2006 is represented by .

So the following ought to convert that number to a java date:

If you're looking for s third party class, try JFreeChart (Or rather
its companion utility library JCommon) - it contains a class named
SpreadSheetDate which IIRC models the date numbers used in Excel.

BK