Confused about date and time in Excel

Question

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

I fail to understand timezone and daylight saving time handling in this.

This page says that the excel epoch (1/1/1900) is based on the local (=computer creating the Excel file?) timezone. This means that a serial date does not indicate a unique instant in time without the info which computer timezone created it. Not what I would have chosen, but OK.

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

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 out a few 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));
    }
}

Which 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

Note that the change from winter time to DST happens in those hours (check second column, hour 2 is missing).

Now comes the confusion. If I paste this in excel, and for the last column choose "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 in formatting the serial date, does not change to DST. So this is not wallclock time.

Long story short:

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

Answer 1

I suspect that Excel doesn't really take the time zone into account. I suspect it's really just treating it as a "local time" where every conceivable date/time is valid. (A "local instant" in Joda Time parlance, I believe - although I don't know how widely that's used.)

I suspect there's no way of representing a specific instant in time, and that instead you should:

• Take whatever date/time you want to represent as a local time (e.g. "25th March 2012, 3am")
• Put that into a Calendar which is set to use UTC
• Take the millis from calendar.getTime().getTime()
• Subtract the "Excel epoch" value of 1900-01-01T00:00:00Z (again, obtain via a calendar which is set to UTC)
• Divide by "millis per day"

Now there's also an oddity with Excel in terms of its handling of dates before March 1st 1900, but hopefully that won't bite you.