CODEWITHSUNDEEP
exceldatetimedate
abbas
abbas

Reputation: 61

date time conversion

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

Upvotes: 1

Views: 3919

Answers (5)

Bruno Eberhard
Bruno Eberhard

Reputation: 1704

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 < 61 ? 0 : -1;
        calendar.set(startYear, 0, wholeDays + dayAdjust, 0, 0, 0);
        return calendar.getTime();
  }

Other thread: Program in Java to convert a date to serial number as done in Excel

Upvotes: 0

Dave
Dave

Reputation: 1971

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;
}

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212412

If it is an Excel datestamp, then it's the number of days since 31st December 1899 (with 1900 treated as a leap year); which puts it as 4th March 2010... unless Excel was configured to use the Mac 1904 Calendar, in which case it's the number of days since 1st January 1904.

How to convert it depends on your preferred scripting language; or whether you can simply use Excel itself, and just set the format mask for that cell to one of the date formats

Upvotes: 3

Matteo Italia
Matteo Italia

Reputation: 126787

If it is "seconds past midnight", you can simply do this:

  • divide by 3600 (seconds in an hour); you get the number of hours;
  • take the remainder of that division, and divide it by 60; you get the minutes;
  • the remainder is the seconds.

Example:

40241/3600=11 (641)
641/60=10 (41)

So it is 11:10:41.

By the way, I suppose that it's a time value; if it was a datetime value it would probably be much bigger (like UNIX timestamps) or it would have a decimal part (like, IIRC, OLE dates).

It turns out that it's an Excel date; then, have a look at this KB article, it's all explained in detail; but if you just want to display it correctly, go on the properties of the cell (Ctrl+1) and set its data type to "Date" or "Date/Time" (or whatever it was, I don't have Excel at hand at the moment).

Upvotes: 2

Skurmedel
Skurmedel

Reputation: 22149

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:

Upvotes: 0

Related Questions