f



Java Date and Excel Date

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

0
aaronfude (214)
9/13/2006 9:30:09 PM
comp.lang.java.programmer 52714 articles. 1 followers. Post Follow

2 Replies
524 Views

Similar Articles

[PageSpeed] 28

aaronfude@gmail.com wrote:
> 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
0
arne6 (9808)
9/13/2006 9:48:02 PM
aaronfude@gmail.com wrote:
> 
> 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
0
bkk.ngroup (44)
9/14/2006 4:09:15 AM
Reply: