34        define(
'PHPEXCEL_ROOT', dirname(__FILE__) . 
'/../../');
 
   35        require(PHPEXCEL_ROOT . 
'PHPExcel/Autoloader.php');
 
   55                return ((($year % 4) == 0) && (($year % 100) != 0) || (($year % 400) == 0));
 
   72                if ($startDay == 31) {
 
   74                } elseif ($methodUS && ($startMonth == 2 && ($startDay == 29 || ($startDay == 28 && !self::_isLeapYear(
$startYear))))) {
 
   78                        if ($methodUS && $startDay != 30) {
 
   80                                if ($endMonth == 12) {
 
   91                return $endDay + $endMonth * 30 + 
$endYear * 360 - $startDay - $startMonth * 30 - 
$startYear * 360;
 
  102                if (!is_numeric($dateValue)) {
 
  103                        if ((is_string($dateValue)) &&
 
  107                        if ((is_object($dateValue)) && ($dateValue instanceof DateTime)) {
 
  138                $oMonth = (int) $PHPDateObject->format(
'm');
 
  139                $oYear = (int) $PHPDateObject->format(
'Y');
 
  141                $adjustmentMonthsString = (string) $adjustmentMonths;
 
  142                if ($adjustmentMonths > 0) {
 
  143                        $adjustmentMonthsString = 
'+'.$adjustmentMonths;
 
  145                if ($adjustmentMonths != 0) {
 
  146                        $PHPDateObject->modify($adjustmentMonthsString.
' months');
 
  148                $nMonth = (int) $PHPDateObject->format(
'm');
 
  149                $nYear = (int) $PHPDateObject->format(
'Y');
 
  151                $monthDiff = ($nMonth - $oMonth) + (($nYear - $oYear) * 12);
 
  152                if ($monthDiff != $adjustmentMonths) {
 
  153                        $adjustDays = (int) $PHPDateObject->format(
'd');
 
  154                        $adjustDaysString = 
'-'.$adjustDays.
' days';
 
  155                        $PHPDateObject->modify($adjustDaysString);
 
  157                return $PHPDateObject;
 
  181                $saveTimeZone = date_default_timezone_get();
 
  182                date_default_timezone_set(
'UTC');
 
  189                                        $retValue = (integer) time();
 
  192                                        $retValue = 
new DateTime();
 
  195                date_default_timezone_set($saveTimeZone);
 
  221                $saveTimeZone = date_default_timezone_get();
 
  222                date_default_timezone_set(
'UTC');
 
  227                                        $retValue = (float) $excelDateTime;
 
  236                date_default_timezone_set($saveTimeZone);
 
  292        public static function DATE($year = 0, $month = 1, $day = 1) {
 
  297                if (($month !== NULL) && (!is_numeric($month))) {
 
  301                if (($day !== NULL) && (!is_numeric($day))) {
 
  308                if ((!is_numeric($year)) ||
 
  309                        (!is_numeric($month)) ||
 
  310                        (!is_numeric($day))) {
 
  313                $year   = (integer) $year;
 
  314                $month  = (integer) $month;
 
  315                $day    = (integer) $day;
 
  319                if ($year < ($baseYear-1900)) {
 
  322                if ((($baseYear-1900) != 0) && ($year < $baseYear) && ($year >= 1900)) {
 
  326                if (($year < $baseYear) && ($year >= ($baseYear-1900))) {
 
  333                        $year += ceil($month / 12) - 1;
 
  334                        $month = 13 - abs($month % 12);
 
  335                } elseif ($month > 12) {
 
  337                        $year += floor($month / 12);
 
  338                        $month = ($month % 12);
 
  342                if (($year < $baseYear) || ($year >= 10000)) {
 
  350                                        return (
float) $excelDateValue;
 
  386        public static function TIME($hour = 0, $minute = 0, $second = 0) {
 
  391                if ($hour == 
'') { $hour = 0; }
 
  392                if ($minute == 
'') { $minute = 0; }
 
  393                if ($second == 
'') { $second = 0; }
 
  395                if ((!is_numeric($hour)) || (!is_numeric($minute)) || (!is_numeric($second))) {
 
  398                $hour   = (integer) $hour;
 
  399                $minute = (integer) $minute;
 
  400                $second = (integer) $second;
 
  403                        $minute += floor($second / 60);
 
  404                        $second = 60 - abs($second % 60);
 
  405                        if ($second == 60) { $second = 0; }
 
  406                } elseif ($second >= 60) {
 
  407                        $minute += floor($second / 60);
 
  408                        $second = $second % 60;
 
  411                        $hour += floor($minute / 60);
 
  412                        $minute = 60 - abs($minute % 60);
 
  413                        if ($minute == 60) { $minute = 0; }
 
  414                } elseif ($minute >= 60) {
 
  415                        $hour += floor($minute / 60);
 
  416                        $minute = $minute % 60;
 
  421                } elseif ($hour < 0) {
 
  439                                                $dayAdjust = floor($hour / 24);
 
  440                                                $hour = 24 - abs($hour % 24);
 
  441                                                if ($hour == 24) { $hour = 0; }
 
  442                                        } elseif ($hour >= 24) {
 
  443                                                $dayAdjust = floor($hour / 24);
 
  446                                        $phpDateObject = 
new DateTime(
'1900-01-01 '.$hour.
':'.$minute.
':'.$second);
 
  447                                        if ($dayAdjust != 0) {
 
  448                                                $phpDateObject->modify($dayAdjust.
' days');
 
  450                                        return $phpDateObject;
 
  484                $dateValue = preg_replace(
'/(\d)(st|nd|rd|th)([ -\/])/Ui',
'$1$3',$dateValue);
 
  486                $dateValue      = str_replace(array(
'/',
'.',
'-',
'  '),array(
' ',
' ',
' ',
' '),$dateValue);
 
  489                $t1 = explode(
' ',$dateValue);
 
  490                foreach($t1 as &
$t) {
 
  491                        if ((is_numeric(
$t)) && (
$t > 31)) {
 
  495                                        if (
$t < 100) { 
$t += 1900; }
 
  500                if ((count($t1) == 1) && (strpos(
$t,
':') != 
false)) {
 
  503                } elseif (count($t1) == 2) {
 
  506                                array_unshift($t1,1);
 
  508                                array_push($t1,
date(
'Y'));
 
  512                $dateValue = implode(
' ',$t1);
 
  514                $PHPDateArray = date_parse($dateValue);
 
  515                if (($PHPDateArray === False) || ($PHPDateArray[
'error_count'] > 0)) {
 
  516                        $testVal1 = strtok($dateValue,
'- ');
 
  517                        if ($testVal1 !== False) {
 
  518                                $testVal2 = strtok(
'- ');
 
  519                                if ($testVal2 !== False) {
 
  520                                        $testVal3 = strtok(
'- ');
 
  521                                        if ($testVal3 === False) {
 
  522                                                $testVal3 = strftime(
'%Y');
 
  530                        $PHPDateArray = date_parse($testVal1.
'-'.$testVal2.
'-'.$testVal3);
 
  531                        if (($PHPDateArray === False) || ($PHPDateArray[
'error_count'] > 0)) {
 
  532                                $PHPDateArray = date_parse($testVal2.
'-'.$testVal1.
'-'.$testVal3);
 
  533                                if (($PHPDateArray === False) || ($PHPDateArray[
'error_count'] > 0)) {
 
  539                if (($PHPDateArray !== False) && ($PHPDateArray[
'error_count'] == 0)) {
 
  541                        if ($PHPDateArray[
'year'] == 
'')        { $PHPDateArray[
'year'] = strftime(
'%Y'); }
 
  542                        if ($PHPDateArray[
'year'] < 1900)
 
  544                        if ($PHPDateArray[
'month'] == 
'')       { $PHPDateArray[
'month'] = strftime(
'%m'); }
 
  545                        if ($PHPDateArray[
'day'] == 
'')         { $PHPDateArray[
'day'] = strftime(
'%d'); }
 
  546                        $excelDateValue = floor(
PHPExcel_Shared_Date::FormattedPHPToExcel($PHPDateArray[
'year'],$PHPDateArray[
'month'],$PHPDateArray[
'day'],$PHPDateArray[
'hour'],$PHPDateArray[
'minute'],$PHPDateArray[
'second']));
 
  550                                                return (
float) $excelDateValue;
 
  554                                                return new DateTime($PHPDateArray[
'year'].
'-'.$PHPDateArray[
'month'].
'-'.$PHPDateArray[
'day'].
' 00:00:00');
 
  585                $timeValue      = str_replace(array(
'/',
'.'),array(
'-',
'-'),$timeValue);
 
  587                $PHPDateArray = date_parse($timeValue);
 
  588                if (($PHPDateArray !== False) && ($PHPDateArray[
'error_count'] == 0)) {
 
  597                                                return (
float) $excelDateValue;
 
  601                                                return new DateTime(
'1900-01-01 '.$PHPDateArray[
'hour'].
':'.$PHPDateArray[
'minute'].
':'.$PHPDateArray[
'second']);
 
  618        public static function DATEDIF($startDate = 0, $endDate = 0, $unit = 
'D') {
 
  623                if (is_string($startDate = self::_getDateValue($startDate))) {
 
  626                if (is_string($endDate = self::_getDateValue($endDate))) {
 
  631                if ($startDate >= $endDate) {
 
  636                $difference = $endDate - $startDate;
 
  639                $startDays = $PHPStartDateObject->format(
'j');
 
  640                $startMonths = $PHPStartDateObject->format(
'n');
 
  641                $startYears = $PHPStartDateObject->format(
'Y');
 
  644                $endDays = $PHPEndDateObject->format(
'j');
 
  645                $endMonths = $PHPEndDateObject->format(
'n');
 
  646                $endYears = $PHPEndDateObject->format(
'Y');
 
  651                                $retVal = intval($difference);
 
  654                                $retVal = intval($endMonths - $startMonths) + (intval($endYears - $startYears) * 12);
 
  656                                if ($endDays < $startDays) {
 
  661                                $retVal = intval($endYears - $startYears);
 
  663                                if ($endMonths < $startMonths) {
 
  665                                } elseif (($endMonths == $startMonths) && ($endDays < $startDays)) {
 
  670                                if ($endDays < $startDays) {
 
  672                                        $PHPEndDateObject->modify(
'-'.$endDays.
' days');
 
  673                                        $adjustDays = $PHPEndDateObject->format(
'j');
 
  674                                        if ($adjustDays > $startDays) {
 
  675                                                $retVal += ($adjustDays - $startDays);
 
  678                                        $retVal = $endDays - $startDays;
 
  682                                $retVal = intval($endMonths - $startMonths);
 
  683                                if ($retVal < 0) $retVal = 12 + $retVal;
 
  685                                if ($endDays < $startDays) {
 
  690                                $retVal = intval($difference);
 
  691                                if ($endYears > $startYears) {
 
  692                                        while ($endYears > $startYears) {
 
  693                                                $PHPEndDateObject->modify(
'-1 year');
 
  694                                                $endYears = $PHPEndDateObject->format(
'Y');
 
  696                                        $retVal = $PHPEndDateObject->format(
'z') - $PHPStartDateObject->format(
'z');
 
  697                                        if ($retVal < 0) { $retVal += 365; }
 
  736        public static function DAYS360($startDate = 0, $endDate = 0, $method = 
false) {
 
  740                if (is_string($startDate = self::_getDateValue($startDate))) {
 
  743                if (is_string($endDate = self::_getDateValue($endDate))) {
 
  747                if (!is_bool($method)) {
 
  753                $startDay = $PHPStartDateObject->format(
'j');
 
  754                $startMonth = $PHPStartDateObject->format(
'n');
 
  755                $startYear = $PHPStartDateObject->format(
'Y');
 
  758                $endDay = $PHPEndDateObject->format(
'j');
 
  759                $endMonth = $PHPEndDateObject->format(
'n');
 
  760                $endYear = $PHPEndDateObject->format(
'Y');
 
  791        public static function YEARFRAC($startDate = 0, $endDate = 0, $method = 0) {
 
  796                if (is_string($startDate = self::_getDateValue($startDate))) {
 
  799                if (is_string($endDate = self::_getDateValue($endDate))) {
 
  803                if (((is_numeric($method)) && (!is_string($method))) || ($method == 
'')) {
 
  818                                                        if (($startMonth < 3) ||
 
  819                                                                (($endMonth * 100 + $endDay) >= (2 * 100 + 29))) {
 
  828                                                                if ($startMonth < 3) {
 
  834                                                                if (($endMonth * 100 + $endDay) >= (2 * 100 + 29)) {
 
  844                                                        } elseif ($days < 366) {
 
  850                                        return $days / (365 + $leapDays);
 
  892                array_shift($dateArgs);
 
  893                array_shift($dateArgs);
 
  896                if (is_string($startDate = $sDate = self::_getDateValue($startDate))) {
 
  899                $startDate = (float) floor($startDate);
 
  900                if (is_string($endDate = $eDate = self::_getDateValue($endDate))) {
 
  903                $endDate = (float) floor($endDate);
 
  905                if ($sDate > $eDate) {
 
  912                if ($startDoW < 0) { $startDoW = 0; }
 
  914                if ($endDoW >= 6) { $endDoW = 0; }
 
  916                $wholeWeekDays = floor(($endDate - $startDate) / 7) * 5;
 
  917                $partWeekDays = $endDoW + $startDoW;
 
  918                if ($partWeekDays > 5) {
 
  923                $holidayCountedArray = array();
 
  924                foreach ($dateArgs as $holidayDate) {
 
  925                        if (is_string($holidayDate = self::_getDateValue($holidayDate))) {
 
  928                        if (($holidayDate >= $startDate) && ($holidayDate <= $endDate)) {
 
  929                                if ((self::DAYOFWEEK($holidayDate,2) < 6) && (!in_array($holidayDate,$holidayCountedArray))) {
 
  931                                        $holidayCountedArray[] = $holidayDate;
 
  936                if ($sDate > $eDate) {
 
  937                        return 0 - ($wholeWeekDays + $partWeekDays);
 
  939                return $wholeWeekDays + $partWeekDays;
 
  968        public static function WORKDAY($startDate,$endDays) {
 
  974                array_shift($dateArgs);
 
  975                array_shift($dateArgs);
 
  977                if ((is_string($startDate = self::_getDateValue($startDate))) || (!is_numeric($endDays))) {
 
  980                $startDate = (float) floor($startDate);
 
  981                $endDays = (int) floor($endDays);
 
  983                if ($endDays == 0) { 
return $startDate; }
 
  985                $decrementing = ($endDays < 0) ? True : False;
 
  990                if (self::DAYOFWEEK($startDate,3) >= 5) {
 
  991                        $startDate += ($decrementing) ? -$startDoW + 4: 7 - $startDoW;
 
  992                        ($decrementing) ? $endDays++ : $endDays--;
 
  996                $endDate = (float) $startDate + (intval($endDays / 5) * 7) + ($endDays % 5);
 
 1001                        $endDate += ($decrementing) ? -$endDoW + 4: 7 - $endDoW;
 
 1005                if (!empty($dateArgs)) {
 
 1006                        $holidayCountedArray = $holidayDates = array();
 
 1007                        foreach ($dateArgs as $holidayDate) {
 
 1008                                if (($holidayDate !== NULL) && (trim($holidayDate) > 
'')) {
 
 1009                                        if (is_string($holidayDate = self::_getDateValue($holidayDate))) {
 
 1012                                        if (self::DAYOFWEEK($holidayDate,3) < 5) {
 
 1013                                                $holidayDates[] = $holidayDate;
 
 1017                        if ($decrementing) {
 
 1018                                rsort($holidayDates, SORT_NUMERIC);
 
 1020                                sort($holidayDates, SORT_NUMERIC);
 
 1022                        foreach ($holidayDates as $holidayDate) {
 
 1023                                if ($decrementing) {
 
 1024                                        if (($holidayDate <= $startDate) && ($holidayDate >= $endDate)) {
 
 1025                                                if (!in_array($holidayDate,$holidayCountedArray)) {
 
 1027                                                        $holidayCountedArray[] = $holidayDate;
 
 1031                                        if (($holidayDate >= $startDate) && ($holidayDate <= $endDate)) {
 
 1032                                                if (!in_array($holidayDate,$holidayCountedArray)) {
 
 1034                                                        $holidayCountedArray[] = $holidayDate;
 
 1041                                        $endDate += ($decrementing) ? -$endDoW + 4: 7 - $endDoW;
 
 1049                                        return (
float) $endDate;
 
 1074                if ($dateValue === 
null) {
 
 1076                } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
 
 1078                } elseif ($dateValue == 0.0) {
 
 1080                } elseif ($dateValue < 0.0) {
 
 1087                return (
int) $PHPDateObject->format(
'j');
 
 1112                if (!is_numeric(
$style)) {
 
 1119                if ($dateValue === 
null) {
 
 1121                } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
 
 1123                } elseif ($dateValue < 0.0) {
 
 1129                $DoW = $PHPDateObject->format(
'w');
 
 1135                        case 2: 
if ($DoW == 0) { $DoW = 7; }
 
 1137                        case 3: 
if ($DoW == 0) { $DoW = 7; }
 
 1144                        if (($PHPDateObject->format(
'Y') == 1900) && ($PHPDateObject->format(
'n') <= 2)) {
 
 1146                                if ($DoW < $firstDay) {
 
 1180                if (!is_numeric($method)) {
 
 1182                } elseif (($method < 1) || ($method > 2)) {
 
 1185                $method = floor($method);
 
 1187                if ($dateValue === 
null) {
 
 1189                } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
 
 1191                } elseif ($dateValue < 0.0) {
 
 1197                $dayOfYear = $PHPDateObject->format(
'z');
 
 1198                $dow = $PHPDateObject->format(
'w');
 
 1199                $PHPDateObject->modify(
'-'.$dayOfYear.
' days');
 
 1200                $dow = $PHPDateObject->format(
'w');
 
 1201                $daysInFirstWeek = 7 - (($dow + (2 - $method)) % 7);
 
 1202                $dayOfYear -= $daysInFirstWeek;
 
 1203                $weekOfYear = ceil($dayOfYear / 7) + 1;
 
 1205                return (
int) $weekOfYear;
 
 1225                if ($dateValue === 
null) {
 
 1227                } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
 
 1229                } elseif ($dateValue < 0.0) {
 
 1236                return (
int) $PHPDateObject->format(
'n');
 
 1253        public static function YEAR($dateValue = 1) {
 
 1256                if ($dateValue === 
null) {
 
 1258                } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
 
 1260                } elseif ($dateValue < 0.0) {
 
 1267                return (
int) $PHPDateObject->format(
'Y');
 
 1287                if (!is_numeric($timeValue)) {
 
 1289                                $testVal = strtok($timeValue,
'/-: ');
 
 1290                                if (strlen($testVal) < strlen($timeValue)) {
 
 1295                        if (is_string($timeValue)) {
 
 1300                if ($timeValue >= 1) {
 
 1301                        $timeValue = fmod($timeValue,1);
 
 1302                } elseif ($timeValue < 0.0) {
 
 1307                return (
int) gmdate(
'G',$timeValue);
 
 1327                if (!is_numeric($timeValue)) {
 
 1329                                $testVal = strtok($timeValue,
'/-: ');
 
 1330                                if (strlen($testVal) < strlen($timeValue)) {
 
 1335                        if (is_string($timeValue)) {
 
 1340                if ($timeValue >= 1) {
 
 1341                        $timeValue = fmod($timeValue,1);
 
 1342                } elseif ($timeValue < 0.0) {
 
 1347                return (
int) gmdate(
'i',$timeValue);
 
 1367                if (!is_numeric($timeValue)) {
 
 1369                                $testVal = strtok($timeValue,
'/-: ');
 
 1370                                if (strlen($testVal) < strlen($timeValue)) {
 
 1375                        if (is_string($timeValue)) {
 
 1380                if ($timeValue >= 1) {
 
 1381                        $timeValue = fmod($timeValue,1);
 
 1382                } elseif ($timeValue < 0.0) {
 
 1387                return (
int) gmdate(
's',$timeValue);
 
 1410        public static function EDATE($dateValue = 1, $adjustmentMonths = 0) {
 
 1414                if (!is_numeric($adjustmentMonths)) {
 
 1417                $adjustmentMonths = floor($adjustmentMonths);
 
 1419                if (is_string($dateValue = self::_getDateValue($dateValue))) {
 
 1432                                        return $PHPDateObject;
 
 1455        public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0) {
 
 1459                if (!is_numeric($adjustmentMonths)) {
 
 1462                $adjustmentMonths = floor($adjustmentMonths);
 
 1464                if (is_string($dateValue = self::_getDateValue($dateValue))) {
 
 1470                $adjustDays = (int) $PHPDateObject->format(
'd');
 
 1471                $adjustDaysString = 
'-'.$adjustDays.
' days';
 
 1472                $PHPDateObject->modify($adjustDaysString);
 
 1480                                        return $PHPDateObject;
 
date( 'd-M-Y', $objPHPExcel->getProperties() ->getCreated())
An exception for terminatinating execution or to throw for unit testing.
static YEAR($dateValue=1)
YEAR.
static TIME($hour=0, $minute=0, $second=0)
static EOMONTH($dateValue=1, $adjustmentMonths=0)
EOMONTH.
static DAYOFMONTH($dateValue=1)
DAYOFMONTH.
static _getTimeValue($timeValue)
_getTimeValue
static HOUROFDAY($timeValue=0)
HOUROFDAY.
static _adjustDateByMonths($dateValue=0, $adjustmentMonths=0)
static MONTHOFYEAR($dateValue=1)
MONTHOFYEAR.
static WORKDAY($startDate, $endDays)
static DATEDIF($startDate=0, $endDate=0, $unit='D')
DATEDIF.
static DATEVALUE($dateValue=1)
static WEEKOFYEAR($dateValue=1, $method=1)
WEEKOFYEAR.
static SECONDOFMINUTE($timeValue=0)
SECONDOFMINUTE.
static _isLeapYear($year)
Identify if a year is a leap year or not.
static MINUTEOFHOUR($timeValue=0)
MINUTEOFHOUR.
static DATE($year=0, $month=1, $day=1)
static YEARFRAC($startDate=0, $endDate=0, $method=0)
static TIMEVALUE($timeValue)
static DAYS360($startDate=0, $endDate=0, $method=false)
static DAYOFWEEK($dateValue=1, $style=1)
DAYOFWEEK.
static _dateDiff360($startDay, $startMonth, $startYear, $endDay, $endMonth, $endYear, $methodUS)
Return the number of days between two dates based on a 360 day calendar.
static _getDateValue($dateValue)
_getDateValue
static EDATE($dateValue=1, $adjustmentMonths=0)
EDATE.
static NETWORKDAYS($startDate, $endDate)
const RETURNDATE_PHP_OBJECT
static getReturnDateType()
static getCompatibilityMode()
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
const COMPATIBILITY_GNUMERIC
static setReturnDateType($returnDateType)
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
const COMPATIBILITY_EXCEL
constants
const RETURNDATE_PHP_NUMERIC
const COMPATIBILITY_OPENOFFICE
static FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)
FormattedPHPToExcel.
static PHPToExcel($dateValue=0, $adjustToTimezone=FALSE, $timezone=NULL)
Convert a date from PHP to Excel.
static dayStringToNumber($day)
static ExcelToPHPObject($dateValue=0)
Convert a date from Excel to a PHP Date/Time object.
static monthStringToNumber($month)
static getExcelCalendar()
Return the Excel calendar (Windows 1900 or Mac 1904)
const CALENDAR_WINDOWS_1900
constants
static ExcelToPHP($dateValue=0, $adjustToTimezone=FALSE, $timezone=NULL)
Convert a date from Excel to PHP.
static testStringAsNumeric($value)
Retrieve any leading numeric part of a string, or return the full string if no leading numeric (handl...
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'