MS Excel date/time

About the date/time format in Excel: http://www.cpearson.com/excel/datetime.htm

code based on: http://poi.apache.org,
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java?view=markup

useful ⇒ excel

This is a kind of pseudo code without types and language specific stuff. YEAR, DAY_OF_YEAR, HOUR, MINUTE, SECOND represents the date you want to encode with DAY_OF_YEAR being the number of days since the beginning of the year

leapDays = (YEAR - 1) / 4
         - (YEAR - 1) / 100
         + (YEAR - 1) / 400
         - 460;

date = 365 * (YEAR - 1900) + leapDays + DAY_OF_YEAR + 1;

time = (SECOND + (MINUTE * 60) + (HOUR * 3600)) / (24 * 60 * 60);

excelDate = date + time;

and the same in Java:

Calendar cal = new GregorianCalendar(year, month, day[, hour, minute, second]); // month is zero based!
int year = dt.get(Calendar.YEAR) - 1;
int leapDays = year / 4
             - year / 100
             + year / 400
             - 460;
 
int date = 365 * (dt.get(Calendar.YEAR) - 1900) + leapDays + dt.get(Calendar.DAY_OF_YEAR) + 1;
 
double time = (double)(dt.get(Calendar.SECOND)
            + (dt.get(Calendar.MINUTE) * 60)
            + (dt.get(Calendar.HOUR_OF_DAY) * 3600))
            / 86400d;
 
double excelDate = date + time;

excel ⇒ useful (Java only)

double excelDate;
Calendar cal = new GregorianCalendar(1900, Calendar.JANUARY, 0);
cal.add(Calendar.DATE, ((int)excelDate) - 1);
cal.add(Calendar.SECOND, (int)(86400 * (dt - (int)excelDate)));