Date Functions
| Formulas | Description | Examples |
|---|---|---|
| DATE | Syntax: DATE(year, month, day) Converts a date written as year, month, day to an internal serial number and displays it in the cell's formatting. Year is an integer between 1583 and 9956 or 0 and 99. Month is an integer between 1 and 12. Day is an integer between 1 and 31. |
=DATE(2012, 06, 26) |
| DATEVALUE | Syntax: DATEVALUE(date_text) Converts a date that is stored as text to a serial number that it recognizes as a date. |
=DATEVALUE("2012-06-26") |
| DAY | Syntax: DAY(date_value) This function returns the day of the month (a number from 1 to 31) given a date value. |
=DAY(today()) |
| DAYS | Syntax: DAYS(end_date, start_date) This function returns the number of days between two dates. |
=DAYS(A1,A2) =DAYS(2013-02-01,2013-12-22) |
| DAYS360 | Syntax: DAYS360(start_date, end_date, [method]) This function returns the number of days between two dates based on a 360-day year. start_date and end_date are the two dates to calculate the difference between. method is optional. It is a boolean value - either TRUE or FALSE. If TRUE is entered, the DAYS360 function will use the US method. If FALSE is entered, the DAYS360 function will use the European method. |
=DAYS360(A1,A2) =DAYS360(2013-02-01,2013-12-22) |
| EDATE | Syntax: EDATE(Start_date, months) Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). |
=EDATE(today(), 2) |
| EOMONTH | Syntax: EOMONTH(Start_date, months) Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. |
=EOMONTH(today(), 2) |
| HOUR | Syntax: Hour(serial_number) This function returns the hour of a time value (from 0 to 23). |
=DAY(A1) =DAY(12:52:35) |
| MINUTE | Syntax: MINUTE(serial_number) This function returns the minute of a time value (from 0 to 59). |
=MINUTE("8:28:10") |
| MONTH | Syntax: MONTH(date_value) This function returns the month (a number from 1 to 12) given a date value. |
=MONTH(2013-12-22) |
| NETWORKDAYS | Syntax: NETWORKDAYS(start_date,end_date, [holidays]) Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. |
=NETWORKDAYS(A1, A2, B1:B5) |
| NETWORKDAYS.INTL | Syntax: NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]) Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. |
=NETWORKDAYS.INTL(A1, A2, 1, B1:B5) |
| NOW | Syntax: NOW() Returns the current computer system date time. NOW is a function without arguments. |
=NOW() |
| SECOND | Syntax: SECOND(serial_number) This function returns the second of a time value (from 0 to 59). |
=SECOND("8:28:18") =SECOND("3:08:18 PM") =SECOND("8:28:18") |
| TIME | Syntax: TIME(hour, minute, second) This function returns the decimal number for a particular time. |
=TIME(0,650,0) =TIME(A1,A2,A3) |
| TIMEVALUE | Syntax: TIMEVALUE(date_text) Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). |
=TIMEVALUE("2012-06-26 20:30") |
| TODAY | Syntax: TODAY() Returns the current computer system date. TODAY is a function without arguments. |
=TODAY() |
| WEEKDAY | Syntax: WEEKDAY(serial_number, [type]) Returns the day of the week for the given serial_number (date value). The day is returned as an integer based on the type. Type is option. It can be any of the following values: type = 1 (default), the weekdays are counted starting from Sunday (Monday = 2). type = 2, the weekdays are counted starting from Monday (Monday = 1). type = 3, the weekdays are counted starting from Monday (Monday = 0). |
=WEEKDAY(A1) =WEEKDAY("2010/10/13") |
| WEEKNUM | Syntax: WEEKNUM(serial_number, [type]) Returns a number that indicates where the week falls numerically within a year. The WEEKNUM function considers the week containing January 1 to be the first week of the year. Type is option. It can be any of the following values: type = 1 (default), Week begins on Sunday. type = 2, Week begins on Monday. |
=WEEKNUM(A1) =WEEKNUM(Date(2010,10,13), 2) |
| WORKDAY | Syntax: WORKDAY(start_date, days, [holidays]) Returns a number that represents a 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. |
=WORKDAY(A1, 10, B1:B5) |
| WORKDAY.INTL | Syntax: WORKDAY.INTL(start_date, days, [weekend], [holidays]) Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays. |
=WORKDAY.INTL(A1, 10, 1, B1:B5) |
| YEAR | Syntax: YEAR(date_value) This function returns a four-digit year (a number from 1900 to 9999) given a date value. |
=YEAR(A1) =YEAR("2013/10/13") |
| YEARFRAC | Syntax: YEARFRAC(start_date, end_date, basis) Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. |
=YEARFRAC(A2,A3,A4) |