NETWORKDAYS, WORKDAY and WEEKDAY practice exercises
Count the working days between two dates, find the date a number of working days ahead, and tell which day of the week a date falls on.
How NETWORKDAYS, WORKDAY and WEEKDAY work
NETWORKDAYS counts the working days between two dates, Monday to Friday. WORKDAY goes the other way: give it a start date and a number of working days, and it returns the date that many working days later. Both can skip a list of holidays. WEEKDAY turns a date into a number for the day of the week.
With Mar 4, 2024 in A2 and Mar 15, 2024 in B2, =NETWORKDAYS(A2,B2) returns 10, because the start and end dates both count. With an order date of Mar 4, 2024 in A2 and 5 in B2, =WORKDAY(A2,B2) returns Mar 11, 2024. Add a range of holiday dates as the last argument, as in =NETWORKDAYS(A2,B2,E2:E4), and those days are left out too. WORKDAY returns a date serial number, so format the cell as a date.
WEEKDAY counts Sunday as 1 unless you say otherwise. Give it 2 as the second argument and Monday becomes 1. If your weekend is not Saturday and Sunday, use NETWORKDAYS.INTL or WORKDAY.INTL. The exercises below cover counting working days with and without holidays, delivery dates with WORKDAY and numbering the day of the week.
- 1Count working days with NETWORKDAYSIn C2, use NETWORKDAYS to count the working days, Monday to Friday, from the start date in A2 to the end date in B2.
- 2Working days around EasterPremiumThe office is closed on the days in E2:E4. In C2, count the working days from A2 to B2 with NETWORKDAYS, and give it E2:E4 as the holidays.
- 3Delivery date with WORKDAYPremiumDelivery takes the number of working days in B2. In C2, use WORKDAY to find the delivery date for the order date in A2.
- 4Day of the week with WEEKDAYPremiumIn B2, use WEEKDAY with 2 as the second argument to number the day of the week of A2, with Monday as 1.
- 5Delivery date over the holidaysPremiumThe office is closed on the days in E2:E5. In C2, find the delivery date B2 working days after the order date in A2. Use WORKDAY and give it E2:E5 as the holidays.
Functions in this topic
Read the guide
Questions
Why does WORKDAY show a number instead of a date?
Excel stores dates as serial numbers, and WORKDAY returns one. The cell just needs a date format. Select it, open the number format menu on the Home tab and choose Short Date. The same goes for any formula that returns a date, such as EDATE or a date plus a number of days.
How do I count working days when the weekend is not Saturday and Sunday?
Use NETWORKDAYS.INTL, which takes a weekend code as the third argument. =NETWORKDAYS.INTL(A2,B2,11) treats only Sunday as the weekend, and 7 makes Friday and Saturday the weekend. Holidays go in as the fourth argument. WORKDAY.INTL works the same way for delivery dates.
How do I get the name of the weekday from a date in Excel?
Use =TEXT(A2,"dddd") to get the full name, such as Monday, or "ddd" for Mon. To keep the date and only change how it looks, format the cell with the custom format dddd. WEEKDAY on its own only returns a number, so it is not the tool for names.