Category: Excel

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

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.

Tip: To calculate the serial number of the date before or after a specified number of workdays by using parameters to indicate which and how many days are weekend days, use the WORKDAY.INTL function.

Syntax

WORKDAY(start_date, days, [holidays])

The WORKDAY function syntax has the following arguments:

• Start_date    Required. A date that represents the start date.
• Days    Required. The number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
• Holidays    Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
• If any argument is not a valid date, WORKDAY returns the #VALUE! error value.
• If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.
• If days is not an integer, it is truncated.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data
10/1/2008	Start date
151	Days to completion
11/26/2008	Holiday
12/4/2008	Holiday
1/21/2009	Holiday
Formula	Description (Result)	Result
=WORKDAY(A2,A3)	Date 151 workdays from the start date (4/30/2009)	4/30/2009
=WORKDAY(A2,A3,A4:A6)	Date 151 workdays from the start date, excluding holidays (5/5/2009)	5/5/2009

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

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

The WEEKNUM function syntax has the following arguments:

• Serial_number     Required. A date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
• Return_type     Optional. A number that determines on which day the week begins. The default is 1.

Return_type	Week begins on	System
1 or omitted	Sunday	1
2	Monday	1
11	Monday	1
12	Tuesday	1
13	Wednesday	1
14	Thursday	1
15	Friday	1
16	Saturday	1
17	Sunday	1
21	Monday	2

Remark

• Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1. January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
• If Serial_number is out of range for the current date base value, a #NUM! error is returned.
• If Return_type is out of the range specified in the table above, a #NUM! error is returned.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data
3/9/2012
Formula	Description	Result
=WEEKNUM(A2)	Number of the week in the year that 3/9/2012 occurs, based on weeks beginning on Sunday (default).	10
=WEEKNUM(A2,2)	Number of the week in the year that 3/9/2012 occurs, based on a week beginning on Monday (the second argument, 2).	11

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

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

The WEEKDAY function syntax has the following arguments:

• Serial_number    Required. A sequential number that represents the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
• Return_type    Optional. A number that determines the type of return value.

Return_type	Number returned
1 or omitted	Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2	Numbers 1 (Monday) through 7 (Sunday).
3	Numbers 0 (Monday) through 6 (Sunday).
11	Numbers 1 (Monday) through 7 (Sunday).
12	Numbers 1 (Tuesday) through 7 (Monday).
13	Numbers 1 (Wednesday) through 7 (Tuesday).
14	Numbers 1 (Thursday) through 7 (Wednesday).
15	Numbers 1 (Friday) through 7 (Thursday).
16	Numbers 1 (Saturday) through 7 (Friday).
17	Numbers 1 (Sunday) through 7 (Saturday).

Remark

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
• If serial_number is out of range for the current date base value, a #NUM! error is returned.
• If return_type is out of the range specified in the table above, a #NUM! error is returned.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data
2/14/2008
Formula	Description (Result)	Result
=WEEKDAY(A2)	Day of the week, with numbers 1 (Sunday) through 7 (Saturday) (5)	5
=WEEKDAY(A2, 2)	Day of the week, with numbers 1 (Monday) through 7 (Sunday) (4)	4
=WEEKDAY(A2, 3)	Day of the week, with numbers 0 (Monday) through 6 (Sunday) (3)	3

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

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.

Note: If the TODAY function does not update the date when you expect it to, you might need to change the settings that control when the workbook or worksheet recalculates. On the File tab, click Options, and then in the Formulas category under Calculation options, make sure that Automatic is selected.

Syntax

TODAY()

The TODAY function syntax has no arguments.

Note: Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Formula	Description	Result
=TODAY()	Returns the current date.	12/1/2011
=TODAY()+5	Returns the current date plus 5 days. For example, if the current date is 1/1/2012, this formula returns 1/6/2012.	12/6/2011
=DATEVALUE(“1/1/2030”)-TODAY()	Returns the number of days between the current date and 1/1/2030. Note that cell A4 must be formatted as General or Number for the result to display correctly.	1/31/1918
=DAY(TODAY())	Returns the current day of the month (1 – 31).	1
=MONTH(TODAY())	Returns the current month of the year (1 – 12). For example, if the current month is May, this formula returns 5.	12

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

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)

The TIMEVALUE function syntax has the following arguments:

• Time_text    Required. 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.

Remarks

• Date information in time_text is ignored.
• Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Formula	Description	Result
=TIMEVALUE(“2:24 AM”)	Decimal part of a day, with only the time specified.	0.10
=TIMEVALUE(“22-Aug-2011 6:35 AM”)	Decimal part of a day, with date and time specified.	0.2743

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

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)

The TIME function syntax has the following arguments:

• Hour    Required. 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.
• Minute    Required. 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.
• Second    Required. 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

Remark

Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Hour	Minute	Second
12	0	0
16	48	10
Formula	Description	Result
=TIME(A2,B2,C2)	Decimal part of a day, for the time specified in row 2 (12 hours, 0, minutes, 0 seconds)	0.5
=TIME(A3,B3,C3)	Decimal part of a day, for the time specified in row 3 (16 hours, 48 minutes, 10 seconds)	0.7001157

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

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)

