date time conversion

By : abbas
Source: Stackoverflow.com
Question!

I have 40241 as a date value. Which format is this in?

I think it is in seconds past midnight. But I need a formula so that I can work out manually and verify!!

Thanks

By : abbas


Answers

Excel stores dates in an interesting way. I've had this crop up on me too but I never had to move outside Excel so I could just use the format function in Excel.

You can read more here:

By : Skurmedel


Excel saves the date as an integer for the number of days since Jan 1st, 1900

Note: there is a bug in excel so you do the conversion and subtract one. If you see a decimal after it is the time.

Here is some java code to convert it if you want to verify it:

public static Date ExcelDateParse(int ExcelDate){
    Date result = null;
    try{
        GregorianCalendar gc = new GregorianCalendar(1900, Calendar.JANUARY, 1);
        gc.add(Calendar.DATE, ExcelDate - 1);
        result = gc.getTime();
    } catch(RuntimeException e1) {}
    return result;
}     
By : Dave


I condensed the apache solution for the date without time ( https://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java )

  public static Date parseExcelDate(double date) {
        int wholeDays = (int) Math.floor(date);
        Calendar calendar = new GregorianCalendar();
        int startYear = 1900;
        int dayAdjust = wholeDays 


This video can help you solving your question :)
By: admin