how to set time format in hours and minutes.. I need in this format only Hours and Minutes.. i need output as a below Format..

Topics: Developer Forum
Dec 15, 2012 at 5:11 AM

<?php
/* Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '
');

//set_time_limit(0);



/* Include PHPExcel */
require_once 'C:\Zend\Apache2\htdocs\magento\report\PHPExcel\PHPExcel_1.7.8\Classes\PHPExcel.php';
require_once  'C:\Zend\Apache2\htdocs\magento\report\PHPExcel\PHPExcel_1.7.8\Classes\PHPExcel\IOFactory.php';

// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();
$worksheet = $objPHPExcel->getActiveSheet();





// Add some data
$testDates = array(    array(3,15,10),        array(13,15,10),    array(15,15,15),    array(3,15,30),
                    array(15,15,15),    array(5,10,5),        array(9,15,0),   
                   
                  );
$testDateCount = count($testDates);

$worksheet->fromArray($testDates,NULL,'E2',true);

for ($row = 2; $row <= $testDateCount; ++$row) {
    $worksheet->setCellValue('H'.$row, '=TIME(E'.$row.',F'.$row.',G'.$row.')');
    $worksheet->setCellValue('I'.$row, '=H'.$row);
}
$worksheet->getStyle('I1:I'.$testDateCount)
          ->getNumberFormat()
          ->setFormatCode('hh:mm:ss');





// Test the formulae

    for ($row = 2; $row <= $testDateCount; ++$row) {
       
         $worksheet->getCell('E'.$row)->getFormattedValue() ;
         $worksheet->getCell('F'.$row)->getFormattedValue() ;
         $worksheet->getCell('G'.$row)->getFormattedValue() ;
         $worksheet->getCell('I'.$row)->getValue() ;
         $worksheet->getCell('I'.$row)->getFormattedValue() ;
         $worksheet->getCell('H'.$row)->getFormattedValue() ;

    }

   

   
   
   
    echo date('H:i:s') , " Rename worksheet" , EOL;
    $objPHPExcel->getActiveSheet()->setTitle('Summary');
   
   
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex();
   
// Save Excel 2007 file
    echo date('H:i:s') , " Write to Excel2007 format" , EOL;
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
    echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
// Save Excel5 file
    echo date('H:i:s') , " Write to Excel5 format" , EOL;
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save(str_replace('.php', '.xls', __FILE__));
    echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
   
   
// Echo memory peak usage
    echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
   
// Echo done
    echo date('H:i:s') , " Done writing files" , EOL;
    echo 'Files have been created in ' , getcwd() , EOL;
   
?>

 

 

 

Recorded Hours Recorded Minutes Adjusted Hours Adjusted Minutes Submitted
0 1 0 1 0:00:00
0 1 0 1 0:00:00
0 4 0 4 0:00:00
0 8 0 8 0:00:00
0 10 0 10 0:00:00
6 52 6 52 0:00:00
Dec 18, 2012 at 12:07 PM

I'm not sure where is your problem...
In Excel, hours, minutes, seconds format is something like h:mm:ss, if your time exceed 24 hours (**), adds the brackets ([h]:mm:ss). Remove ':ss' if you don't want seconds.
For use with PHPExcel:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode('[h]:mm:ss');


You will find many examples in the documentation in Chapter 4.6.19, as well as a trick to know a format from a model in Excel, the documentation of the API for the PHPExcel_Style_NumberFormat class will allow you to see all of the constants can be used as shortcuts.

(**): Excel TIME function does not meet 24 hours (it returns the modulo 24), I have not tested PHPExcel, but it certainly does the same thing, if you need more, you have to change your method, a formula such as: =(A1/24) + (B1/(24*60)) + (C1 /(24*60*60)) will work, with the correct format.