Java Date and Excel Date

Thread view:

aaronfude@gmail.com - 13 Sep 2006 22:30 GMT

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

Reply to this Message

Arne Vajhøj - 13 Sep 2006 22:48 GMT

> 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:
>
[quoted text clipped - 8 lines]
>
> 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

Reply to this Message

Babu Kalakrishnan - 14 Sep 2006 05:09 GMT

> 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

Reply to this Message

Java Date and Excel Date

Free Magazines