Excel WORKDAY & NETWORKDAYS Functions: Exclude Weekends and Holidays

excel dashboards reportsExcel’s workday function is great when you need to calculate an end date based on a given number of working days – which means you need to exclude weekends and holidays. Some examples are:

creating a project timeline

creating a reporting timetable

Excel’s standard WORKDAY formula assumes weekends fall on Saturdays and Sundays. In case the weekends in your country fall on other days, you will need to use Workday.intl where you have an additional option to select the weekend days.

Watch the video to find out how to use the WORKDAY function:

On the other hand, if you need to calculate the number of working days based on given start and end dates, you need to use the NETWORKDAYS function.

This is a great function for anyone planning deadlines and needs to find the number of working days available to them. This function excludes weekends as well. If you use NETWORKDAYS.INTL you can decide which days are considered weekends and should be excluded.

In the video below I also show you a way to make the selection for holidays dynamic. For example, if you are doing a project in one country and want to roll out that same project in other countries and you’d like the number of working days between the two dates to consider the holidays of the chosen country – the index function can come to the rescue.

Watch the video to find out how to use the NETWORKDAYS function:

Watch out:

  • WORKDAY does not count start day
  • NETWORKDAYS does count start day

Feel free to Download the Workbook HERE.

Free Excel Download

The new Excel Dashboards course is here!

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

8 Comments

  1. Quick Gantt Chart | June 18, 2017 at 9:17 am - Reply

    […] let’s say, you just setup a preliminary project plan in Excel. You did that with the help of WORDAY and NETWORKDAY […]

    • Wahab July 5, 2017 at 8:10 am - Reply

      Dear Ms. Leila,
      Is there any way to hide empty rows in excel printout, please recall “Lotus 123” if beginning of the row with “||” this one than that row will not print but it will be shown in worksheet.

      Thanks and Regards

      • Leila Gharani July 10, 2017 at 7:05 pm - Reply

        Hi Wahab,
        Unfortunately there is no short-hand for that. You could create a macro to hide empty rows before printing. As an alternative you could turn your data set into an Excel Table and filter out empty cells. You could also write an IF formula in the first cell that if the counta of the adjacent columns is empty it should put “to Hide” in the first cell and then filter out “to Hide” cells prior to printing….

      • Krishna kumar June 22, 2018 at 5:34 am - Reply

        Hi Amy Laskowski,

        Gratitude for putting up this prolific article! You truly make everything a cake walk. Genuinely good stuff, saving time and energy.I started using Workday tutorial blog for my training practice.

        I have been asked to create a formula that uses balance values from the previous 3 payroll periods. The idea is to retrieve the basic pay period balance in each of the 3 previous periods. The sum of these will be used in a separate calculation. I have used ptd balances but never spanning previous periods.

        Any help greatly appreciated.

        By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
        Please keep providing such valuable information.

        Thank you,
        Krishna kumar

  2. Conditional Formatting Symbols | July 11, 2017 at 2:39 pm - Reply

    […] to the number of working days specified. (The link to how to use the WORKDAY() function is found here […]

  3. Quick Gantt Chart (August Update) | August 2, 2017 at 12:20 pm - Reply

    […] did that with the help of WORKDAY and NETWORKDAY […]

  4. Radhey May 24, 2018 at 10:16 am - Reply

    Hello There,

    Muchas Gracias Mi Amigo! You make learning so effortless. Anyone can follow you and I would not mind following you to the moon coz I know you are like my north star. Workday tutorial

    Yes you can have multiple assignments for a person and can query them.

    Multiple assignments are used for say
    1. A person works in a company as a Accounts Head and he has three subordinates so assignment number as Accounts Head will be used for all three as a supervisor.
    2. Same is that if he is also working as Admin head and has 2 persons under him then assignment number as Admin head will be used for 2 persons as supervisior

    Costing can also be maintain for two different assignments.

    Appreciate your effort for making such useful blogs and helping the community.

    Obrigado,
    Radhey

  5. Krishna kumar June 22, 2018 at 5:35 am - Reply

    Hi Amy Laskowski,

    Gratitude for putting up this prolific article! You truly make everything a cake walk. Genuinely good stuff, saving time and energy.I started using Workday tutorial blog for my training practice.

    I have been asked to create a formula that uses balance values from the previous 3 payroll periods. The idea is to retrieve the basic pay period balance in each of the 3 previous periods. The sum of these will be used in a separate calculation. I have used ptd balances but never spanning previous periods.

    Any help greatly appreciated.

    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
    Please keep providing such valuable information.

    Thank you,
    Krishna kumar

Leave A Comment

Share This