Explanation
The WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. WEEKNUM takes two arguments: a date , and (optionally) return_type , which controls the scheme used to calculate the week number.
By default, the WEEKNUM function uses a scheme where week 1 begins on January 1, and week 2 begins on the next Sunday (when the return_type argument is omitted, or supplied as 1). With a return_type of 2, week 1 begins on January 1, and week 2 begins on the next Monday. See the WEEKNUM page for more information.
ISO week number
ISO week numbers, start on the Monday of the first week in a year with a Thursday . This means that the first day of the year for ISO weeks is always a Monday in the period between Jan 29 and Jan 4. Starting with Excel 2010, you can generate an ISO week number using 21 as the return_type:
=WEEKNUM(date,21)
Starting in Excel 2013, there is a new function called ISOWEEKNUM .
For more details, see Ron de Bruin’s nice write-up on Excel week numbers .
Explanation
This formula uses the NETWORKDAYS function calculate total working days between two dates, taking into account weekends and (optionally) holidays. Holidays, if provided, must be a range of valid Excel dates. Once total work days are known, they are simply multiplied by a fixed number of hours per day, 8 in the example shown.
The NETWORKDAYS function includes both the start and end date in the calculation, and excludes both Saturday and Sunday by default. The function will also exclude holidays when then are provided as the “holidays” argument as a range of valid dates.
In these example shown, the first two formulas use the NETWORKDAYS function.
D6=NETWORKDAYS(B6,C6)*8 // no holidays
D7=NETWORKDAYS(B7,C7,holidays)*8 // holidays provided
If your workweek includes days other than Monday through Friday, you can switch to the NETWORKDAYS.INTL function, which provides a “weekend” argument that can be used to define which days of the week are workdays and weekend days. NETWORKDAYS.INTL can be configured just like NETWORKDAYS, but it provides an additional argument called “weekend” to control which days in a week are considered workdays.
The next 4 formulas use the NETWORKDAYS.INTL function:
D8=NETWORKDAYS.INTL(B8,C8)*8 // Mon-Fri, no holidays
D9=NETWORKDAYS.INTL(B9,C9,11)*8 // Mon-Sat, no holidays
D10=NETWORKDAYS.INTL(B10,C10)*8 // M-F, no holidays
D11=NETWORKDAYS.INTL(B11,C11,1,holidays)*8 // M-F, w/ holidays
Custom work schedule
This formula assumes all working days have the same number of work hours. If you need to calculate work hours with a custom schedule where work hours vary according to the day of week, you can try a formula like this:
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
You can find an explanation here .
