/**
* DATE
*
* The DATE function returns a value that represents a particular date.
*
* NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date
* format of your regional settings. PHPExcel does not change cell formatting in this way.
*
* Excel Function:
* DATE(year,month,day)
*
* PHPExcel is a lot more forgiving than MS Excel when passing non numeric values to this function.
* A Month name or abbreviation (English only at this point) such as 'January' or 'Jan' will still be accepted,
* as will a day value with a suffix (e.g. '21st' rather than simply 21); again only English language.
*
* @access public
* @category Date/Time Functions
* @param integer $year The value of the year argument can include one to four digits.
* Excel interprets the year argument according to the configured
* date system: 1900 or 1904.
* If year is between 0 (zero) and 1899 (inclusive), Excel adds that
* value to 1900 to calculate the year. For example, DATE(108,1,2)
* returns January 2, 2008 (1900+108).
* If year is between 1900 and 9999 (inclusive), Excel uses that
* value as the year. For example, DATE(2008,1,2) returns January 2,
* 2008.
* If year is less than 0 or is 10000 or greater, Excel returns the
* #NUM! error value.
* @param integer $month A positive or negative integer representing the month of the year
* from 1 to 12 (January to December).
* If month is greater than 12, month adds that number of months to
* the first month in the year specified. For example, DATE(2008,14,2)
* returns the serial number representing February 2, 2009.
* If month is less than 1, month subtracts the magnitude of that
* number of months, plus 1, from the first month in the year
* specified. For example, DATE(2008,-3,2) returns the serial number
* representing September 2, 2007.
* @param integer $day A positive or negative integer representing the day of the month
* from 1 to 31.
* If day is greater than the number of days in the month specified,
* day adds that number of days to the first day in the month. For
* example, DATE(2008,1,35) returns the serial number representing
* February 4, 2008.
* If day is less than 1, day subtracts the magnitude that number of
* days, plus one, from the first day of the month specified. For
* example, DATE(2008,1,-15) returns the serial number representing
* December 16, 2007.
* @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
* depending on the value of the ReturnDateType flag
*/
public static function DATE($year = 0, $month = 1, $day = 1)
{
$year = PHPExcel_Calculation_Functions::flattenSingleValue($year);
$month = PHPExcel_Calculation_Functions::flattenSingleValue($month);
$day = PHPExcel_Calculation_Functions::flattenSingleValue($day);
if ($month !== null && !is_numeric($month)) {
$month = PHPExcel_Shared_Date::monthStringToNumber($month);
}
if ($day !== null && !is_numeric($day)) {
$day = PHPExcel_Shared_Date::dayStringToNumber($day);
}
$year = $year !== null ? PHPExcel_Shared_String::testStringAsNumeric($year) : 0;
$month = $month !== null ? PHPExcel_Shared_String::testStringAsNumeric($month) : 0;
$day = $day !== null ? PHPExcel_Shared_String::testStringAsNumeric($day) : 0;
if (!is_numeric($year) || !is_numeric($month) || !is_numeric($day)) {
return PHPExcel_Calculation_Functions::VALUE();
}
$year = (int) $year;
$month = (int) $month;
$day = (int) $day;
$baseYear = PHPExcel_Shared_Date::getExcelCalendar();
// Validate parameters
if ($year < $baseYear - 1900) {
return PHPExcel_Calculation_Functions::NaN();
}
if ($baseYear - 1900 != 0 && $year < $baseYear && $year >= 1900) {
return PHPExcel_Calculation_Functions::NaN();
}
if ($year < $baseYear && $year >= $baseYear - 1900) {
$year += 1900;
}
if ($month < 1) {
// Handle year/month adjustment if month < 1
--$month;
$year += ceil($month / 12) - 1;
$month = 13 - abs($month % 12);
} elseif ($month > 12) {
// Handle year/month adjustment if month > 12
$year += floor($month / 12);
$month = $month % 12;
}
// Re-validate the year parameter after adjustments
if ($year < $baseYear || $year >= 10000) {
return PHPExcel_Calculation_Functions::NaN();
}
// Execute function
$excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day);
switch (PHPExcel_Calculation_Functions::getReturnDateType()) {
case PHPExcel_Calculation_Functions::RETURNDATE_EXCEL:
return (double) $excelDateValue;
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC:
return (int) PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT:
return PHPExcel_Shared_Date::ExcelToPHPObject($excelDateValue);
}
}
