title: Confused about date and time in Excel
body: <p>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.</p>
  
  <p>I fail to understand timezone and daylight saving time handling in this. </p>
  
  <p><a href="http://www.decimaltime.hynes.net/p/dates.html#excel" rel="noreferrer">This page </a> 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.</p>
  
  <p>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):</p>
  
  <pre><code>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;
  }
  </code></pre>
  
  <p>Using this I can print out a few times:</p>
  
  <pre><code>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 &lt; startTime + 4*ONE_HOUR; t+=ONE_HOUR) {
          System.out.println(t + sep + fmt.format(new Date(t)) + sep + formatForExcel(t) + sep + formatForExcel(t));
      }
  }
  </code></pre>
  
  <p>Which returns</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Note that the change from winter time to DST happens in those hours (check second column, hour 2 is missing). </p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Note, that excel in formatting the serial date, does not change to DST. So this is not wallclock time.</p>
  
  <p>Long story short: </p>
  
  <p><strong>How should I convert Java time to Excel so that it just works?</strong></p>