class PHPExcel_Calculation_DateTime

PHPExcelCalculationDateTime

Copyright (c) 2006 - 2015 PHPExcel

This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

Methods

static boolean
isLeapYear(integer $year)

Identify if a year is a leap year or not

static mixed
getDateValue(string $dateValue)

getDateValue

static mixed
DATETIMENOW()

DATETIMENOW

static mixed
DATENOW()

DATENOW

static mixed
DATE(integer $year, integer $month = 1, integer $day = 1)

DATE

static mixed
TIME(integer $hour, integer $minute, integer $second)

TIME

static mixed
DATEVALUE(string $dateValue = 1)

DATEVALUE

static mixed
TIMEVALUE(string $timeValue)

TIMEVALUE

static integer
DATEDIF(mixed $startDate, mixed $endDate, string $unit = 'D')

DATEDIF

static integer
DAYS360(mixed $startDate, mixed $endDate, boolean $method = false)

DAYS360

static float
YEARFRAC(mixed $startDate, mixed $endDate, integer $method)

YEARFRAC

static integer
NETWORKDAYS($startDate, $endDate)

NETWORKDAYS

static mixed
WORKDAY($startDate, $endDays)

WORKDAY

static int
DAYOFMONTH(mixed $dateValue = 1)

DAYOFMONTH

static int
DAYOFWEEK(mixed $dateValue = 1, int $style = 1)

DAYOFWEEK

static int
WEEKOFYEAR(mixed $dateValue = 1, boolean $method = 1)

WEEKOFYEAR

static int
MONTHOFYEAR(mixed $dateValue = 1)

MONTHOFYEAR

static int
YEAR(mixed $dateValue = 1)

YEAR

static int
HOUROFDAY(mixed $timeValue)

HOUROFDAY

static int
MINUTEOFHOUR(mixed $timeValue)

MINUTEOFHOUR

static int
SECONDOFMINUTE(mixed $timeValue)

SECONDOFMINUTE

static mixed
EDATE(mixed $dateValue = 1, int $adjustmentMonths)

EDATE

static mixed
EOMONTH(mixed $dateValue = 1, int $adjustmentMonths)

EOMONTH

Details

at line 65
static boolean isLeapYear(integer $year)

Identify if a year is a leap year or not

Parameters

integer $year The year to test

Return Value

boolean TRUE if the year is a leap year, otherwise FALSE

at line 114
static mixed getDateValue(string $dateValue)

getDateValue

Parameters

string $dateValue

Return Value

mixed Excel date/time serial value, or string if error

at line 196
static mixed DATETIMENOW()

DATETIMENOW

Returns the current date and time. The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.

NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date and time format of your regional settings. PHPExcel does not change cell formatting in this way.

Excel Function: NOW()

Return Value

mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, depending on the value of the ReturnDateType flag

at line 237
static mixed DATENOW()

DATENOW

Returns the current date. The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.

NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date and time format of your regional settings. PHPExcel does not change cell formatting in this way.

Excel Function: TODAY()

Return Value

mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, depending on the value of the ReturnDateType flag

at line 310
static mixed DATE(integer $year, integer $month = 1, integer $day = 1)

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.

Parameters

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.
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.
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 Value

mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, depending on the value of the ReturnDateType flag

at line 405
static mixed TIME(integer $hour, integer $minute, integer $second)

TIME

The TIME function returns a value that represents a particular time.

NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the time format of your regional settings. PHPExcel does not change cell formatting in this way.

Excel Function: TIME(hour,minute,second)

Parameters

integer $hour A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.
integer $minute A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM.
integer $second A number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM

Return Value

mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, depending on the value of the ReturnDateType flag

at line 513
static mixed DATEVALUE(string $dateValue = 1)

DATEVALUE

Returns a value that represents a particular date. Use DATEVALUE to convert a date represented by a text string to an Excel or PHP date/time stamp value.

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: DATEVALUE(dateValue)

Parameters

string $dateValue Text that represents a date in a Microsoft Excel date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates. Using the default date system in Excel for Windows, datetext must represent a date from January 1, 1900, to December 31, 9999. Using the default date system in Excel for the Macintosh, datetext must represent a date from January 1, 1904, to December 31, 9999. DATEVALUE returns the #VALUE! error value if date_text is out of this range.

Return Value

mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, depending on the value of the ReturnDateType flag

at line 634
static mixed TIMEVALUE(string $timeValue)

TIMEVALUE

Returns a value that represents a particular time. Use TIMEVALUE to convert a time represented by a text string to an Excel or PHP date/time stamp value.

NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the time format of your regional settings. PHPExcel does not change cell formatting in this way.

Excel Function: TIMEVALUE(timeValue)

Parameters

string $timeValue A text string that represents a time in any one of the Microsoft Excel time formats; for example, "6:45 PM" and "18:45" text strings within quotation marks that represent time. Date information in time_text is ignored.

