Times are an hour off in Java Cells

Aspose.Cells Product Family
1

I am using the Java SimpleDateFormat to convert times into text for headings. What I found was the times shown in my heading were an hour different than the times shown in my data cells in the sheet. Attached is a file with a snipet of code that takes the same Date and in one case converts it into a string using SimpeDateFormat and puts the result into a cell and in the other case it puts the Date object into another cell and then setting the Style formatting to display the Date in a similar format. The dates are 2 hours apart. (I am running in America/Chicago timezone and it is currently daylight savings here.) What am I missing? Is there a problem with handling of dates in cells or do I need to manipulate the Date object before inserting into cell?

Thanks for any help.
Steve

2

Hi Steve,

The built-in formats are dependent on the current regional settings of the operating system.We only list these formats in the US-English environment.

The built_in date format 14,22 will be refelected by the system setting.

You can use Style.setCustom() to complete your request. Just as following codes:

dateStyle.setCustom("M/D/yy h:mm");

3

Hi Warren,

The problem isn't that the formats are different. The problem is that the the time displayed using the same Date object are 1 hour different.

In the sample code I have attached, I took 2 dates:
August 16, 2006 18:12:53 and November 16, 2006 18:12:53
and created a Date object for each.

1. Using SimpleDateFormat I take these Date objects and create strings of
08/16/06 18:12 and 11/16/06 18:12

2. Putting the Date object into a cell and then setting the format to display a date format I get:
08/16/2006 17:12 and 11/16/2006 18:12

The time on the August date from the Date object ( number 2 above) is 1 hour less that the date in the string (nubmer 1 above).

It appears the conversion of the Date object into an Excel formatted date value doesn't account for daylight savings time. Since August 16 2006 is during DST here and November 16, 2006 is not.

Steve

4

Hi Steve,

Sorry for my mistake.

Now I understand your problem.

Could you create xls file which contains the two date object with MS Excel and post here?

It will help me to fix it.

5

Hi,

Please try this fix.

6

Hi Warren,

The fix didn't seem to work. I have attached a zip file containing the code segment I used, an xls using the released 1.7.1.0 and one after using the fixed version you just posted. I hope this helps.

Steve

7

Hi Steve,

We could not find the attached zip. Please attach again.

8

Sorry,

I thought I had attached it. Here it is.

9

Hi,

Please try this fix.

If it doesn't work, please post the result of following codes:

GregorianCalendar calVal = new GregorianCalendar(2006, Calendar.AUGUST, 16, 18, 12, 53);
Object ttableCell = calVal.getTime();

Calendar cal = Calendar.getInstance();
System.out.println(cal.getTimeZone().getID());
cal.setTime((Date)ttableCell);
System.out.println(cal.get(Calendar.DST_OFFSET));

10

Hi Warren,

That seemed to fix my problem. Will this work anywhere in the world? We have customers world wide who will running this. For example, we have had problems with the time zone Asia/Calcutta, which is UTC 5:30 and no DST

I have attached a zip with the updated source code which outputs the time zone id and the DST offset, the console output, and the spreadsheet created. In case you need to use for verification.

Steve

11

Hi Steve,

It only works when the date is in daylight savings time in the time zone which uses daylight savings time.

12

Thanks Warren,

Do you have any idea when will the next official release with this fix in be available?

Thanks, Steve

13

Hi Steve,

We will release official version in the next week.