Here, we will learn how to use the Excel date functions: WORKDAY, WORKDAY.INTL, NETWORKDAYS and NETWORKDAYS.INTL. Later, we will also learn how to calculate workdays in a year using NETWORKDAYS with the DATE function.
Excel WORKDAY function
The Excel WORKDAY function returns the date that is the given number of working days in the future or in the past, excluding weekends (Saturday and Sunday) and (optionally) holidays. The function takes the arguments like this: WORKDAY(start_date,days,[holidays]). In the days argument, use a positive number to make the function return the date in the future, and a negative number to return the date in the past. For example, the formula =WORKDAY("1-Nov-2024",1) returns the date 4-Nov-2024. The formula in C4, copied down, is:
=WORKDAY(A3,B3,$D$3)
Excel WORKDAY.INTL function
Like WORKDAY, the Excel WORKDAY.INTL function returns the date that is the given number of working days in the future or in the past, excluding weekends (Saturday and Sunday) and (optionally) holidays. The only difference between WORKDAY and WORKDAY.INTL is that WORKDAY.INTL takes an extra argument, weekend, that can be configured for a custom weekend(s). The function takes the arguments like this: WORKDAY.INTL(start_date,days,[weekend],[holidays]). To set Sunday as a weekend, set the weekend argument to 11. For example, the formula =WORKDAY.INTL("1-Nov-2024",1,11) returns the date 2-Nov-2024. The formula in H4, copied down, is:
=WORKDAY.INTL(F3,G3,11,$I$3)
Excel NETWORKDAYS function
The Excel NETWORKDAYS function calculates the number of working days between the given two dates, excluding weekends (Saturday and Sunday) and (optionally) holidays. The function takes the arguments like this: =NETWORKDAYS(start_date,end_date,[holidays]). For example, the formula =NETWORKDAYS("1-Nov-2024","4-Nov-2024","1-Nov-2024") returns 1. The formula in N4, copied down, is:
=NETWORKDAYS(K3,L3,$O$3:$O$6)
Excel NETWORKDAYS.INTL function
Like NETWORKDAYS, the Excel NETWORKDAYS.INTL function calculates the number of working days between the given two dates, excluding weekends (Saturday and Sunday) and (optionally) holidays. The only difference between NETWORKDAYS and NETWORKDAYS.INTL is that NETWORKDAYS.INTL takes an extra argument, weekend, that can be configured for a custom weekend(s). The function takes the arguments like this: =NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]). To set Sunday as a weekend, set the weekend argument to 11. For example, the formula =NETWORKDAYS.INTL("1-Nov-2024","4-Nov-2024",11,"1-Nov-2024") returns 2. The formula in T4, copied down, is:
=NETWORKDAYS.INTL(Q3,R3,11,$O$3:$O$6)
Calculating workdays in a year using NETWORKDAYS with DATE
To calculate workdays in a year with the dates (year, month and day) in separate cells, you can use a formula based on the NETWORKDAYS and DATE functions.
The dataset (shown in the image) contains some dates in the "Start date" and "End date" header columns, inserted into separate cells. The goal is to join the date values (year, month and day) using DATE and calculate the number of working days between the start date and end date using NETWORKDAYS. The formula in G13, copied down, is:
=NETWORKDAYS(DATE(A12,B12,C12),DATE(D12,E12,F12))
How this formula works
DATE creates date from the given individual year, month and day components. After this, the formula becomes like this: =NETWORKDAYS("1-Jan-2024","31-Dec-2024"). Therefore, NETWORKDAYS returns 262 as the final output, representing the number of working days.
You have successfully learnt how to use the Excel date functions: WORKDAY, WORKDAY.INTL, NETWORKDAYS and NETWORKDAYS.INTL. You have also learnt how to calculate workdays in a year using NETWORKDAYS with the DATE function! I hope this post helped you.