Return Value

mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, depending on the value of the ReturnDateType flag

at line 677
static integer DATEDIF(mixed $startDate, mixed $endDate, string $unit = 'D')

DATEDIF

Parameters

mixed $startDate Excel date serial value, PHP date/time stamp, PHP DateTime object or a standard date string
mixed $endDate Excel date serial value, PHP date/time stamp, PHP DateTime object or a standard date string
string $unit

Return Value

integer Interval between the dates

at line 800
static integer DAYS360(mixed $startDate, mixed $endDate, boolean $method = false)

DAYS360

Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

Excel Function: DAYS360(startDate,endDate[,method])

Parameters

mixed $startDate Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string
mixed $endDate Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string
boolean $method US or European Method FALSE or omitted: U.S. (NASD) method. If the starting date is the last day of a month, it becomes equal to the 30th of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month. TRUE: European method. Starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month.

Return Value

integer Number of days between start date and end date

at line 856
static float YEARFRAC(mixed $startDate, mixed $endDate, integer $method)

YEARFRAC

Calculates the fraction of the year represented by the number of whole days between two dates (the startdate and the enddate). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.

Excel Function: YEARFRAC(startDate,endDate[,method])

Parameters

mixed $startDate Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string
mixed $endDate Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string
integer $method Method used for the calculation 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360

Return Value

float fraction of the year

at line 952
static integer NETWORKDAYS($startDate, $endDate)

NETWORKDAYS

Returns the number of whole working days between startdate and enddate. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

Excel Function: NETWORKDAYS(startDate,endDate[,holidays[,holiday[,...]]])

Parameters

$startDate
$endDate

Return Value

integer Interval between the dates

at line 1039
static mixed WORKDAY($startDate, $endDays)

WORKDAY

Returns the date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

Excel Function: WORKDAY(startDate,endDays[,holidays[,holiday[,...]]])

Parameters

$startDate
$endDays

Return Value

mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, depending on the value of the ReturnDateType flag

at line 1144
static int DAYOFMONTH(mixed $dateValue = 1)

DAYOFMONTH

Returns the day of the month, for a specified date. The day is given as an integer ranging from 1 to 31.

Excel Function: DAY(dateValue)

Parameters

mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string

Return Value

int Day of the month

at line 1182
static int DAYOFWEEK(mixed $dateValue = 1, int $style = 1)

DAYOFWEEK

Returns the day of the week for a specified date. The day is given as an integer ranging from 0 to 7 (dependent on the requested style).

Excel Function: WEEKDAY(dateValue[,style])

Parameters

mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string
int $style A number that determines the type of return value 1 or omitted Numbers 1 (Sunday) through 7 (Saturday). 2 Numbers 1 (Monday) through 7 (Sunday). 3 Numbers 0 (Monday) through 6 (Sunday).

Return Value

int Day of the week value

at line 1258
static int WEEKOFYEAR(mixed $dateValue = 1, boolean $method = 1)

WEEKOFYEAR

Returns the week of the year for a specified date. The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard.

Excel Function: WEEKNUM(dateValue[,style])

Parameters

mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string
boolean $method Week begins on Sunday or Monday 1 or omitted Week begins on Sunday. 2 Week begins on Monday.

Return Value

int Week Number

at line 1305
static int MONTHOFYEAR(mixed $dateValue = 1)

MONTHOFYEAR

Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).

Excel Function: MONTH(dateValue)

Parameters

mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string

Return Value

int Month of the year

at line 1337
static int YEAR(mixed $dateValue = 1)

YEAR

Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.

Excel Function: YEAR(dateValue)

Parameters

mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string

Return Value

int Year

at line 1369
static int HOUROFDAY(mixed $timeValue)

HOUROFDAY

Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Excel Function: HOUR(timeValue)

Parameters

mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard time string

Return Value

int Hour

at line 1410
static int MINUTEOFHOUR(mixed $timeValue)

MINUTEOFHOUR

Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

Excel Function: MINUTE(timeValue)

Parameters

mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard time string

Return Value

int Minute

at line 1451
static int SECONDOFMINUTE(mixed $timeValue)

SECONDOFMINUTE

Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59.

Excel Function: SECOND(timeValue)

Parameters

mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard time string

Return Value

int Second

at line 1498
static mixed EDATE(mixed $dateValue = 1, int $adjustmentMonths)

EDATE

Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

Excel Function: EDATE(dateValue,adjustmentMonths)

Parameters

mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string
int $adjustmentMonths The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.

Return Value

mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, depending on the value of the ReturnDateType flag

at line 1544
static mixed EOMONTH(mixed $dateValue = 1, int $adjustmentMonths)

EOMONTH

Returns the date value for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

Excel Function: EOMONTH(dateValue,adjustmentMonths)

Parameters

mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), PHP DateTime object, or a standard date string
int $adjustmentMonths The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.

Return Value

mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, depending on the value of the ReturnDateType flag