NETWORKDAYS.INTL Function

-

NETWORKDAYS.INTL Function


The Excel NETWORKDAYS.INTL function calculates the number of working days between two dates. NETWORKDAYS.INTL excludes Saturday and Sunday by default, but provides a way to specify which days of the week are considered weekends.  The function can optionally exclude a list of holidays supplied as dates.


Syntax

The syntax for the NETWORKDAYS.INTL function in Microsoft Excel is:

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

Parameters or Arguments

start_date

The start date to use in the calculation. It must be entered using the DATE function.

end_date

The end date to use in the calculation. It must be entered using the DATE function.

weekend

Optional. It is the days of the week to include as weekend days. If this parameter is omitted, it will assume that weekends include Saturday and Sunday. The weekend parameter can be one of the following numeric or string values:
Value
Explanation(days of week to include as weekend days)
1
Saturday and Sunday (default)
2
Sunday and Monday
3
Monday and Tuesday
4
Tuesday and Wednesday
5
Wednesday and Thursday
6
Thursday and Friday
7
Friday and Saturday
11
Sunday
12
Monday
13
Tuesday
14
Wednesday
15
Thursday
16
Friday
17
Saturday

holidays

Optional. It is the list of holidays to exclude from the work days calculation. It can be entered either as a range of cells that contain the holiday dates (ie: E2:E4) or as a list of serial numbers that represent the holiday dates.

Networkdays.Intl Function Examples


The spreadsheets below show three examples of the Excel Networkdays.Intl function used to calculate the number of work days between the two dates Dec 01, 2015 and Jan 15, 2016. In each case, the days to be counted as weekends and holidays are different.

Formulas:


A
B
C
1
Start Date:
12/01/2015

2
End Date:
01/15/2016

3
Holidays:
12/25/2015

4

12/28/2015

5

01/01/2016

6



7
Work days between Dec 1st, 2015 & Jan 15th, 2016:
8
=NETWORKDAYS.INTL( B1, B2 )
- Weekends Sat & Sun; No holidays
9
=NETWORKDAYS.INTL( B1, B2, 1, B3:B5 )
- Weekends Sat & Sun; Excludes holidays in cells B3-B5
10
=NETWORKDAYS.INTL( B1, B2, "0000111" )
- Weekends Fri, Sat & Sun; No holidays

Results:


A
B
C
7
Work days between Dec 1st, 2015 & Jan 15th, 2016:
8
34

- Weekends Sat & Sun; No holidays
9
31

- Weekends Sat & Sun; Excludes holidays in cells B3-B5
10
27

- Weekends Fri, Sat & Sun; No holidays

Note that, in the above spreadsheets:

►   The [holidays] array has been omitted from the calculation in cell A8. Therefore, this result excludes Saturdays and Sundays but includes all other weekdays, including the holidays at Christmas and New Year.
►   In cell A9 of the example spreadsheet, the [weekend] argument is 1 (specifying weekends on Saturdays and Sundays) and the [holidays] array (in cells B3-B5) is provided to the Networkdays.Intl function. Therefore, the calculation excludes Saturdays and Sundays and the listed Christmas and New Year holidays.
►   In the example in cell A10, Fridays, Saturdays and Sundays are specified as weekends, but no holiday array has been supplied to the function. Therefore, the calculation excludes Fridays, Saturdays and Sundays but includes all other weekdays, including the holidays at Christmas and New Year.
Note also that, as recommended by Microsoft, in all three calls to the Networkdays.Intl function, the start_date, end_date, and [holidays] arguments have been supplied as cell references.

Comments

Post a Comment

Popular posts from this blog

Formatting a Range of Cells In Excel VBA

-
Formatting Cells Number General Range("A1").NumberFormat = "General" Number Range("A1").NumberFormat = "0.00" Currency Range("A1").NumberFormat = "$#,##0.00" Accounting Range("A1").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" Date Range("A1").NumberFormat = "yyyy-mm-dd;@" Time Range("A1").NumberFormat = "h:mm:ss AM/PM;@" Percentage Range("A1").NumberFormat = "0.00%" Fraction Range("A1").NumberFormat = "# ?/?" Scientific Range("A1").NumberFormat = "0.00E+00" Text Range("A1").NumberFormat = "@" Special Range("A1").NumberFormat = "00000" Custom Range("A1").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" Formatting Cells Alignment Text Alignment Horizontal The value o
Read more

Trunc() Function

-
Image
Trunc() Function The Excel TRUNC function returns a truncated number based on a given number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified. Syntax The syntax for the TRUNC function in Microsoft Excel is: =TRUNC( number, [digits] ) Parameters or Arguments number The number to truncate. digits Optional. It is the number of decimal places to display in the resulting truncated number. If this parameter is omitted, the TRUNC function will assume 0. Trunc Function Examples The following spreadsheet shows several examples of the Excel Trunc function: Formulas: A B 1 99.999 =TRUNC( A1, 1 ) 2 99.999 =TRUNC( A2, 2 ) 3 99.999 =TRUNC( A3 ) 4 99.999 =TRUNC( A4, -1 ) 5 -99.999 =TRUNC( A5, 2 ) 6 -99.999 =TRUNC( A6, -1 ) Results: A B 1 99.999 99.9 2 99.999 99.99
Read more

BREAK THE PASSWORD OF THE SHEET IN EXCEL

-
Below steps will guide you to hack or unlock the Password of the excel Sheet Press ALT + F11 or click on View Code in Developers Tabs In the visual basic Enter the below Code. Do not change the code just copy paste: Sub PasswordBreaker() 'Breaks worksheet password protection. Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer, i5 As Integer, i6 As Integer On Error Resume Next For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If ActiveSheet.ProtectContents = False Then MsgBox "One usable password is " & Chr(i) & C
Read more