The SECOND function syntax has the following arguments:

• Serial_number    Required. The time that contains the seconds you want to find. Times may be entered as text strings within quotation marks (for example, “6:45 PM”), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE(“6:45 PM”)).

Remark

Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data
Time
4:48:18 PM
4:48 PM
Formula	Description	Result
=SECOND(A3)	Seconds in the first time (18)	18
=SECOND(A4)	Seconds in the second time (0)	0

This article describes the formula syntax and usage of the NOW function in Microsoft Excel. Find links to more information about working with dates and times in the See Also section.

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.

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: If the NOW function does not update cell values when you expect it to, you might need to change settings that control when the workbook or worksheet recalculates. These settings can be changed in Control Panel for the Excel desktop application.

Syntax

NOW()

The NOW function syntax has no arguments.

Remarks

• Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
• Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date. For example, the serial number 0.5 represents the time 12:00 noon.
• The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Formula	Description	Result
=NOW()	Returns the current date and time.	11/6/2011 19:03
=NOW()-0.5	Returns the date and time 12 hours ago (-0.5 days ago).	11/6/2011 7:03
=NOW()+7	Returns the date and time 7 days in the future.	11/13/2011 19:03
=NOW()-2.25	Returns the date and time 2 days and 6 hours ago (-2.25 days ago).	11/4/2011 13:03

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

The NETWORKDAYS.INTL function syntax has the following arguments:

• Start_date and end_date    Required. The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
• Weekend    Optional. Indicates the days of the week that are weekend days and are not included in the number of whole working days between start_date and end_date. Weekend is a weekend number or string that specifies when weekends occur.Weekend number values indicate the following weekend days:

Weekend number	Weekend days
1 or omitted	Saturday, Sunday
2	Sunday, Monday
3	Monday, Tuesday
4	Tuesday, Wednesday
5	Wednesday, Thursday
6	Thursday, Friday
7	Friday, Saturday
11	Sunday only
12	Monday only
13	Tuesday only
14	Wednesday only
15	Thursday only
16	Friday only
17	Saturday only

Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0.

For example, 0000011 would result in a weekend that is Saturday and Sunday.

• Holidays    Optional. An optional set of one or more dates that are to be excluded from the working day calendar. holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.

Remarks

• If start_date is later than end_date, the return value will be negative, and the magnitude will be the number of whole workdays.
• If start_date is out of range for the current date base value, NETWORKDAYS.INTL returns the #NUM! error value.
• If end_date is out of range for the current date base value, NETWORKDAYS.INTL returns the #NUM! error value.
• If a weekend string is of invalid length or contains invalid characters, NETWORKDAYS.INTL returns the #VALUE! error value.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Formula	Description	Result
=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,1,31))	Results in 22 future workdays. Subtracts 9 nonworking weekend days (5 Saturdays and 4 Sundays) from the 31 total days between the two dates. By default, Saturday and Sunday are considered non-working days.	22
=NETWORKDAYS.INTL(DATE(2006,2,28),DATE(2006,1,31))	Results in -21, which is 21 workdays in the past.	-21
=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),7,{“2006/1/2″,”2006/1/16”})	Results in 22 future workdays by sutracting 10 nonworking days (4 Fridays, 4 Saturdays, 2 Holidays) from the 32 days between Jan 1 2006 and Feb 1 2006. Uses the 7 argument for weekend, which is Friday and Saturday. There are also two holidays in this time period.	22
=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),”0010001″,{“2006/1/2″,”2006/1/16”})	Results in 22 future workdays. Same time period as example directly above, but with Sunday and Wednesday as weekend days.	20

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

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.

Tip:  To calculate whole workdays between two dates by using parameters to indicate which and how many days are weekend days, use the NETWORKDAYS.INTL function.

Syntax

NETWORKDAYS(start_date, end_date, [holidays])

The NETWORKDAYS function syntax has the following arguments:

• Start_date    Required. A date that represents the start date.
• End_date    Required. A date that represents the end date.
• Holidays    Optional. An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.

Important:  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2012,5,23) for the 23rd day of May, 2012. Problems can occur if dates are entered as text.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2012 is serial number 40909 because it is 40,909 days after January 1, 1900.
• If any argument is not a valid date, NETWORKDAYS returns the #VALUE! error value.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Date	Description
10/1/2012	Start date of project
3/1/2013	End date of project
11/22/2012	Holiday
12/4/2012	Holiday
1/21/2013	Holiday
Formula	Description	Result
=NETWORKDAYS(A2,A3)	Number of workdays between the start (10/1/2012) and end date (3/1/2013).	110
=NETWORKDAYS(A2,A3,A4)	Number of workdays between the start (10/1/2012) and end date (3/1/2013), with the 11/22/2012 holiday as a non-working day.	109
=NETWORKDAYS(A2,A3,A4:A6)	Number of workdays between the start (10/1/2012) and end date (3/1/2013), with the three holidays as non-working days.	107