Explanation
The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.
For example, in the screenshot shown, the formula in D6 is:
=NETWORKDAYS(B6,C6) // returns 5
This formula returns 5 since there are 5 working days between December 23 and December 27, and no holidays have been provided. Note that NETWORKDAYS includes both the start and end dates in the calculation if they are workdays.
NETWORKDAYS can also exclude a custom list of holidays. In the next cell down, we use the same formula with the same dates, plus a list of holidays in B10:B11.
=NETWORKDAYS(B7,C7,B10:B11) // returns 3
This formula returns 3, since two of the 5 days are holidays.
Workdays remaining from today
To calculate the number of workdays remaining from today, you can use WORKDAY with the TODAY function like this:
=NETWORKDAYS(TODAY(),A1)
where cell A1 contains an end date in the future.
Custom weekends
If you need take into account custom weekends (i.e. weekends are Saturday only, Sunday and Monday, etc.) you’ll need to switch to the more robust NETWORKDAYS.INTL function , which allows you to set what days of the week are considered are considered weekends, by supplying a weekend argument in the form of a numeric code.
Need a date?
If you need a date n workdays in the past or future, see the WORKDAY function.
Explanation
In this example, the goal is to extract the year number from a list of dates in column B. This can be easily achieved with the YEAR function .
The YEAR function takes just one argument, the date from which you want to extract the year. For example, in the formula below, we pass the “12-Dec-1999” into the YEAR function, which returns 1999:
=YEAR("12-Dec-1999") // returns 1999
In the worksheet shown, we use a cell reference instead of hard-coding the date. The formula in cell D5 is:
=YEAR(B5)
The result in D5 is 1912 since the date in B5 is 4-Apr-1912. As the formula is copied down, it returns a four-digit year for each date listed in column B.
Dates as text
Note that you can use YEAR to extract the year from a day entered as text:
=YEAR("1/5/2016") // returns 2016
However, using text for dates can cause unpredictable results on computers using different regional date settings. In general, a much better approach is to provide a cell reference that already contains a valid date. If YEAR returns a #VALUE error, it means Excel does not recognize the value as a date. For some ways to get Excel to recognize dates, see: Convert text to date .
Display year only
In some cases, you may want to enter a date and only display the year. You can accomplish this by applying a custom number format like “yyyy” or “yy” to one or more dates.
