EXCEL : Date & Time Formula / Syntax

1) DATE function

Description

Use Excel's DATE function when you need to take three separate values and combine them to form a date.

Syntax

DATE(year,month,day)

2) DATEVALUE function

Description

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008. Remember, though, that your computer's system date setting may cause the results of a DATEVALUE function to vary from this example

The DATEVALUE function is helpful in cases where a worksheet contains dates in a text format that you want to filter, sort, or format as dates, or use in date calculations.

To view a date serial number as a date, you must apply a date format to the cell. Find links to more information about displaying numbers as dates in the See Also section.

Syntax

DATEVALUE(date_text)

3) DAY Function

Description

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

Syntax

DAY(serial_number)

4) DAYS function

Description

Returns the number of days between two dates.

Syntax

DAYS(end_date, start_date)

5) DAYS360 function

Description

The DAYS360 function 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.

Syntax

DAYS360(start_date,end_date,[method])

6) EDATE function

Description

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.

Syntax

EDATE(start_date, months)

7) EOMONTH function

Description

Returns the serial number 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.

Syntax

EOMONTH(start_date, months)

8) HOUR function

Description

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

Syntax

HOUR(serial_number)

9) ISOWEEKNUM function

Description

Returns number of the ISO week number of the year for a given date.

Syntax

ISOWEEKNUM(date)

10) MINUTE function

Description

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

Syntax

MINUTE(serial_number)

11) NETWORKDAYS function

Description

Returns the number of whole working days between start_date and end_date. 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.

Syntax

NETWORKDAYS(start_date, end_date, [holidays])

12) NETWORKDAYS.INTL function

Description

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.

Syntax

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

13) NOW function

Description

Returns the serial number of the current date and time. If the cell format was General before the function was entered, Excel changes the cell format so that it matches the date and time format of your regional settings. You can change the date and time format for the cell by using the commands in the Number group of the Home tab on the Ribbon.

Syntax

NOW()

14) SECOND function

Description

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

Syntax

SECOND(serial_number)

15) TIME function

Description

Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date. The decimal number returned by TIME 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.).

Syntax

TIME(hour, minute, second)

16) TIMEVALUE function

Description

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

Syntax

TIMEVALUE(time_text)

17) TODAY function

Description

Returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations. If the cell format was General before the function was entered, Excel changes the cell format to Date. If you want to view the serial number, you must change the cell format to General or Number.

The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals. For example, if you know that someone was born in 1963, you might use the following formula to find that person's age as of this year's birthday:

YEAR( TODAY())-1963

This formula uses the TODAY function as an argument for the YEAR function to obtain the current year, and then subtracts 1963, returning the person's age.

Syntax

TODAY()

18) WEEKDAY function

Description

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

Syntax

WEEKDAY(serial_number,[return_type])

19) WEEKNUM function

Description

Returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1.

There are two systems used for this function:

System 1 The week containing January 1 is the first week of the year, and is numbered week 1.

System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.

Syntax

WEEKNUM(serial_number,[return_type])

20) WORKDAY function

Description

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.

Syntax

WORKDAY(start_date, days, [holidays])

21) WORKDAY.INTL function

Description

This article describes the formula syntax and usage of the WORKDAY.INTL function in Microsoft Excel.

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.

Syntax

WORKDAY.INTL(start_date, days, [weekend], [holidays])

22) YEAR function

Description

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

Syntax

YEAR(serial_number)

23) YEARFRAC function

Description

YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term.

Syntax

YEARFRAC(start_date, end_date, [basis])

---