Need help - Uploading Excel XLS files and converting Excel's Date and Time format

[eluser]skattabrain[/eluser]
Hello,

I'm trying to make uploading Excel data as easy as possible for my users. In the past, I had users convert their XLS files to CSV ... then they could upload and import their data using my tools.

This time, it has to be straight XLS files. So I did a quick search on the wiki and found this - http://codeigniter.com/wiki/Excel_Reader_Class/ (thanks to Derek & James Gifford for the work there).

So this works great, but I'm having trouble with 1 crucial piece ... taking the date and time field from the XLS and converting it into a date I can work with.

I found a couple functions searching google for converting the date part ... but not the time. Excel stores it's dates and times in a serial format. I can't seem to figure this out ... driving me nuts!

This will return the date, but no time ...

Code:

function excel_D2DMY($days) {
   if ($days <1) return "";
   if ($days == 60)   {
      return array('day'=>29,'month'=>2,'year'=>1900);
   } else {
      if ($days < 60)   {
         // Because of the 29-02-1900 bug, any serial date
         // under 60 is one off... Compensate.
         ++$days;
      }
      // Modified Julian to DMY calculation with an addition of 2415019
     $l = $days + 68569 + 2415019;
     $n = floor(( 4 * $l ) / 146097);
     $l = $l - floor(( 146097 * $n + 3 ) / 4);
     $i = floor(( 4000 * ( $l + 1 ) ) / 1461001);
     $l = $l - floor(( 1461 * $i ) / 4) + 31;
     $j = floor(( 80 * $l ) / 2447);
     $nDay = $l - floor(( 2447 * $j ) / 80);
     $l = floor($j / 11);
     $nMonth = $j + 2 - ( 12 * $l );
     $nYear = 100 * ( $n - 49 ) + $i + $l;
     $ret = $nDay.' - '.$nMonth.' - '.$nYear;
     return $ret;
   }
}

Here is a snippet of the array the Excel Reader creates ...

Code:

array(13) {
    [0]=>
    string(13) "NUM"
    [1]=>
    string(4) "DATE"
    [2]=>
    string(15) "TIME (HH:MM:SS)"
    [3]=>
    string(13) "NUMC"
    [4]=>
    string(4) "MODE"
    [5]=>
    string(11) "LENGTH"
    [6]=>
    string(18) "DURATION"
    [7]=>
    string(9) "CTYPE"
    [8]=>
    string(4) "ZX"
    [9]=>
    string(7) "DOL"
    [10]=>
    string(5) "POUND"
    [11]=>
    string(11) "DEST"
    [12]=>
    string(8) "LOC"
  }
  [1]=>
  array(8) {
    [0]=>
    float(7852210022)
    [1]=>
    int(39537)
    [2]=>
    float(0.33478009259259)
    [3]=>
    float(7748967521)
    [5]=>
    float(0.0024421296296296)
    [6]=>
    int(4)
    [8]=>
    string(2) "UK"
    [9]=>
    float(2.6)
  }

Specifically ... these pieces of the array ...

Code:

[1]=>
    int(39537)
    [2]=>
    float(0.33478009259259)

Any ideas?

My users have several of these and their different XLS files have different date formatting ... so CSV exports will be a problem ... I chose to keep XLS because Excel keeps the the date in this particular format ... so if I can convert it ... then I'm set!

Thanks if you have input.