Geek Answers Handbook

Vaguely about date and time in Excel

I have Java time (ms since 1/1/1970 UTC) and I would like to write this time to a csv file so that Excel can correctly interpret and format it. I understand that excel uses "serial time" as the format - this is a floating point number, where the integer part gives the number of days since January 1, 1900, and the decimal part gives the fraction of the day.

In this, I do not understand the time and time of daylight saving time.

Does this page say that the excel era (1/1/1900) is based on the local (= computer creating the Excel file)? Timezone. This means that the serial date does not indicate a unique point in time without the information created by it. Not what I would choose, but OK.

Now, accepting this, I figured I could convert Java time to Excel serial date using the following Java code (nb: I'm in Zurich, CET time zone):

private static final long ONE_HOUR= 60L * 60 * 1000; private static final long ONE_DAY = 24 * ONE_HOUR; private static final long excelEpoch; static{ Calendar cal; cal = Calendar.getInstance(TimeZone.getTimeZone("Europe/Zurich")); cal.set(Calendar.YEAR, 1900); cal.set(Calendar.DAY_OF_YEAR, 1); cal.set(Calendar.HOUR_OF_DAY, 0); cal.set(Calendar.MINUTE, 0); cal.set(Calendar.SECOND, 0); cal.set(Calendar.MILLISECOND, 0); excelEpoch = cal.getTimeInMillis(); } private static String formatForExcel(long time){ return ""+(time-excelEpoch)/(double)ONE_DAY; }

Using this, I can print several times:

 public static void main(String[] args) { String sep = "\t"; // csv field separator SimpleDateFormat fmt = new SimpleDateFormat("HH:mm:ss d/M/yyyy"); fmt.setTimeZone(TimeZone.getTimeZone("Europe/Zurich")); System.out.println("Time in ms since 1/1/1970 UTC"+ sep + "Time as string" + sep + "Excel serial" + sep + "Excel serial formatted by excel"); long startTime = 1332630000000L; // 25/3/2012 00:00 CET , shortly before change from winter time to DST for (long t = startTime; t < startTime + 4*ONE_HOUR; t+=ONE_HOUR) { System.out.println(t + sep + fmt.format(new Date(t)) + sep + formatForExcel(t) + sep + formatForExcel(t)); } }

What returns

 Time in ms since 1/1/1970 UTC Time as string Excel serial Excel serial formatted by excel 1332630000000 00:00:00 25/3/2012 40991.0 40991.0 1332633600000 01:00:00 25/3/2012 40991.041666666664 40991.041666666664 1332637200000 03:00:00 25/3/2012 40991.083333333336 40991.083333333336 1332640800000 04:00:00 25/3/2012 40991.125 40991.125

Please note that the transition from winter time to DST occurs during those hours (check the second column, hour 2 is missing).

Now there is confusion. If I paste this into excel, and for the last column select "Format cells ..." and then "Time" (any of the formats), it prints:

 Time in ms since 1/1/1970 UTC Time as string Excel serial Excel serial formatted by excel 1332630000000 25.03.2012 00:00 40991 0:00:00 1332633600000 25.03.2012 01:00 40991.04167 1:00:00 1332637200000 25.03.2012 03:00 40991.08333 2:00:00 1332640800000 25.03.2012 04:00 40991.125 3:00:00

Note that excel does not change to DST when formatting a serial date. So this is not a wall time job.

Shortly speaking:

How do I convert Java time to Excel so that it just works?

+4
source share
2 answers

I suspect Excel does not account for the time zone. I suspect that this simply refers to it as “local time,” where all possible dates / times are valid. (I believe that the "local moment" is in the Joda Time language, although I do not know how widely used.)

I suspect there is no way to represent a specific point in time, and instead you should:

  • Take any date / time that you want to represent as local time (for example, "March 25, 2012, 3 a.m.").
  • Put this in Calendar , which is configured to use UTC
  • Take Millions from calendar.getTime().getTime()
  • Subtract the value of "Excel epoch" at 1900-01-01T00: 00: 00Z (again, get through the calendar that is set to UTC)
  • Divide by millis per day

Now there is also a strangeness with Excel in terms of date processing until March 1, 1900 , but I hope this does not bite you.

+5
source

Convert floating point "serial date / time" to "mills from 1970/1/1"

Note: daysFrom1900to1970 works with Google spreadsheet dates, but may need minor adjustments for excel

  int daysFrom1900to1970 =365*70 + 19; // Or maybe =365*70 + 17 But 19 worked. int millisPerDay = 1000 * 60 * 60 * 24; long millisSince1970 = (long) ((timeToSend.doubleValue() - daysFrom1900to1970) * millisPerDay ); Calendar dateTimeToSend = Calendar.getInstance(); dateTimeToSend.setTimeZone(TimeZone.getTimeZone("GMT")); dateTimeToSend.setTimeInMillis(millisSince1970); System.out.println("timeToSend:"+ new SimpleDateFormat("yyyy.MM.dd HH:mm:ss z").format(dateTimeToSend.getTime()));
0
source

More articles:


All Articles